Unanswered: ODBC/DSN connection problems to sql server 7
A company pulled me in and wanted me to help with their sql server after a raid failure.
They retrieved the data off the drive and re installed on a virtual server.
They hired another company to install sql 7 from scratch and import the data.
The server hosts their website which ties to the SQL server and 2 programs which store data on the sql server.
Now is the part i am confused on and am sure you guys will know it.
I think everything is setup to work through odbc connections. (dsn)
The website is showing this error
Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'bhPress_user'.
/connect.asp, line 15
On the server there are system dsn's and file dsn's in the odbc list.
Since the company installed SQL 7 from scratch i believe sql lost all the login information? Am I on the right path?
How do I go about setting up all these connections back up? I can provide any code you need.
All help is greatly appreciated and thanks for your time guys.
The easiest way to get the login information back is to restore the master database from the old machine. A number of things would need to be made sure of beforehand. Mainly are all of the paths to the files the same as they were before. If not, you could have all sorts of fun stopping and restarting in single user mode to fix the system databases (model and tempdb will be the most important).
Alternatively, you could try to redefine the logins. I would only do that if there were only a handful of them, though. Say 5 - 10.
You may also want to restore the msdb database, just to make sure that you get any DTS packages, or SQL Agent jobs they may have defined over the years.
To manually create the logins, you would have to first create the login (with the password). Then you can reset the SID with the procedure you found above. The real trick is tracking down all the passwords, which is why it is generally easier to restore the master database.
when i get the the master.mdf open i go to sysxlogins and the names are there but the sid and passwords are displayed as binary data.
Are my only two options manually adding the users in and educated guess the password? Or try and see if the client has an archived backup somewhere and restore that and script out passwords(he is looking for a backup).
Nothing like working on a virtualized windows 4.0 system! Sooo far behind the times!