SQL Server Single User Mode Connection Issue

Question

Assistance is needed to recover the sysadmin password in SQL Server with mixed authentication. The challenge involves getting SQL Server into Single User mode, but attempts using local administrator accounts result in the following error:

Reason: Server is in single user mode. Only one administrator can connect at this time.

All SQL services have been stopped, and only two services are started: the SQL service and the Integration Services. Both services run as NT users defined during the SQL Server installation.

The command

net start MSSQLSERVER /m"sqlcmd"

was attempted, but the GUI yields the same output when using the SQL Server Management Studio. Any advice on how to resolve this issue would be appreciated.

Answer 1

What actually worked is the following:

1. Stop all running SQL services in Configuration Manager.
2. Open CMD prompt as Administrator.
3. Go to the directory of the SQL Binn folder. Follow the sequence below. I used my directory path in this example. cd F:\MSSQL\MSSQL13.MSSQLSERVER\MSSQL\Binn g: sqlservr.exe -m"SQLCMD"
4. The above will place SQL server in single user mode. Then in another CMD prompt as Administrator, run: sqlcmd -S localhost
5. The above should connect fine. Once you see > it means you're connected. Then type: ALTER LOGIN sa WITH PASSWORD = 'YourNewPassword' -- Press enter
6. Type 'GO' -- Press enter
7. Type 'EXIT' -- Press enter
8. Press CTRL and C in the first CMD prompt Window that launched SQL exe in single user mode. It will prompt you to press Y or N to stop the service.
9. Restart all SQL services.
10. Try to login to SQL Management studio using the SA password you defined.