How to Reset and Recover SQL Server Database Password?

MS SQL Server adheres to specific security policies and authentication mechanisms to prevent unauthorized access to the server, database, and data. To gain full control over the server, a System Administrator (SA) account is created and protected with a strong password. If this password has expired or sysadmin logins were removed accidently, you can reset the System Administrator account password manually or by using a SQL password recovery tool. In this article, we will cover different methods to reset and recover the SQL Server password depending on the scenario.

Scenario 1 – If you’re able to Access the SQL Server

If you are able to access the SQL Server, then you can use the SQL Server Management Studio (SSMS) to reset the SA account password and other passwords associated with the database in this account. Here are the steps to do so:

First, log in to SQL Server Management Studio (SSMS), select Database Engine, provide the server name, select Windows Authentication, and then click Connect.
In SSMS, go to Object Explorer, expand the Security folder, and then the Logins folder.
Right-click on the SA account and click Properties.
In the Properties window, under the General page, change the password and confirm it.
On the Status page, change Login to Enabled. Click OK to save the applied change.
Next, restart the SQL Server and all its services.

Note: You can reset the SA credentials using this method only if your SQL Server is in a normal operational state and you have sysadmin privileges.

Scenario 2 - If you are Locked Out from your SQL Server

If you are completely locked out from the SQL Server and also forgot your SA account credentials, then you can recover the lost password by using the below methods.

Method 1: Create SQL Server Login

To use this method, follow the steps below:

Step 1 - Start your Server in Single-User Mode

If you have lost sysadmin access to the SQL Server instance, then you can regain access to the Server by starting it in single-user mode. In this mode, you can gain the privileges of a server-level role sysadmin, helping you to easily recover the SA password. Here are the steps to do so:

Open SQL Server configuration manager. It automatically gets installed when you install SQL Server on your system. To open this manager, type SQL configuration manager in the Windows start field and press Enter.
In SQL Server Configuration manager window, click SQL Server Services, right-click on the SQL Server service, and select Properties.
In the SQL Server (MSSQLSERVER) Properties window, click Startup Parameters and add –m or –f parameter under Specify a startup parameter, and click Add.
Next, click Apply and then OK.

This will make you a member of the local administrator group. Now, you can launch the
SQLCMD utility to connect to SQL Server instance

Step 2 - Open SQLCMD Utility

To open this utility, follow the steps below:

Open Command Prompt window on your system (enter CMD in Run utility and click OK).
In CMD window, run SQLCMD –Slocalhost.
Now, run the below T-SQL commands to create a SQL Server login:
CREATE LOGIN NwSA WITHPASSWORD=’Password@98765’;
ALTER SERVER ROLE sysadmin ADD MEMBER NwSA
GO

Step 3 - Open SQL Server in Multi-User Mode

Remove the –m/-f parameter from the startup parameters to switch from single-user mode to multi-user mode. Then, restart the SQL Server using the new password.

Note: This method works if you have enabled the mixed-mode authentication on the SQL Server. If it is not enabled, then you can face login failed issues. Also, it requires local admin access and CMD knowledge.

Method 2: Use a Professional SQL Password Recovery Tool

If you want to quickly reset the lost SA account password and other database passwords in the same account, you can opt for a trustworthy password recovery tool. You can use Stellar Password Recovery for MS SQL. This reliable tool can help you reset and recover lost SA and other user passwords for the SQL Server database. You can use the Change Password option in the tool to reset the master (.mdf) file password. It can recover passwords for multiple user accounts in SQL Server. The tool supports all the SQL Server versions, including the latest SQL Server 2022.

Conclusion

Resetting the SQL Server password is required when it has expired, you forgot it, lost sysadmin access to the SQL Server instance, or in other situations. Above, we have discussed how to reset and recover the SQL Server database password. Whether you’ve lost access to the SA account password on the SQL Server or have lost the password, you can recover or reset it using Stellar Password Recovery for MS SQL, which is a part of Stellar Repair for MS SQL Technician edition. The password recovery tool allows you to reset the server password easily and securely. Along with the password reset option, the Technician edition has tools for MDF/NDF file recovery and backup (BAK) file recovery.