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!

Answer 1

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

 

 

Code Snippet

sqlcmd –S127.0.0.1,3560

 

 

Answer 2

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.

 

Answer 3

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

Answer 4

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

 

Answer 5

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.

Answer 6

 

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!

Answer 7

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

 

Answer 8

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.

Answer 9

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

Answer 10

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.

Answer 11

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!!!

Answer 12

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

Answer 13

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

Answer 14

Jonathan, you are a genius.  This worked for me! Thanks

Answer 15

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!

Answer 16

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!

Answer 17

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!

Answer 18

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)

Answer 19

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