Start SQL Server in Single User Mode and allow only a particular Application to Connect

There are times when we start the SQL Services in Single User Mode to troubleshoot a critical issue but by the time the services comes up, some Application Logs on to SQL Server. Only way to get rid of this is to ask the Application Team to stop the application and then start the SQL service in Single User Mode.

In SQL Server 2005 and above, a new feature has been provided which allows us to specify the Application which should be allowed to connect to the instance when running in Single User Mode. Here is how I did it on one of the SQL Server 2008 Instances.

Opened up command prompt and issued the below command.

net start mssql$sql2008 /m"SQLCMD"

Note that the /m and the Application Name within double quotes are case-sensitive. In this case the name of the Instance is SQL2008, hence the service name is .ssql$sql2008. The /m switch instructs the Service Control Manager (SCM) to start the SQL Server service in Single User Mode. The Application Name which should be allowed to connect to the SQL Server Instance when running in Single User Mode is entered immediately after /m switch (no space after /m). In this case I chose “SQLCMD”.

To test if SQL Server allows any applications apart from SQLCMD to connect, I right away opened up SSMS and tried connecting to the instance. I was not able to connect and here is the error message.

Now it does not allow any other application to connect. Would it allow sqlcmd to connect? Yes it did !

Related posts:

1. SQL Service does not start. Error “The system cannot find the file specified”
2. Unlock SQL User without changing the password
3. How to Resolve missing MSI or MSP packages during SQL Server Service Packs, Hotfix or Cumulative Updates
4. xp_msver | Unable to determine server language version
5. SQL Performance Counters Missing. Load the Performance objects manually