How do I set up a SQL Server 2000 DSN connection when moving servers?

March 15, 2008 12:49 PM

IANA IT professional, but I need to move our SQL Server database from one server to another server. I can't get the DSN connections to work now that we're on the new server; can you please hold my hand?

Our office is upgrading to a new server this weekend. Both servers are Windows Small Business Server 2003 (using SQL Server 2000).

We had one database on the old server called OURGREATDB. I detached it in Enterprise Manager, copied it onto a thumb drive, placed it in the folder that our IT consultant set up for SQL Server on the new machine, and attached it there. It looked like it worked just fine; I can browse tables, etc, in EM no problem. Now the new server is all set up on our LAN and our users have joined its domain, etc. The old server is disconnected and out of the picture.

Users on the LAN connect to the database using an Access 2003 front end and a DSN connection (I know, I know, we should switch to something better--I can worry about that on Monday once this is all working). I tried changing the DSN connection on a user's computer and it won't authenticate ('Login failed for user BOB'). This is using SQL Server authentication with the new server name, along with the same ID and password that worked on the old server. I can see user BOB in EM on the server just fine.

So I guess my question is, how do I manage the IDs and Passwords on the new SQL Server to make them work the way they did before switching machines?

I did change a setting in EM on the new server to allow both windows & sql server authentication (then stopped and started) but I don't really understand how any of this works so I'm not sure where (if anywhere) the password needs to be reentered on the new server.

If anyone can make sense of what I'm asking I'll be eternally grateful. Small words and short sentences, please--I'm a hack at this and our IT consultant has no SQL Server experience at all. Thanks!

posted by bcwinters to computers & internet (7 comments total)

Do you know if you're using Windows or SQL usernames? You are using Windows usernames if your DSN doesn't have a place for the password and username, or if there is a box checked for "Trusted connection."

If that's the case, is the new DB server part of the same domain as the workstations? Next, go to Enterprise Manager and make sure that you add a new username for each Windows account that needs access, and give it permissions to the correct database.

This MSDN article may be of help.
posted by odinsdream at 1:31 PM on March 15

Can you see the login names under security in the enterprise manager? If so what happens if you reset a password and then try again?

>I did change a setting in EM on the new server to allow both windows & sql server authentication

Dont do this. Replicate the exact same setting as the old server. Sounds like you are uncertain to whether you guys use domain authetincation or sql authentication. Do your users have a different password to log into the domain than the one they use to open that access database? If so then you are using sql authentication.
posted by damn dirty ape at 1:38 PM on March 15

Thanks odinsdream and damn dirty ape! I wasn't totally clear: on the old server we were using SQL Server authentication, not the windows usernames. I changed the setting on the new server to reflect this. The rest of the settings on the new server are all set to whatever defaults they came with out of the box.

I can see the users in the Users section of the database, so the names at least carried over with the database.
posted by bcwinters at 1:53 PM on March 15

i'm not sure if this is an issue with sql server 2000, but sql server 2005 has remote connections disabled by default. this msdn article explains how to enable remote connections.
posted by phil at 2:35 PM on March 15

You said that the user names were carried over, but what about the logins. On SQL server, you have 2 levels for the authentication. First you have the Logins to connect to the server and second these logins are mapped to the users for each database running on the server. If the logins were not copied to the new server, you won't be able to connect. Here is a link for the admin of logins on the server.
posted by McSly at 2:38 PM on March 15

McSly has it. Depending on the number of users you can either:

1.Recreate each login and map it to each user manually

2.Google around for a script that will export all of the logins from the old server.

Option 2 is easiest if you have lots of users. The script will be a SQL script that you run on the old server. The script should create a new custom script for you which you can run on the NEW server to create and map the logins.
posted by lordaych at 4:20 PM on March 15

Silly me, thinking that users meant users. I checked McSly's recommendation and it was correct. Now I've set the SQL Server-wide logins to match the single database users and all is well. All of your comments helped me grok the situation more completely but it's McSly ftw. Thanks, all!
posted by bcwinters at 6:50 PM on March 15