none
SA is disabled; can't login single user mode to re-enable SA

    Question

  • For SQL Server 2012, I am trying to enable sa, which has become disabled somehow.  I have a domain account (part of domain/admins), so I am trying to put the service into single-user mode as suggested here: http://msdn.microsoft.com/en-us/library/dd207004.aspx

    by running SQL Server Configuration Manager, right-click on the service, and adding the Start-up Parameter of  -m"Microsoft SQL Server Management Studio", then restarting the service.

    I have halted all related services I can think of, including SQL Agent, Sharepoint, World Wide Web Publishing, Team Foundation Services Administration, etc. but whenever I start SQL Server Management Studio (in Administrator Mode) & attempt to connect with Windows Authentication, I get the error:

    Login failed ... Server is on single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

    Also, I've looked in the Event Viewer > Application logs & all I see are the normal restart messages for SQL Server, and login failures for Sharepoint & myself.

    So looks like some service keeps logging in ahead of me.  Any way to figure out what is connecting first & thus preventing me from connecting?


    Scott D Duncan



    Monday, September 17, 2012 9:40 PM

Answers

  • I'm closing this thread; I found another user who had administrative privileges and he was able to login to SLQ Server & re-activate the sa account for me.  Since any other readers of this thread are unlikely to have that option, I hope the other suggestions posted here prove fruitful to you.

    Scott D Duncan

    • Marked as answer by Scott D Duncan Wednesday, September 19, 2012 2:49 PM
    Wednesday, September 19, 2012 2:49 PM

All replies

  • You can disable the network adapter, change the port number, disable SQL Browser, etc. while you start SQL Server in single-user mode. Plus, make sure that you do not open Object Explorer in SSMS when you connect since that also starts a separate connection to the database

    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn

    Monday, September 17, 2012 10:03 PM
  • Hello Scott,

    During installation you have selected "Windows Only" authentification mode, therefore all SQL login like "Sa" are disabled and even when you enable them, they won't work. You have to change the auth mode first to "Mixed Mode" = "Windows and SQL Logins", see Change Server Authentication Mode

    Afterwards you have to logon with an account of the SysAdmin role to enable accounts and give them a strong password. If you don't have a logon with SysAdmin permission, see Connect to SQL Server When System Administrators Are Locked Out, as you already wrote. But you also have to temporary disable the "SQL Server-Agent" service, because this also login to SQL Server as soon as it's available.


    Olaf Helper

    Blog Xing

    Tuesday, September 18, 2012 4:20 AM
  • If you think the problem is that some other process is grabbing the only connection (in single-user mode) before you can get it, you can start SQL Server from the command line (How to: Start an Instance of SQL Server (sqlservr.exe) http://technet.microsoft.com/en-us/library/ms180965(SQL.105).aspx ) and add a parameter such as -m"sqlcmd" (see Using the SQL Server Service Startup Options http://technet.microsoft.com/en-us/library/ms190737(SQL.100).aspx ) so you can limit the incomming connection to the SQLCMD program. That might help keep some other process from snatching the connection.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Proposed as answer by vr.babu Wednesday, September 19, 2012 9:09 AM
    Tuesday, September 18, 2012 3:37 PM
  • Thank you for your response. AS noted in the original entry though, I have tried all of these things, but for anyone else reading this post, these are the items to try first.

    Scott D Duncan

    Tuesday, September 18, 2012 3:43 PM
  • Thanks for your response Edwin.  Wow, hadn't thought of disabling the network adapter, etc. so good advice, but I'm going to try the command line option presented by Rick (below) first.

    Scott D Duncan

    Tuesday, September 18, 2012 3:46 PM
  • Thank you for your response, Rick.  This looks like the best approach to try first.


    Scott D Duncan

    Tuesday, September 18, 2012 3:48 PM
  • I'm closing this thread; I found another user who had administrative privileges and he was able to login to SLQ Server & re-activate the sa account for me.  Since any other readers of this thread are unlikely to have that option, I hope the other suggestions posted here prove fruitful to you.

    Scott D Duncan

    • Marked as answer by Scott D Duncan Wednesday, September 19, 2012 2:49 PM
    Wednesday, September 19, 2012 2:49 PM