How to recover a MSSQL user's password?

Question

I've been tasked with moving us from one decade old system (Access '97) to a brand new system using MS SQL Express 2008 as a backend. So great, I know I can connect to the database (successful with a read-only user), the only problem is... I don't have a user in the database with write access, and we don't have source access to the application to know what password the application uses (though, I do have the username).

So, as I can see it, I have two options:

• Recover the password for the user the application uses in a non-destructive (or undoable) manner.
• Insert an extra administrative user to the database that I can then use as a data import user.

Are either of these options viable and/or possible, and how would I go about one or both of them, given the fact that I am sitting infront of the computer with full access to do whatever.

Answer 1

Typically, this is done by starting SQL Server in single user mode and inserting a new sql admin user that you can then use to gain full access. I would avoid changing the sa password until you know if the application is using it or not.

Here is a link with instructions and screen shots for doing this with normal SQL Server. To put SQL Express in single user mode and open a query window goes something like this:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn> Sqlservr.exe -s SQLEXPRESS -m
C:\Program Files\Microsoft SQL Server\90\Tools\Binn> sqlcmd -S.\SQLExpress

Edit (in case of link rot): Once you have a query window open in single user mode you should be able to add a server admin login that you can use:

CREATE LOGIN MYADMIN WITH PASSWORD='myadminpassword'
SP_ADDSRVROLEMEMBER 'MYADMIN', 'SYSADMIN'

In your case the SQL Logins are enabled or you wouldn't be able to use your read only login, but for others they may need to ensure SQL Logins are enabled by checking or editing your registry (replacing MSSQL.1 with whichever instance you are after, but most likely it is .1). put this in a .reg file and run it (or use RegEdit):

-------------COPY BELOW---------------------------------
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer]
"LoginMode"=dword:00000002
-------------COPY TILL THIS---------------------------------

And finally if your sa login is disabled, and you want to enable it (but don't do this if your application is using sa or you may break the whole thing):

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;
GO

Answer 2

I'm not 100% sure what you're asking. It almost sounds like your application uses a hard coded username and password?!?!?

If your problem is simply resetting the 'sa' account for SQL Server here's how you can reset the 'sa' password (assuming you have physical access to the box).

1. Login to the physical machine as the Administrator.

2. Run SQL Server Enterprise Manager.

3. Right-click the server name and choose "Edit SQL Server Registration properties".

4. Choose 'Use Windows authentication' and then click OK.

5. Expand the server, expand Security then click Logins.

6. Double-click the sa login and specify new password on the General tab.