I recently had an interesting challenge at work. A 3rd party app we use had stopped working, and was generating an error when it tried to start. The error suggested a problem with its MSDE database, though unfortunately it didn't provide a lot of detail. As the resident SQL "expert" I was asked to look at it, and see if I could find a way to repair the database, since the alternative would be a re-installation, and a lot of work re-configuring everything.
Now I've never really played with MSDE before, just full fat SQL, but I soldiered on to see what I could come up with. After trying the obvious things using osql I'd quickly exhausted all the ideas I could think of, and found every article, post or blog relating to database repair made reference to using Enterprise Manager... not much use to me you might think! Well with a little searching I found that you can connect to an MSDE instance using EM, but it's not that straight forward, especially if you're connecting from a different computer. All the information is out there, but even after the fact I can't find anywhere that pulls it all together... so here goes!
There are two or three steps required, depending on whether you are connecting from a local or remote EM installation.
1) Enable TCP/IP on the MSDE database.
Run 'C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe' on the MSDE machine, and then select tcp/ip and move it to enabled protocol.
2) Create a sysadmin user in the database to allow you to login.
Connect to the MSDE instance by running :
'C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql -E'
on the MSDE machine. Once connected enter the following commands (nothing will happen in osql until after the 'go' statement) :
1> use master
2> EXEC sp_addlogin 'YourSysAdminUserName','YourPasswd'
3> EXEC sp_addsrvrolemember 'YourSysAdminUserName', 'sysadmin'
4> go
Now on a local installation this may be enough, however on a remote installation you will likely receive an error :
"Login failed for user 'YourSysAdminUserName'. Reason: Not associated with a trusted SQL Server connection."
in which case you will need to change the authentication mode of your MSDE instance to Mixed Mode to allow you to connect with your new SQL login, so...
3) Change MSDE authentication mode from Windows Integrated to Mixed.
(The following is taken direct from MS KB285097)
---
To set this key to mixed-mode, follow these steps:
1. Stop SQL Server and all related services, such as SQLAgent, from the Services control panel.
2. Click Start, click Run, type regedt32, and then click OK.
3. Find the HKEY_LOCAL_MACHINE window on the local computer.
4. Navigate to the following registry key for the default instance:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer
5. Navigate to the following registry key for a named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode
6. On the right-hand pane, find the value LoginMode and double-click it.
7. In the DWORD Editor window, set the value to 0 or 2. Also, make sure that the Hex radio button is selected.
8. Click OK.
Restart SQL Server and SQL Server Agent services for this change to take effect.
Note When you change the default login authentication mode from Windows NT authentication to SQL authentication, we recommend that you change the sa password to a strong password.
---
So that was an interesting day in the office! Annoyingly after all that and many hours of fiddling I still manage to repair the problem myself. I was able to repair it with a 3rd party recovery tool, but it would have been so much more satisfying to have managed it myself.
References :
http://forums.deftechgroup.com/showthread.php?t=204
http://support.microsoft.com/kb/889615
http://support.microsoft.com/kb/285097
