SQL Server Single User Mode Problem

Question

I'm trying to recover the sysadmin password. Mixed authentication set. Unable to get SQL Server in Single User mode working. I tried local administrator accounts but keep getting the following error:

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

All SQL services stopped and only two were started when the service starts. The service itself and the integration services.

Both of those services run as NT users defined by the SQL Server installation.

I've also tried: net start MSSQLSERVER /m"sqlcmd"

The GUI produces the same output when done for the Studio app. Please advise. Thanks!

Answer 1

Hello, 

Welcome to the Microsoft Community. Thank you for supporting Microsoft products. 

You’re running into a common frustration with SQL Server’s single-user mode: it’s very easy for something else (often an automated service or tool) to grab the single available connection before you can. Here’s what’s happening and how you can fix it: Why this happens 

• Single-user mode allows only one connection to the SQL Server instance.
• SQL Server Management Studio (SSMS), SQL Server Agent, SQL Server Integration Services, and even monitoring tools can all try to connect automatically, often grabbing the single slot before you do.
• Object Explorer in SSMS can use multiple connections, which will also block you.

How to reliably connect in single-user mode 1. Stop ALL SQL-related services 

• Stop SQL Server Agent, SQL Server Integration Services, and any other SQL-related services except the main SQL Server service.
• You can do this in Services.msc or via command line:

net stop SQLSERVERAGENT   
net stop MsDtsServer   
net stop MSSQLSERVER   

2. Make sure nothing else is connecting 

• Close SSMS and any other tool that might try to connect (including monitoring/backup tools).
• Disconnect your network if possible, to prevent remote connections.

3. Start SQL Server in single-user mode with a specific client 

• Open a Command Prompt as Administrator.
• Start SQL Server in single-user mode and restrict the connection to a specific tool (e.g., sqlcmd):

net start MSSQLSERVER /m"sqlcmd"   

• For a named instance, use:

net start MSSQL$InstanceName /m"sqlcmd"   

• This restricts the single connection to only sqlcmd clients.

4. Connect using sqlcmd 

• In the same Command Prompt, run:

sqlcmd -S localhost   

• If using a named instance: sqlcmd -S localhost\InstanceName

5. Add your account to sysadmin 

• Once connected, run:

CREATE LOGIN [YourDomain\YourUser] FROM WINDOWS;   
EXEC sp_addsrvrolemember 'YourDomain\YourUser', 'sysadmin';   
GO   

• Or, if your login already exists, just run the second line.

6. Stop and restart SQL Server normally 

• After you’re done, stop SQL Server and restart it without /m.

Troubleshooting tips 

• If you still get the error, something else is connecting first. Double-check that all SQL tools and services are closed/stopped.
• Don’t use SSMS to connect in single-user mode unless you use the /m"Microsoft SQL Server Management Studio - Query" switch (but even then, SSMS’s Object Explorer can open multiple connections).
• If you need to use SSMS, start SQL Server with:

net start MSSQLSERVER /m"Microsoft SQL Server Management Studio - Query"   

Then, open only a single query window (not Object Explorer). 

References 

Microsoft Docs: Start SQL Server in Single-User Mode 

How to: Connect to SQL Server When System Administrators Are Locked Out 

Sincerely,

Nam-D - MSFT | Microsoft Community Support Specialist.

Answer 2

Thanks for your response. I tried again and got this far:

Answer 3

Hello, 

Welcome to the Microsoft Community. Thank you for supporting Microsoft products. 

Based on the screenshots: 

1. You started SQL Server in single-user mode with sqlservr.exe -m"sqlcmd"
2. You tried to connect with sqlcmd but got timeout and login errors: 
   1. Timeout error [258]
   2. Login timeout expired
   3. Unable to complete login process due to delay in login response
3. SQL Server is running in Mixed Authentication mode and is using a specific service account.

Problem Analysis 

• Single-user mode (-m"sqlcmd") allows only one connection, and only from sqlcmd utility.
• If another process (e.g., SQL Server Management Studio, SQL Server Agent, monitoring tool, or even a service trying to connect) grabs the connection first, your sqlcmd connection will fail with timeouts.
• The error messages indicate that sqlcmd could not connect, likely because the single connection slot was already taken.

Solutions: 1. Stop All Other SQL Server Connections/Services 

• Stop SQL Server Agent, monitoring tools, or any other services that might connect to SQL Server automatically.
• Disable any SQL Server-related services (except the SQL Server service itself) before starting in single-user mode.
• Disconnect any remote desktop sessions that might have SQL Server Management Studio (SSMS) open.

2. Start SQL Server in Single-User Mode with a Dedicated Command Prompt 

• Open a Command Prompt as Administrator.
• Stop SQL Server (if running):

net stop MSSQLSERVER   

• Start SQL Server in single-user mode:

sqlservr.exe -m"sqlcmd"   

(Or use SQL Server Configuration Manager to add -m"sqlcmd" to the startup parameters, then start SQL Server from there.) 

• Open a SECOND Command Prompt as Administrator.
• Run sqlcmd as soon as SQL Server is up:

sqlcmd -S

(Use -S . or -S localhost for a local default instance, -S .\INSTANCE for a named instance, and -E for Windows Authentication.) 

• The first connection will take the single-user slot, so sqlcmd must be the first to connect.

3. If SQL Server is a Named Instance 

• Adjust the -S parameter, e.g.:

sqlcmd -S .\MSSQLSERVER -E   

4. If SQL Server is Running as a Service 

• Stop the SQL Server service, then start SQL Server in single-user mode from the command line as described above, not as a service. This avoids SQL Server Agent or other services grabbing the connection.

5. Check SQL Server Error Log 

• Look for lines like SQL Server is now ready for client connections. This is an informational message to confirm SQL Server is ready.
• If SQL Server is not starting, check the error log for the cause.

Best regards.
Darren-Ng | Microsoft Community Support Specialist

Answer 4

Just to be clear. The service account that appeared above is my domain login which was the first time it grabbed the session. The other account that was and still is taking precedence is the NT Service\ MSSQLServer account.