Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have started SQL Server in single user mode like this: c:\....\sqlservr -m

When I try to connect to it with SQLCMD -E, I get the following error:

Msg 18461, Level 14, State 1, Server SERVERNAME, Line 1
Login failed to user 'USER\Name'. Reason: Server is in single user mode. Only one administrator can connect at this time.

The user I am logged onto the server as is a domain administrator.

Before I tried this, the user I am logged on as had nothing to do with this SQL server. (I am in fact, trying to create a new SA as we don't know what the current password is)

Edit

It seems that even if I do:

c:\....\sqlservr -m"sqlcmd"

I am still getting the above error. I am not sure if there are other SQLCMD calls happening from elsewhere.

share|improve this question
    
Being a domain admin might not have any effect on login rights. Since SS2008, local administrators group is no longer added into to sysadmin role without user interaction. –  vonPryz Oct 1 '13 at 12:32

2 Answers 2

up vote 7 down vote accepted

This case is explicitly called out in Connect to SQL Server When System Administrators Are Locked Out:

When you start an instance of SQL Server in single-user mode, first stop the SQL Server Agent service. Otherwise, SQL Server Agent might connect first and prevent you from connecting as a second user.

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".

share|improve this answer
    
@RemusRasanu Thanks. I have tried that, but I still get the error. I have updated my question accordingly. –  rhughes Oct 2 '13 at 8:01
1  
"I am not sure if there are other SQLCMD calls happening from elsewhere": well, until you are sure that it does not then the simplest explanation is that it does. –  Remus Rusanu Oct 2 '13 at 8:09
    
You can try to use netstat and see who is connected to the SQL listening port to help you narrow down the search space. –  Remus Rusanu Oct 2 '13 at 17:02
    
Fantastic answer, and 100% spot on. Thanks! Using 'netstat' really helped! –  rhughes Oct 3 '13 at 5:52

Here's a potential syntax wrinkle. Even though the MS documentation, as @RemusRasanu pointed out, says to use -m"sqlcmd", like so:

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.

There's some evidence that the quotes in the parameter value prevent it from working as expected. I was having issues getting connected using single-user mode, and removing the quotes from the startup parameter, i.e. using -mSQLCMD instead of -m"sqlcmd" worked for me. I tried it both ways a couple different times to verify that there was a causal relationship, and that seems to be the case. Maybe it's some sort of buggy edge case, but it worked for me.

You can see the difference in the SQL logs as, well. For example:

2015-06-30 17:13:41.89 Server      Registry startup parameters: 
     -d c:\Prog[...]\master.mdf
     -e c:\Prog[...]\ERRORLOG
     -l c:\Prog[...]\mastlog.ldf
     -m "SQLCMD"

versus

2015-06-30 17:13:41.89 Server      Registry startup parameters: 
     -d c:\Prog[...]\master.mdf
     -e c:\Prog[...]\ERRORLOG
     -l c:\Prog[...]\mastlog.ldf
     -m SQLCMD

I stole this from this ServerFault answer, so go there and vote it up if you found it helpful: http://serverfault.com/a/338294/3183

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.