Error: Login failed for user ”. Reason: Server is in single user mode. Only one administrator can connect at this time.

I encountered this error message during a project to move system databases while having to restart SQL Server service with /f /T3608 trace flags and hence into single user mode. However when trying to log on and issues scripts for moving mssqlresource database, I constantly got denied to access by either SSMS or command line and the error message was the same as the title suggests.

After googling for awhile, here is my working solution:

1. restart SQL Server service in normal mode and change SQL Server access to local connection only (you can do this either by sp_configure or surface area configuration tool).

2. make sure that “shared memory protocol” is enabled in SQL Server configuration manager. Reason is that local connectioins to SQL Server always go through shared memory first.

And then you are all set, try to connect to SQL Server instance locally through sqlcmd and issue whatever scripts that you want. At least I didn’t encounter any trouble under these settings on that day.

Advertisements

About Nick Xu

Not a DBA anymore
This entry was posted in SQL Server. Bookmark the permalink.

One Response to Error: Login failed for user ”. Reason: Server is in single user mode. Only one administrator can connect at this time.

  1. This saved my day. Thanxs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s