Restoring the SA user password of a MS SQL Instance

October 30, 2018 in windows ‐ 3 min read

Before making any changes to the server, please create a backup. In case you are working on a VM I recommend cloning it before trying this on a live instance.

Download the PS Tools, you are going to need them.

Login using a DomAdmin to the SQL Server and create a local administrator.

net user restoresql /add net localgroup Administrators /ADD

Keep in mind that the local group name depends on the locale… m( You can of-course use the MMC Snap-In for local groups and user for this as well.

Then start the SQL Server Manager and connect to the SQL Instance using Windows authentication. Rightclick on SQL Instanz > Security > Logins and select new login… and select the local admin you just created.

Do not try and give this account any additional rights at this point. It will fail as you do not have sufficient permissions.

image

Once this is done log off and log back in using the local admin.

Run the  SQLServerManagerXX.msc located in the C:\Windows\SysWOW64\SQLServer folder The filename depends on the SQL Version you are running.

image

Select the SQL Instance that you do not have the SA Password for, stop it and then go to preferences.

image

We will add the start parameter -m to that instance, forcing it to start in the single user mode. Start it the same way you stopped it.

image

Now we need to get a SQL Server Manager running using windows Authentication as NT-AUTHORITY\SYSTEM. This is where the PStools come into play.

Look at what file the shortcut of the SQL Server Manager is pointing to and copy that path.

image

Run a administrative CMD and switch into the path of the PS Tools folder. Here you will run psexec.exe with the parameters -i and -s “Path to the Ssms.exe”.

Depending on the MS SQL Version you are running, itwill look something like this.

psexec.exe -i -s "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\ManagementStudio\Ssms.exe"

image

Now a SQL Management Studio in the user context of NT-AUTHORITY\SYSTEM gets started and you can use windows authentication.

image

You should notice that you will now see a lot more accounts in the login part of the Instance. From my understanding this should be enough to reset the SA password, however I could not get it to run. That is why I did go the extra mile of creating the local admin and adding it to the instance.

Go to the restoresql user, Rightklick > Settings > Serverroles and give him at least sysadmin.

image

Then go to File > Save all and quit the SQL Server Management Studio.

image

Now go back to the SQL Server Configuration Manager > stop the SQL Server > remove the parameter -m and start the server agin. I think I do not need to screenshot that part again. Start the SQL Server Management Studio again using the windows authentication of the local admin.

image

You can now change the Password of the SA User.

image

Do not forget to save all Alles speichern and try logging in as the SA user.

image

And now you are SA.

image

I hope this can one day help someone.

Cheers, Ori