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
add comment

1 Answer

up vote 6 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
add comment

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.