Sql-server – Can’t Login to SQL Server 2008 when running single user mode

Securitysql-server-2008

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?

Best Answer

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

Related Question