Can't Login to SQL Server 2008 when running single user mode

Question

I'm following the MS documentation here to migrate logins from one instance of SQL Server to another as part of the process described here. This involves putting the server into single user mode (which I've done) and then running scripts etc.

However, after restarting in single-user mode (with only the SqlServer service running) I am unable to log-in using either my Windows account or sa credentials. I get the error:

Failed to connect to <servername>
Login failed for user 'sa'. Reason: Server is in single user mode. 
Only one administrator can connect at this time. (Microsoft SSQL Server, Error: 18461)

After restarting the server normally (ie. multi-user mode) all the logins work fine.

Can anyone explain what is going on and/or how to correct this?

Answer 1

This might be because you are attempting to connect using SSMS. Inside SSMS, Object Explorer will open one connection to the database, while each subsequent query window will open another connection to the database.

What I have done in the past is open SSMS, do not connect to any database via Object Explorer, and open a Dedicated Administrator Connection (DAC) query windows by doing the following:

1. Click New Query
2. Type the following into the Server Name dialog box: ADMIN:ServerName\InstanceName
3. From here, you can run any scripts needed against your database in Single User Mode.

This requires the SQL Server Browser service to be running.

The real sticking point in all of this is that Object Explorer will eat up that "single user".

Hope this helps,

Matt

• immutabl: Thanks v. much for answering. AFAIK I'm not connected to ANYTHING via Object Explorer. Its late and I've been doing this all afternoon - hometime. I'll give your method a go tomorrow and report back ;-)

Answer 2

There's no need to start the server in single user mode to transfer the logins using sp_help_revlogin.

• immutabl: No really confused - no less an authority than the MS Knowledgebase article itself states this explicitly ...?
• mrdenny: I see nothing in the MSKB article about single user mode. I've used sp_help_revlogin dozens of times and I've never put the system into single user mode.
• immutabl: Sorry, my bad, it was actually part of @SQLRockstar 's answer (dba.stackexchange.com/questions/1880/…) to the bigger question I was asking.

Answer 3

In situations where you cannot connect to SQL Server normally, try using DAC.

Answer 4

I have had a similar experience with Single User mode. It is easy to think, "I started the single user mode - I should be the single user." But applications do not know that. The first ting I check is stopping any application services before starting the single user mode. Another way to stop other connections in single user mode is to deny connection to the login(s) that are connecting before you. Finding out who logged in? Turn on "Login Auditing -- audit both failed and successful logins" in the instance security properties. Start SQL in single user mode wait a bit - then stop it. Then check the SQL Error log with notepad from the log file location. Any login that is connecting or attempting to connect will show up in the SQL Error Log. When those logins are denied - they cannot get connected before you. Once you are finished with single user mode -- remember to re-enable the logins.

Answer 5

If you're a SysAdmin on SQL Server, have you thought about putting the databases (ok, so there might be quite a few) into restricted_user mode? However, the apps and other users won't have elevated permissions.

Answer 6

To migrate logins without problem, just do the following:

• start Business Intelligence Development Studio
• choose new project
• choose SSIS project
• drag and drop the task of "copy logins from server to server"
• specify source and destination
• execute

... and done.

• immutabl: Damn that's good! So ... where were you in March 2011 ...? ;-)

Answer 7

I would suggest that you start the SQL Service from the command line, and then using SQLCMD to connect to and run your scripts.