How to Reset SA Password in SQL Server 2017 Manually

Puran Kandpal | Modified: November 8th, 2018 | SQL |

In this article, we will consider the procedure on how to reset SA password in SQL Server 2017. In the event that the SA password was successfully forgotten or there are no users with administrative rights to MS SQL 2017. We will show how to reset the password of the SA account, and, if necessary, provide the right Windows account with MSSQL administrator rights.

The situation when the user SA is lost / forgotten by the MS SQL database happens quite often. As a rule, the option of reinstalling SQL and reconnecting the database is not considered; In this case, the configuration parameters stored in the system DB master will be lost.

SQL Server provides much more convenient access recovery functionality. The fact is that members of the local group of system administrators on which SQL Server is installed have full authority over the DBMS running in single-user mode. By running SQL Server in single user mode, you can reset SA password in SQL Server or grant administrative rights to the desired Windows write.

Council: Recall that starting with SQL Server 2017, the local group of system administrators on which SQL Server is installed was not added to the sysadmin role on the SQL server by default.

The procedure for starting SQL Server 2017 in single-user mode and restoring access is as follows (the main condition is that you must have administrator rights on the system on which MSSQL is installed):

We stop the service of the necessary instance of SQL Server (in our case, the name of the MSSQL instance is DBSERVER ):

net stop MSSQL$DBSERVER

Open the Registry Editor (regedit.exe) and go to the section of the SQL service startup

parameters:

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\ MSSQL$DBSERVER

Changing the value ImagePath , adding as an argument sqlservr.exe key « -m ».

In our example, this value is:

C:\Program Files\Microsoft SQL Server\MSSQL14.DBSERVER\MSSQL\Binn\sqlservr.exe

-m -s sEPOSERVERMSSQL ImagePath – SQL exclusive mode

Run the SQL Server service: net start MSSQL$DBSERVER

Now SQL is started in single-user mode and allows any member of the local computer administrators group to connect to an instance of SQL Server with sysadmin rights.

Using Management Studio, you can reset SA password or provide the necessary Windows account with the sysadmin role (This operation can be performed from the sqlcmd command line:

EXEC sp_addsrvrolemember 'DOMAIN\Username', 'sysadmin';)

It remains to stop the SQL service again, change the value of the ImagePart key to the original one by removing the “-m” attribute (

"C: \ Program Files \ Microsoft SQL Server \ MSSQLDBSERVER \ MSSQL \ Binn \ sqlservr.exe" -s sDBSERVER"

) and run SQL Server in normal mode

Council: At the time of this writing, I found that SQL Server 2017 can be run in single-user mode and from SQL Server Configuration Manager.

To do this, add the argument ” -m ” in the properties of the required instance in the Advanced tab of the Startup Parameters. In the rest – the whole procedure is similar.

Reset SA Password in SQL Server 2017 using SysTools SQL Password Recovery

There might be chances that, after trying the above manual approach user still failed to reset the password. Hence we suggest you another reliable solution to reset SA password in SQL Server

Follow the below steps to set a new password in SQL Server 2017

    • Browse the master.mdf file of your SQL Serveropen master mdf file
    • After Scanning the file, Software allow you to select the user and Reset the Password by clicking on Reset Password buttonreset sql password

Note: Software prompt for backup your master.mdf file before going to reset SA password in SQL Server

  • In password settings windows, set the New password for user/SA account and then click on Ok

password reset

  • The password has been successfully reset by SQL Password recovery tool

reset sql sa password

  • Now connect with SQL Server using new password

Conclusion

It is perfectly normal to forget the password from time to time, and this does not mean that there will even be minor inconveniences. The recovery measures described by me will allow you to reset SA password in SQL Server 2017 & below versions without downtime and password decryption.

Summary
Review Date
Reviewed Item
How to Reset SA Password in SQL Server 2017 Manually
Author Rating
51star1star1star1star1star