Help: How to fix error – Reason: Server is in single user mode. Only one administrator can connect at this time
Posted by blakhani on December 6, 2013
First of all this is not normal to start SQL Server in single user mode. So if you are reading this blog by getting this as a search result, you might be in a tough situation. In some situations, like restoring system database or during disaster recovery you may need to start SQL in single user mode.
Okay, so you have started SQL Server in single user mode by specifying start-up parameter “m” either by net start MSSQLServer /m or via command prompt sqlservr.exe –m –sInstanceName but when you are trying to connect via any tool (SQLCMD, OSQL, SQL Server Management Studio or any other) you are welcomed by error message.
TITLE: Microsoft SQL Server Management Studio
——————————
Error connecting to ‘(local)\SQL2k8R2’.
——————————
ADDITIONAL INFORMATION:
Login failed for user ‘Contoso\demouser’. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18461&LinkId=20476
——————————
BUTTONS
OK
——————————
Error message is clear that someone “else” has made connection to SQL Server and due to /m switch, SQL is not letting you get in. Now, logically there are two ways to fix this problem.
- Find out who is connecting before you and stop that application (difficult in real/disaster time)
- Ask SQL Server to not to allow anyone except me.
Second one sounds more easy. So let’s discuss that.
If you want to know the root cause of “why its in single user mode” then go back and look for SQL Error Log and you may find something as below.
In above Errorlog, we can see additional start-up parameter and warning that SQL is in single user mode.
Essentially we want to start in single use mode and no one else except you should be able to connect. Books online has explained this clearly that you can append m parameter with the client application name. Here are few example
- Only SQLCMD should be able to connect then it would be m”SQLCMD”
- If you want to use Management studio only then it would be m"Microsoft SQL Server Management Studio – Query".
Let’s see it in action. I can add start-up parameter in multiple ways. My favorite is staring SQL from command line as a service, My instance name is SQL2K8R2 so below would be the command
Net Start MSSQL$SQL2K8R2 /m”SQLCMD”
If you have default instance than it would be
Net Start MSSQLServer /m”SQLCMD”
Once you have started SQL Service in Single use mode then only SQLCMD application can connect and other connection would get error message.
Let’s see what we nave in ERRORLOG
2013-12-06 09:13:50.08 Server Registry startup parameters:
-d E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf
-e E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2013-12-06 09:13:50.08 Server Command Line Startup Parameters:
-s "MSSQLSERVER"
-m "SQLCMD"…
2013-12-06 09:13:50.80 spid4s Warning ******************
2013-12-06 09:13:50.80 spid4s SQL Server started in single-user mode. This an informational message only. No user action is required.….
2013-12-06 09:14:32.93 Logon Error: 18461, Severity: 14, State: 1.
2013-12-06 09:14:32.93 Logon Login failed for user ‘Contoso\demouser’. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]
It’s important to note that string after –m parameter is case-sensitive. This means that if you give sqlcmd (all lower case) then connection can’t be made. If we want to use management studio then the parameter would be –m”Microsoft SQL Server Management Studio – Query”. This is the exactly same name as in program_name when you look at sys.processes or sys.dm_exec_sessions
Hopefully this blog would help you in making a connection to SQL Server without stopping application, changing password, disabling account as there were the tricks I have seen to get into SQL when only one connection can be made and unfortunately that’s not you.
Cheers,
Balmukund Lakhani
Twitter @blakhani
Author: SQL Server 2012 AlwaysOn – Paperback, Kindle
Aman Ankit said
good one sir.. please keep posting.
blakhani said
Thanks @Aman
Prashant Kumar said
Cool stuff. And indeed a dedicated blog on this trick was required as https://sqlserver-help.com/2012/02/08/help-i-lost-sa-password-and-no-one-has-system-administrator-sysadmin-permission-what-should-i-do/ might go unnoticed 🙂
blakhani said
Thanks @Prashant
GS Sohal said
very help .. as always !!!!!! Thanks alot Balmukund
blakhani said
Thanks Gurpreet
Priyanka said
Thanks Sirjii.. 🙂
blakhani said
Thanks to you for reading 🙂
In Depth SQL said
Thanks Balmukund for sharing the info. Just curious to know if some user logged in through single user mode, how to track it and kill that session so others can log in ?
blakhani said
In single user mode only one Sysadmin can connect. Unless you have some other tracing mechanism (like logon trigger, logging of successful logins in errorlog etc) it would be difficult to find out who is connecting.
Myths of SQL Server: Rollback Service Pack with Resource Database? (MSSQLSystemResource) « Help: SQL Server said
[…] Start SQL in Single User Mode (Refer earlier blog) […]
sqllearner said
I am extremely impressed with your writing skills and also with the layout on your blog.
Is this a paid theme or did you customize it yourself?
Anyway keep up the excellent quality writing, it is rare to see a great blog like this one nowadays.
Anil said
Excellent article..Thanks a lot for sharing 🙂
With LocalAdmin become sysadmin SQL Server | DL-UAT said
[…] found this blog and added the application name after the m switch https://sqlserver-help.com/2013/12/06/help-how-to-fix-error-reason-server-is-in-single-user-mode-only…. But no dice, I still get the above error. The User Account looks fine I guess, I am an […]
SQL SERVER – Unable to Start SQL – Error: SQL Server Could Not Spawn Lazy Writer Thread | Journey to SQL Authority with Pinal Dave said
[…] Help: How to fix error – Reason: Server is in single user mode. Only one administrator can connect… […]
With LocalAdmin become sysadmin SQL Server | Question and Answer said
[…] found this blog and added the application name after the m switch https://sqlserver-help.com/2013/12/06/help-how-to-fix-error-reason-server-is-in-single-user-mode-only…. But no dice, I still get the above error. The User Account looks fine I guess, I am an […]
jhaamit6666amit said
what is the solution. this is only the description of the problem
c.lo said
Thank you. Good info
Boon said
Thank you so much.. I was able to fix the issue using net start MSSQLSERVER /m”SQLCMD”
blakhani said
Thanks!
harish said
It is net start mssql$sQLPRE02 /f /t3608 /mSQLCMD and do not work with quotes…i hecked msdn and it also says to use quotes but it didnt work with quotes for me
Anoob K D said
https://support.plesk.com/hc/en-us/articles/360001110234-How-to-disable-single-user-mode-in-MSSQL- try this way
n said
Thank you blakhani like a BILLION 🙂
Ratish said
Always login failed indicates
Steffen Hansen said
The did the trick for us. We tried (as other guides suggested) to add, first “-m”, then “-msqlcmd”, option to the startup parameters in SQL Server Configuration Manager – and we kept getting the “Only one administrator can connect at this time” error. So removed the parameter(s) from SSCM and used net start in a command prompt (runas Administrator of course). Then we could start sqlcmd in the same window.
Cheer – and thank you.
blakhani said
great. thanks, Steffen.