SQLCMD – Cannot Use SQLCMD in SQL Server 2008 R2

sql serversql-server-2008-r2sqlcmd

I have started SQL Server in single user mode like this: c:\....\sqlservr -m

When I try to connect to it with SQLCMD -E, I get the following error:

Msg 18461, Level 14, State 1, Server SERVERNAME, Line 1
Login failed to user 'USER\Name'. Reason: Server is in single user mode. Only one administrator can connect at this time.

The user I am logged onto the server as is a domain administrator.

Before I tried this, the user I am logged on as had nothing to do with this SQL server. (I am in fact, trying to create a new SA as we don't know what the current password is)

Edit

It seems that even if I do:

c:\....\sqlservr -m"sqlcmd"

I am still getting the above error. I am not sure if there are other SQLCMD calls happening from elsewhere.

Best Answer

This case is explicitly called out in Connect to SQL Server When System Administrators Are Locked Out:

When you start an instance of SQL Server in single-user mode, first stop the SQL Server Agent service. Otherwise, SQL Server Agent might connect first and prevent you from connecting as a second user.

When you use the -m option with sqlcmd or SQL Server Management Studio, you can limit the connections to a specified client application. For example, -m"sqlcmd" limits connections to a single connection and that connection must identify itself as the sqlcmd client program. Use this option when you are starting SQL Server in single-user mode and an unknown client application is taking the only available connection. To connect through the Query Editor in Management Studio, use -m"Microsoft SQL Server Management Studio - Query".

Related Solutions

Sql-server – Restoring master database

Start SQL Server with the -m parameter, specifying the application name allowed to take the only available connection. For instance, to allow sqlcmd to take the connection:

sqlservr -m"sqlcmd"

If you want to work with SQL Server Management Studio, use this:

sqlservr -m"Microsoft SQL Server Management Studio - Query"

Remember that the "Connect..." button in Management Studio connects the object explorer, that would take your connection without letting you open another one to run the query. In this case, open a query window either disconnected or connected to another server and then use the "Connection, Change Connection" command from the context menu.

See the docs for more information: http://msdn.microsoft.com/en-us/library/ms188236.aspx

An interesting thing to note is that the Configuration Manager doesn't let you enter -m"Microsoft SQL Server Management Studio - Query" as a parameter (I suppose it's the spaces in the parameter), so this is an option only when starting SQL Server from the command line. If you decide to do this, remember to start cmd as an Administrator.

Sql-server – With LocalAdmin become sysadmin SQL Server

For such a situation we have made use of PSExec utility to get regain access on the SQL server.

You can download the utility from here , extract and copy the exe file to the drive location from where you can run below command.

Once downloaded you need to open the cmd with admin and run below:

PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

The path above may vary, like for one of our SQL 2k8 , we found the path to be:

"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

This will make you connect to the current via sever Windows Authentication, and the username is hard-coded as NT AUTHORITY\SYSTEM.

YOu can create the required login from here:

Best Answer

Related Solutions

Sql-server – Restoring master database

Sql-server – With LocalAdmin become sysadmin SQL Server

Related Question