Can't login to SQL Server in Single User mode
Solution 1
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
Solution 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.
Solution 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.
Solution 4
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.
Related videos on Youtube
user971102
Hello there! My name's Jordan Earls, but most people online know me as "earlz". I'm the lead developer and a co-founder of the Qtum project which brings the Ethereum Virtual Machine (ie, the thing that makes Solidity contracts function) to a UTXO based blockchain similar to Bitcoin. I've been programming since I was 13 and am completely self-taught. Low-level code like assembly and pointer arithmetic is the fun stuff for me. I also make music when I have time even though it's usually awful. Most of my personal projects are open source and BSD licensed. The majority of them are at bitbucket with the rest of them being listed on github Also, you can follow me on the twitters @earlzdotnet
Updated on September 18, 2022Comments
-
user971102 almost 2 years
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?
-
user971102 over 12 years-m can be optionally suffixed by a "client name", in my case I needed it to be sqlcmd because other things would be trying to connect and steal my connection.
-
MrEdmundo over 9 yearsRunning SSMS as Administrator is quite an important point. It wasn't working for me without that.
-
tdmsoares over 8 yearsThis was absolutely 100% the problem I had. sqlcmd worked immediately after this.
-
Gerhard Powell almost 7 yearsWhy the downvotes, when it is a solution that works?
-
K_Rol over 6 yearsPeople downvoting should explain why this is not a good answer. Otherwise the downvote is a useless as a bad answer.
-
Omzig over 5 yearsUgg, this run as admin stuff is a pain. i didn't even have to go into -m mode.
-
techvslife over 4 years@Earlz: Failing to remove the quote marks was absolutely, exactly, entirely my problem as well. (And probably saved me a whole day of work—thank you!)
-
RonJohn almost 4 yearsAnd apparently it must be
SQLCMD
notsqlcmd
. -
mwardm over 3 yearsYep, this was the fix for a colleague with a recently installed SQL Server.
-
Dan Chase almost 3 yearsI didn't downvote but the answer doesn't explain how updating UAC in Windows was related, or why it was attempted. Same thing if I were to say "I ate a pizza and it worked", except in this case it's more like "I ate a pizza in the middle of the highway and it worked" since modifying UAC is a potential security risk for an application issue. At the core, changing UAC to never notify basically makes you an admin, which might be the real issue, but instead of doing that, you're really bringing all of the shields down. If someone follows this they are worse off.