none
can't access SQLServer in single user mode

    Question

  • Hello:

     

    Having an extremely frustrating issue trying to connect to SQL once I've done the NET START MSSQL$SQL_DEV /c /m /T3608.

    Briefly:  SQL 2K and 2K5 named instances running on same DEV box. Can't open admin:<server\instance> in SSMS, I get 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)

     

    This happens regardless of credentials supplied (my AD acct (local admin on box), AD acct running SQL services or sa).  Object Explorer is closed, remote connections disabled in SS Surface Area Config. I open SSMS and make no attempt to connect to server.  Choose File >New > Query with Current Connection.  In dialogue box, I attemp to utilize DAC and get the above error.

    In DOS prompt where I issued the single user mode startup cmd, try to use sqlcmd to grab DAC session and receive the following error:

     

    SQL Network Interfaces: An error occurred while obtaining the dedicated administrator connection (DAC) port. Make sure that SQL Browser is running, or check the  error log for the port number [xFFFFFFFF].  Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not  allow remote connections..

     

    So, no matter what method I use, I've managed to disable ALL access to the instance, despite the tips I've seen here and on Google.

    I need to move the system databases to new disk volume, but cannot do so until I can open a connection to the instance once I've put it in single user mode.  Looking in the SQL Srvr log following startup in single user mode I see the following:

     

    Message
    Server is listening on [ 'any' <ipv4> 3560].
    Message
    Dedicated admin connection support was established for listening remotely on port 3560.

     

    Do I need to call the port# specifically when using SSMS or sqlcmd?

    I can include screenshots of the failures and mssgs if necessary.

     

    More disturbing is in a DR situation,  I CAN'T OPEN A SESSION TO THE INSTANCE, which would be a very bad thing...

     

    Please Help!

    Thursday, February 14, 2008 4:17 PM

Answers

  •  

    Have tried using both existing SSMS session and new SSMS session.  When using existing SSMS session, once I do the NET STOP and NET START with single user mode, I disconnect in Object Explorer, then close the Object Explorer.  Try New > Query With Existing Connection....

     

    and it dawns on me.

     

    Check services running on box, shut down FTS, SSIS and RptSvcs, leaving only SQL Srvr and SQL Srvr Browser services running and lo and behold, ADMIN:MTRDEV06\SQL_DEV works and opens session!!

     

    Hooray!

     

    Stay tuned, I may still need some help moving master database...

     

    Thanks All!

    Thursday, February 14, 2008 5:17 PM

All replies

  • You can try to connect using ipaddress,port.  So something like 127.0.0.1,3560.

     

     

    Code Snippet

    sqlcmd –S127.0.0.1,3560

     

     

    • Proposed as answer by Xaeon Tuesday, August 21, 2012 2:31 PM
    • Unproposed as answer by Xaeon Tuesday, August 21, 2012 2:31 PM
    Thursday, February 14, 2008 4:27 PM
  • Also per BOL

     

    Stop the SQL Server Agent service before connecting to an instance of SQL Server in single-user mode; otherwise, the SQL Server Agent service uses the connection, thereby blocking it.

     

    • Proposed as answer by gco26421 Thursday, December 22, 2011 1:45 AM
    Thursday, February 14, 2008 4:31 PM
  • If I understand this correctly then, with the instance in normal user mode, set the properties of the SQL Agent to NOT start automatically, then perform the NET STOP followed by NET START /c /m /T3608 ??

     

    Okay, went to SS Surface Area Config: 

    --disabled "allow remote  connections"

    --set SQL Agent to "disabled" and confirmed it's status as "not running"

     

    And I still cannot connect, either thru SSMS or DOS.   SSMS gives same error as above, command line also fails:

     

    Microsoft Windows [Version 5.2.3790]
    (C) Copyright 1985-2003 Microsoft Corp.

    C:\Documents and Settings\SQLService>NET STOP MSSQL$SQL_DEV
    The SQL Server (SQL_DEV) service is stopping.
    The SQL Server (SQL_DEV) service was stopped successfully.


    C:\Documents and Settings\SQLService>CD \

    C:\>NET START MSSQL$SQL_DEV /c /m /T3608
    The SQL Server (SQL_DEV) service is starting..
    The SQL Server (SQL_DEV) service was started successfully.


    C:\>sqlcmd -S MTRDEV06\SQL_DEV -A -d master -E
    Msg 18461, Level 14, State 1, Server MTRDEV06\SQL_DEV, Line 1
    Login failed for user 'US\sqlservice'. Reason: Server is in single user mode.
    Only one administrator can connect at this time.

     

    The above AD acct is the account used to run MSSQL and SQL Agent, so it has local admin credentials.

     

    Don't know what to try next...

    Thursday, February 14, 2008 4:46 PM
  • Once you start Sql Server 2005 using the command NET START MSSQL$SQL_DEV /c /m /T3608 and connect to SSMS, you need to disconnect Sql Server in the object explorer and then close object explorer. Then try connecting using new query window it will connect I believe.

     

    - Deepak

     

    Thursday, February 14, 2008 4:55 PM
  • If Deepak's recommendation doesn't work try creating a batch file with the startup and login commands in it and try executing the batch file to see if the startup/login in sequence will let it connect faster than another connection.
    Thursday, February 14, 2008 5:00 PM
  •  

    Have tried using both existing SSMS session and new SSMS session.  When using existing SSMS session, once I do the NET STOP and NET START with single user mode, I disconnect in Object Explorer, then close the Object Explorer.  Try New > Query With Existing Connection....

     

    and it dawns on me.

     

    Check services running on box, shut down FTS, SSIS and RptSvcs, leaving only SQL Srvr and SQL Srvr Browser services running and lo and behold, ADMIN:MTRDEV06\SQL_DEV works and opens session!!

     

    Hooray!

     

    Stay tuned, I may still need some help moving master database...

     

    Thanks All!

    Thursday, February 14, 2008 5:17 PM
  • You can refer this link for moving system databases, http://sql-articles.com/index.php?page=articles/msysdb.htm

    Screenshots are available which might help you.

     

    - Deepak

     

    Thursday, February 14, 2008 5:50 PM
  • I ran into the problem of not being able to get a session from one of our app servers to drop, despite its services being disabled. This of course caused problems when in single user mode, because it kept grabbing the only allowed connection. Even disabling remote connections didn't help.

    Ultimately the simple solution was to block port 1433 on the DB's firewall and check sp_who for any remaining connections and kill them off. After that I was able to continue with my single-user mode work.
    Wednesday, August 20, 2008 8:18 PM
  • This is still an issue for me. can somebody help?

    I am using the below command to put the server in single user mode

    Net Start MSSQLSERVER /c /m /T3604

    The server started successfully

    Then

    SQLCMD -S <servername> -E
    Msg 18461, Level 14, State 1, Server <servername>, Line 1
    Login failed for user 'domain\SQLadm'. Reason: Server is in single user mod
    . Only one administrator can connect at this time.

    I don't have any other connection to this sql server. SQL agent is not running. the same issue while connectiong via SSMS as well
    Friday, December 04, 2009 12:35 PM
  • The best way I have found to solve this problem is to start sql normally and watch the active connections.  Disable the logins that are connecting.  The ones that are causing you the problem will be coming up fast.  

    It's either that or you can disable tcp/ip as a connection method.  I've always found it easier to just disable the logins, though.
    Friday, December 04, 2009 7:09 PM
  • I had the same problem. Finnaly after thouthands of post I tried to run SQL Server Management Studio AS ADMINISTRATOR. And OMG it helped.

    I got the sysadmin rights for my windows account!!!

    • Proposed as answer by Cristin Tuesday, March 15, 2011 4:31 PM
    Thursday, February 03, 2011 9:45 PM
  • In the Window Services you can configure to start the SQL in your local account. Once it is done you can connect to the SQL server with your current logged in Account

    • Proposed as answer by Loren P Monday, November 19, 2012 7:44 PM
    Wednesday, June 15, 2011 3:57 PM
  • thanks Suresh.Rajamanickam. Your solution worked for me when I couldn't log on to the server in single user mode in any other way. Saved me a lot of time. thanks once again
    Simran Jindal
    Sunday, September 18, 2011 9:13 PM
  • Jonathan, you are a genius.  This worked for me! Thanks
    Thursday, December 22, 2011 1:44 AM
  • I had the same problem. Finnaly after thouthands of post I tried to run SQL Server Management Studio AS ADMINISTRATOR. And OMG it helped.

    I got the sysadmin rights for my windows account!!!

    Finally got it to work... thanks so much for posting that!
    Wednesday, April 11, 2012 6:16 PM
  • This is the "proposed answer" but has 0 votes.  The "run as Admin" answer worked for me.  It has, as of this post, 10 votes but it was hard to find lower in this thread.  It should be marked as the answer!
    Tuesday, July 10, 2012 6:00 PM
  • I had the same problem. Finnaly after thouthands of post I tried to run SQL Server Management Studio AS ADMINISTRATOR. And OMG it helped.

    I got the sysadmin rights for my windows account!!!


    Bingo!!! Don't forget about the UAC!
    • Edited by Andrew Nefedkin Monday, August 13, 2012 3:05 PM Quote original post
    • Proposed as answer by blaze4fun Tuesday, August 21, 2012 11:04 PM
    Monday, August 13, 2012 3:03 PM
  • open SSMS. dont click on connect.

    click on new query --> and then connect.. it will work , make sure that that object explorer not opens at this time


    Please mark the post as answered to help others to choose the best. chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)

    Tuesday, September 11, 2012 5:09 PM
  • Excellent stuff Jonathan. I've been scratching my head for the last 20 minutes trying to work this one out!

    Note to self: Must pay more attention to BOL.

    Once again thanks for the tip. I'm now able to connect via sqlcmd


    www.SQL4n00bs.com

    Thursday, November 08, 2012 1:36 PM
  • I can't believe I didn't try this.

    Hours of trying to find a solution to this and this works.

    Thank you

    Tuesday, October 01, 2013 11:42 AM