How to reset and recover SQL Server Administrator (sa) password

Reading Time: 4 minutes

Hello people,
Good afternoon!

In this post I will demonstrate how to retrieve the password for the SQL Server Administrator (sa) user, in case it was lost or changed by a malicious user or you just don't remember the password anymore. Many less experienced DBAs may be desperate with this situation and resort to reinstalling the product to recover this password and the solutions are really quite simple.

Using the SQLCMD

The first way to recover the SA user password is by using SQLCMD (To learn more about SQLCMD, access this post).

As it is always present and comes with the product, I consider it the most practical solution, although it does NOT work if the domain group login that allows connections using Windows Authentication (AD) has not been created (or removed).

To recover the password, open the Windows Command Prompt (cmd) on the server that will have the password changed and enter the following commands to connect to the database:

After connecting, simply execute the commands below to change the password:

Since sp_password will be disabled in future releases, we can also use the ALTER LOGIN command to change the password.

Example:

Using SQL Management Studio

Another alternative to performing password recovery is to log into your database locally from Management Studio using Windows AD authentication and manually change the password.

Now just enter the security options and change the SA user password (or whatever you choose)

I can't connect using Windows Authentication

Occurs when you do not remember the SA user password, the default instance role has been deleted, and you cannot connect to the instance at all.

The first step is stop the instance of SQL Server using Configuration Manager.

To open Configuration Manager, simply run the command SQLServerManager10.msc or Start> All Programs> Microsoft SQL Server -your_version-> Configuration Tools> SQL Server Configuration Manager

Now let's start the instance in single user mode (-m) or with the minimum settings (-f). To do this, type the command below at the Command prompt:

An alternative way to configure this is by manipulating Windows services:

With this, you can now log in to the instance normally using the sqlcmd command and change your password since the login will be done with the default server user even if it has been deleted.

If you have disabled Administrators role access (usually the role with the same instance name), you may not be able to log in to the instance yourself using this procedure, but you can try the solution below.

Using PsExec

A fantastic utility from sysinternals, PsExec lets you run applications using the NT AUTHORITY \ SYSTEM user, who has native access to SQL Server.

To perform password recovery, simply download PsExec on this link, extract to a local folder on your server and run the commands below at the Command Prompt (as Administrator), where the -s parameter is for using the system account, while -i enables interactive mode:

Now just create, change, or enable the users you need to administer your instance.

Using third party software

Another way to quickly recover the Administrator password is by using third party software to accomplish this task. Generally this form of unlocking is preferred by people who run SQL Server banks and do not have much technical knowledge (yes, it does) and end up opting to pay for these tools.

When you have tried ALL of the other alternatives and have not been able to recover (if all users are locked), you can use one of these tools as an option as it does not require a database connection as the tool acts directly on the file. Database Master MDF and alters the bits by forcing the password change, almost a cracker procedure.

Some of the best known tools for this are:
- SQL Server Password Geeker
- Elcomsoft Advanced SQL Password Recovery
- MS SQL Server Password Unlocker

That's it folks!
To the next

how to reset reset change change recover administrator password sa SQL Server, how to recover retrieve change lost password lost password PsTools PsExec

how to reset reset change change recover administrator password sa SQL Server, how to recover retrieve change lost password lost password PsTools PsExec