How to reset lost sa password in SQL express

If you are in front of a system where there is a SQL Express installed and you don't have the sa password for login in, you're basically stuck, because you have no way to log in and if you login as Windows user you have no rights over the instance and all commands will get you nice and long error message saying that you have no rights to do so! The mixed mode is Windows + SQL Server authentication. There can also be Windows authentication only. (it's a radio button to choose from). How to reset lost sa password in SQL express in this scenario?

Normally it's an easy process to change the authentication mode, but you must have the rights to do that….  All you have to do is to right click the SQL server Management studio object explorer > Properties > Server authentication > security > select new authentication mode > OK. But this won't happen as you have no rights.  So to help you out, follow today's guide.

This situation can easily happens in case you inherited an administration of server system with some SQLExpress DB installed or during some consulting at client's site… In many cases this can come handy and saves your day. .

You'll have to first change the authentication mode from SQL server authentication into Mixed mode > Then enable the sa user name > Do a reset of the sa password (assigning new password to the sa user).

How to reset lost sa password in SQL express – the steps:

First – it's necessary to change the authentication mode into Mixed mode. We'll use a registry hack for that as this is the only (I know) way to do that.

Open the registry editor and go to here:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.10.SQLEXPRESS\MSSQLServer

Change the value of LoginMode from 1 to 2

Second – Now you can Enable the sa user account and do a reset of a password.

• You'll have to first start services.msc console and stop the SQLEXPRESS service
• Enter -m into the “Start Parameters” field
• Start the service
• Open command prompt and enter this command:

osql -S YOUR_SERVER_NAME\SQLEXPRESS -E

so in my case it was a mirage server VM which netbios name was mirage. So I entered:

osql -S MIRAGE\SQLEXPRESS -E

It depends of the name of your system…..so dont put the name ‘YOUR_SERVER_NAME'… -:) I won't work..

Third – there will be a prompt with numbers in front. Go and enter exactly this (except the new_password)

1> alter login sa enable
2> go
1> sp_password NULL,'new_password','sa'
2> go
1> quit

note in this example the password is Totogogo007*

• Stop the SQLEXPRESS service once again
• Remove the -m from the start parameters field
• Start the service

You're done! Now you should be able to login into SQL server management studio by using the sa user account and the new password…..

During the installation of SQL server database (express or standard) the DB engine is set to either Windows authentication mode or SQL server and Windows authentication mode.

Source: Partly from MSDN

Update: There is another option to gain an access to the SQL server (without restarting the DB). Thanks to Genadi for the comment and source.

Step 1: login with an acccount which is in the local administrator group

Step 2: Use PsExec to start the SQL server management studio as NT AUTHORITY\SYSTEM account which has an access to SQL server

From the source:

Source here

Download and extract PsExec.exe. Start an elevated command prompt (Shift + Right-click, “Run as Administrator”). Run the following command, adjusting for your actual path to Management Studio, which may be different:

PsExec -s -i “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe”

This command tells PsExec to run SSMS interactively (-i) and as the system account (-s).

While I haven't tested this personally, it seems that the NT AUTHORITY\SYSTEM account does the trick…. Good stuff -:)