I’m back here for a little post (I hope) about something cool and useful I needed and found recently: reset the “sa” user’s password in the SQL Server despite having serious access restrictions.

In my search I found several solutions for this purpose in various conditions. I will try to list here these means pointing the situation in which they operate or help.

In my case, the tests were done in SQL Server 2008 installed on Windows 7. The real problem occurred on a Windows Server 2008 R2 + SQL Server 2008 R2 where I didn’t have the “sa” user’s password in the server (or any other user) and had no access to the Windows Authentication .

Well, the big reason for this post would be able to access even without the possibility of using the authentication for Windows account, but I’ll begin by showing some ways of setting a new password with some kind of access… may be useful if you automate this process or make the change via the command line.

 

Set password from the command line with access through Windows authentication

1. At the command prompt (MS-DOS) open the SQL Server Command Line Tool using Windows authentication:

sqlcmd -E

1.1. In the case of versions earlier than SQL Server 2008, use osql -E

2. Use the sp_password to set the new password for user ‘”sa”:

sp_password @old = null, @New = ‘newpassword’, @loginame = ‘sa’
go

3. Just quit using the exit and test login:

exit

sqlcmd -U sa

 

Set password without access by the Windows authentication

In my case, a unknown problem stopped me from logging in the database via Windows Authentication and I didn’t have the password for the user “sa”. I googled a bit and managed to regain access via login and password. Here are some possibilities and solutions for some obstacles.

 

» Access to SQL Server in local mode or single-user mode

Starting the SQL Server service in the single user allows any server administrator to connect to the database as a sysadmin. This can be done by running the command:

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn>sqlservr.exe -m

With the -m the server administrator can log in to the database using Windows authentication (either by command line or by the manager interface), even if it has not been set previously.

sqlcmd -E

Through this access, you can change the password as the previous item.

 

» Change the password via MS SQL Server Password Unlocker

Even without being able to access SQL Server, you can force a new password for a given user using the MS SQL Server Password Unlocker. It is a paid app, but for my tests I used an “alternative” version…

Installation is straightforward and does not require anything special. Once installed, you must open the master.mdf and displays the registered users. In my case, the file location was:

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf

Just change the password and voila!

 

» Changing the authentication method to the server with access through Windows authentication

Even changing the user’s password, you can not log in if the server is configured to allow only the Windows Authentication. In this mode, any attempt to log in, even with the correct password, give a simple “Login failed for user ‘sa’.” This setting can be made by the manager interface (SQL Server Manegement Studio). If necessary, start the server in single-user mode.

In this interface, to get the type of authentication option, simply:

  1. right-click in the instance/server (in my case is “localhost (SQL Server 10.0.1600 – sa)”);
  2. “Properties”
  3. “Security”
  4. Server authentication” check the “SQL Server and Windows Authentication mode”.
  5. Restart the service.

 

» Changing the authentication method to the server without any access to it

This feat can be done through the Windows registry editing. o_O’

Open the regedit from Windows and navigate to the SQL Server records. In my case the path is:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer

There should be the record “LoginMode” in the right pane. If you do not find the correct path, make a general search (CTRL + F) by “LoginMode” and you should find it.

Edit this record with the hexadecimal value “2”. The “1” means that SQL Server is in Windows Authentication Mode only. Placing “2” will use the “SQL Server and Windows Authentication mode”. Restart the service to apply the change.

For now that’s it. I hope that helps someone in moments of despair. =P

Sources:
Microsoft Support
MSDN Library
MS SQL Password Unlocker
MSDN Forum
MSDN Library

PS: This post was manually translated from the original portuguese version.