1

Re: Migrating a network installation from MSDE to SQL Server

I've experimented with doing this, following the instructions on p.9 of the Network Installation Guide, and assuming the latter concerns the situation  the default SQL server authentication method has been chosen, since the instructions make reference to enabling the NBNUser login.

There was no problem in detaching the NBNData database from MSDE and attaching to SQL Server (steps 1 9). My probems started at step 10, "Enable the NBNUser login etc." I suspect there is a fundamental problem here in that although you have copied over an NBNUser login with the database, there is no corresponding server-level NBNUser login.

This is what I did:

I firstly tried running 'EXEC sp_change_users_login "Update_One", "NBNUser", "NBNUser"' without creating a matching server level login ( as implied in the installation guide). This gave the error 'Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 108
Terminating this procedure. The User name 'NBNUser' is absent or invalid.'. So I deleted NBNUser in NBNData (it won't let you delete while the user still owns a Schema so I temporarily transferred ownership of the NBNUser schema to another user). I then created  a new server level login NBNUser, with permissions matching those in MSDE, and mapped this to NBNData, thus creating a new NBNUser on the database. I changed permissions to match the original. I then re-ran the script just to make sure, but still got the "invalid user" message. (Whats even more puzzling is that I have no problem logging in as this user from Management Studio).

If I install Recorder to use Trusted Connection, there is no problem, since NBNUser presumably is not used.

Any comments please?

Alan

Alan Hale
Aberystwyth

2

Re: Migrating a network installation from MSDE to SQL Server

I've been through similar problems.
Both Graham Hawker and myself resolved some of the issues in the following thread on this site:
http://forums.nbn.org.uk/viewtopic.php?id=17
Does any of that help?
We had horrendous problems with a networked SQL server at work, only finally solving it here with Mike Weideli's help (but then our systems are complex here).
I also installed SQL server at home and finally managed to resolve all the installation issues on a single PC.  I've yet to redo this at home following a disk failure but I did make good notes at the time of my initial problems, I'll check these out if you need them.
The installation guides are inadequate, partially because the installation testing was not carried out on a wide enough range of network configurations.

3

Re: Migrating a network installation from MSDE to SQL Server

Many thanks for your response on this and I'm very sorry not to have acknowledged sooner. I've more or less decided to give up on this approach and await the release of 6.9.3. I only started looking at in any case after John Van Breda suggested that using SQL Server authentication with NBNUser would be the way to getting networking to work without Windows domains. I think this is not the case, simply due to the fact that, although you copy over NBNUser on NBNData when transferring the database from MSDE to SQL Server 2005, you will have no corresponding NBNUser login at the server level. And it seems impossible to create the server login retrospectively as it were, and get it matched to NBNUser on the database. (If anyone DOES know how to do the latter I'd be interested to hear from them).

I'm still not convinced any of this will work on a Windows peer-to-peer network as opposed to a domain-based network.

Cheers

Alan

Alan Hale
Aberystwyth

4

Re: Migrating a network installation from MSDE to SQL Server

In SQL Server, there are 2 concepts that are associated with your connection. Firstly, there is a login which applies at the server level. Secondly, there is a 'user' which exists at the database level, and defines the security priveleges for a given database and server login combination. So, if you move an NBNData database from one server to another you are only moving the database user called NBNUser, not the server-wide login. You can create this login yourself on the server, but this still doesn't work, because logins and database users need to be joined together by a security ID (SID). The SID of the login and user are different so you can't login.

Fortunately, there is a simple SQL script to repair this. The SQL to join a server login up with a database user is:

USE Nbndata
EXEC sp_change_users_login 'update_one', 'nbnuser', 'nbnuser'

John van Breda
Biodiverse IT

5 (edited by adhale 10-11-2006 12:17:04)

Re: Migrating a network installation from MSDE to SQL Server

John. Yes, I'm fully aware by now (see my first posting in this thread) of the concepts of server level and database level authentication. What I was not clear on was how to reinstitute the former and connect this to the latter following a migration from MSDE to SQL Server. The Installation Guide (Network editio p.9) does not explain this at all well. First of all nowhere does it state that you have to manually create a server level NBNUser login on the SQL Server (this might seem perfectly obvious from your end, but not to someone coming at it for the first time). Secondly, although it tells you to run the script, it does not adequately explain what this script is supposed to do.
Anyway, it looks a lot clearer now. I'll give it a go. Thanks.

Alan

Alan Hale
Aberystwyth

6

Re: Migrating a network installation from MSDE to SQL Server

Gordon Ross here at CCW has managed to get SQL server /R6 running over citrix as a possible method of all ccw access to a single database. He hasnt provided us with details of how he did it yet though although he seems to have used the workstation install.

There is a poblem with mapping, we get the popup message "error code 5 file access denied" C:\program files\Recorder 6 Worksation\map files\gb base map.gsf when adding a map. Also "view map" and "map navigator tools" are greyed out, any ideas?
thanks

CyprianPayne
CCW

7

Re: Migrating a network installation from MSDE to SQL Server

Although not fully tested in this setup, I've seen Recorder 6 working successfully on Citrix simply by installing a workstation onto the Citrix box and making it available to the appropriate logins. Maps worked fine. It seems likely that you have a permissions problem with access to the C:\program files\Recorder 6 Worksation\Map Files folder (which you need full read/write privilieges on). You could test this by trying to save a notepad file in the folder, for example.

John van Breda
Biodiverse IT

8

Re: Migrating a network installation from MSDE to SQL Server

Gordon has asked if CCW can have access to the source code for Recorder?

CyprianPayne
CCW

9 (edited by petrovyoung 28-09-2011 11:13:56)

Re: Migrating a network installation from MSDE to SQL Server

When you install Microsoft Office SharePoint Portal Server 2003 on a single server by using the default (Typical) installation options, you have an installation that uses Microsoft SQL Server 2000 Desktop Engine (MSDE) for your databases. This is fine in a small-scale environment, when you are hosting just a few Web sites. If your server gets more traffic or you need to start hosting hundreds of sites, you might run into performance and storage problems. If you find yourself in this situation and need to move to a more robust solution, you can switch to using Microsoft SQL Server 2000 Service Pack 3 (SP3) or later as your database. There are two methods you can use to switch your databases to SQL Server 2000:

Upgrade the databases to SQL Server on the same computer.
Use this option if you want to continue running SharePoint Portal Server on a single server.
Migrate the content databases to a server farm running SQL Server and SharePoint Portal Server.
Use this option if you want to move your sites to a server farm, with at least one front-end Web server and at least one back-end database server.

Roofing Vancouver

10

Re: Migrating a network installation from MSDE to SQL Server

Migrating directly from MSDE to SQL Server 2005 SP2 Standard Edition is not a supported upgrade path from
Microsoft. Instead, you need to uninstall MSDE, and then install SQL Server 2005 SP2 Standard Edition. Follow
the steps below to migrate from MSDE to SQL Server 2005 SP2 Standard Edition.

11

Re: Migrating a network installation from MSDE to SQL Server

Web design can be very important to learn if you are considering doing business online. One of the best ways to earn income online is by setting up a website Web design that either promotes your own products, or promotes the products of other companies who pay you a commission when you refer customers. However, the one issue that stops many people from going forward is the necessity of designing an attractive site.