If you start SQL Server in single user mode only one administrator can connect at the same time.
What’s probably happening here is that some service is using a sysadmin login to connect such as Reporting services or SQL Server agent.
When starting SQL Server in single user mode you have the option to specify a client application so only that application can connect.
Have a look at the command line options where it lists:
-m «Client Application Name»
When you use the -m option with SQLCMD or SQL Server Management
Studio, you can limit the connections to a specified client
application. For example, -m»SQLCMD» limits connections to a single
connection and that connection must identify itself as the SQLCMD
client program. Use this option when you are starting SQL Server in
single-user mode and an unknown client application is taking the only
available connection. To connect through the Query Editor in
Management Studio, use -m»Microsoft SQL Server Management Studio —
Query».Client Application Name is case sensitive.
If you are able to connect this way, change the maximum server memory to something reasonable. I suppose you will be able to connect because otherwise you would probably receive something as «there is no process on the other end of the pipe» so I would assume your server is running.
If however you can’t log in to SQL Server because your memory configuration doesn’t allow you, you can start SQL Server in minimal configuration using the -f option.
-f
Starts an instance of SQL Server with minimal configuration. This is
useful if the setting of a configuration value (for example,
over-committing memory) has prevented the server from starting.
Starting SQL Server in minimal configuration mode places SQL Server in
single-user mode. For more information, see the description for -m
that follows.
For reference: SQL Server can’t start after accidently set the «max server memory» to 0
- Remove From My Forums
-
Вопрос
-
I’m moving the master & resource databases as described on MSDN… http://msdn2.microsoft.com/en-us/library/ms345408.aspx
I’ve completed step 7… «Start the instance of SQL Server in master-only recovery mode….» and am up to step 8 which requires me to login to SQL Management Studio to run a query, but I receive the following error…
Login failed for user ‘sa’. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)
Further details produces… «Error: 18461, Severity: 14, State: 1»
I have no other open connection that I can think of.
Can anyone help?
Ответы
-
In case anyone comes upon this posting, here’s the solution…
it seems that when you login to SQL Management Studio, the Summary tab takes up the initial connection, hence, your query window is a second connection. Just disconnect and click ‘New Query’ which will open your only connection.
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
This entry was posted on December 6, 2013 at 8:00 AM and is filed under Connectivity, Error, Screenshot, Step by Step.
Tagged: disaster, only one administrator can connect at this time, restore master, Server is in single user mode, sql server, sqlcmd. You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.
I’ve been trying all sorts of different combinations of startup flags to SQL Server 2008 R2 Express and I can not get past this error:
Login failed for user ‘LOCALSERVER\Administrator’. Reason: Server is
in single user mode. Only one administrator can connect at this time.
(Microsoft SQL Server, Error: 18461)
I’ve tried starting it up in Single User mode by both -m"SQLCMD" and -m"sqlcmd" and Iv’e even tried to start it up with -m"Microsoft SQL Server Management Studio - Query" and connecting with the appropriate clients I ALWAYS get this error. I’m 99% sure that nothing else is stealing my connection and when connecting with SSMS I make sure to do the «new query-connect` method to ensure it only uses 1 connection.
Has anyone else had this problem? How do I fix it and login?
asked Dec 6, 2011 at 20:01
Here’s a solution I just found to this old issue:
- Stop all SQL services
- Start with -m
- Run MSSQL Management Studio As
administrator
If not started as administrator (as I wasn’t), then you’ll receive the generic an admin is already connect. Instead of saying you can’t connect because you aren’t an administrator.
Another confusing error from Microsoft. I suppose the same issue will exist with a sqlcmd too
Regards
Liam
answered Aug 19, 2014 at 14:38
2
Despite Microsoft saying otherwise you shouldn’t quote after the -m. So for SQLCMD, you’d do -mSQLCMD. I didn’t see this said anywhere, so I’ll document it here. If nothing else works, try not quoting it.
answered Dec 6, 2011 at 20:10
EarlzEarlz
1,0195 gold badges13 silver badges29 bronze badges
3
There should’t be anything after -m. The syntax should look like:
sqlservr.exe -c -f -m
You then log into the instance using sqlcmd and make the needed changes. If you try to use SQL Server Management Studio to log in you won’t be able to as SSMS requires several connections to log in.
answered Dec 6, 2011 at 20:33
mrdennymrdenny
27.2k4 gold badges41 silver badges69 bronze badges
2
I just had this exact problem and all that I had to do was:
- Stop the main SQL service for my instance.
- Start it.
I reconnected SQL browser and it worked again. So instead of trying all the extra steps, people should really try the simplest approach that works first before escalating to more thorough attempts.
answered Feb 13, 2018 at 7:20
1
After setting parameter -m on SQL Server Configuration Manager restart SQL Server Service ,and then run ssms as administrator then it will work 100 percent.
answered Jan 4 at 11:28
(old Post but I suffered same issue today)
Found the solution and how strange it was.
Issue is that you need to use capitals:
Window 1:
sqlservr.exe -c -f -mSQLCMD
Window 2:
SQLCMD -E -S .\SQLINSTANCE
(and the word SQLCMD needs to be in capitals!)
Tom
stormdrain
1,4397 gold badges28 silver badges52 bronze badges
answered May 9 at 15:42
Here’s a solution I just found to this old issue:
- Stop all SQL services
- Start SQl Server service with -m
- Run MSSQL Management Studio As administrator
- Now, add your windows login into Security->Logins
- Close MSSQL Management Studio
- Remove -m startup parameter
- Restart SQL Server service
- Now login to MSSQL Management Studio
And your windows login should work
answered Jun 6 at 7:57
Changing User Account Control to Never Notify resolved the issue for me
answered Aug 16, 2017 at 17:49
3
You must log in to answer this question.
Not the answer you’re looking for? Browse other questions tagged
.
Not the answer you’re looking for? Browse other questions tagged
.
-> I had to start SQL server in single user mode. I opened services.msc and then right clicked SQL Services and noted down the “Service name”,
-> I then started SQL Services in single user mode using below command from command prompt,
net start MSSQL$IN2019 /m-> It started fine, I tried connecting to the SQL server using SQLCMD and got below error,
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user ”. Reason: Server is in single user mode. Only one administrator can connect at this time..
-> I tried connecting to SQL Server using SQL Server management Studio and got below same error,
TITLE: Connect to Server
Cannot connect to JBResearch\IN2019.
ADDITIONAL INFORMATION:
Login failed for user ”. 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%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18461&LinkId=20476
BUTTONS:
OK
-> I stopped SQL services and also stopped all other SQL related services,
-> I then used below query to start the SQL server in single user mode that allows only SQLCMD to make connection,
Net Start MSSQL$IN2019 /m”SQLCMD”
-> It worked fine this time.
-> If you want to use SQL Server Management studio instead of SQLCMD, then you can use below command,
net start MSSQL$IN2019 -m"Microsoft SQL Server Management Studio - Query"Thank You,
Vivek Janakiraman
Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.