-m Startup Parameter issues

This week I delivered a disaster recovery workshop at one of clients. One of the tasks is to startup the SQL Server in a single user mode to restore the master database for example. I ran into a weird situation that I would like to share.

When you startup SQL Server in single user mode, by using the –m startup parameter, you get 1 single connection to the instance. In some circumstances there might be applications that are faster than you and will take that single connection… Result… you’re not able to connect anymore… and you will get this error.

To avoid this issue, you can limit the connections to a specified client application, for example SQL Server Management Studio or sqlcmd.
When you check the documentation, you need to add the name of the application just after the –m. Great, I only want to allow connections from the SQL Server Management Studio.
To connect through the Query Editor in the SQL Server Management Studio I have to use -m"Microsoft SQL Server Management Studio - Query" .

When I add the startup parameter in the SQL Server Configuration Manager, I get the following error:

I removed the – Query and it worked. After investigating the code in SqlManager.dll, together with my colleague PFE Nacho (blog), we figured out that a dash (-) or a forward slash (/), other than the one at the first position, is considered as an invalid value. A possible workaround for this is adding the value directly in to the registry of the server.

Open regedit and go to HKEY_LOCAL_MACHINE – Software – Microsoft – Microsoft SQL Server – (name of your instance) MSSQL12.MSSQLSERVER – MSSQLServer – Parameters and add the –m startup parameter here

I managed now to get the startup parameter in place, restarted the service so that my instance is started in single user mode and it will only allow connection from the Query Editor of the SQL Server Management Studio. Make sure when you open the SQL Server Management Studio that you don’t open the object explorer.  It might fail because it requires more than one connection for some operations. When I tried to connect to my instance, I got the same “Login Failed” error again. According to the documentation I did everything correct… After investigating the SQL code again, we have found that actually what is passed as the input string, is everything you put after the –m, including leading/trailing spaces, quotes, double quotes, brackets, etc. If that doesn’t match exactly (binary comparison) with what you pass in your login packet as the application name, then you get that Login Failed error.

Notice that if you set your startup parameter like any of these cases, they will never match to what you can pass over the connection string attribute, and you will get the error I was observing.

1. Prefixing and suffixing quotes which you cannot get as part of the application name provided by the client

-m”Microsoft SQL Server Management Studio - Query”

2. Leading and trailing blanks (spaces) which you would only get as part of the application name provided by the client if it was enclosed in quotes so that the spaces didn’t get trimmed out

-m     Microsoft SQL Server Management Studio - Query

Other reasons why you would get that logon error are because:

1. The login is not a member of sysadmin fixed server role,
2. The server is in running upgrade scripts mode and you’re not connecting over DAC,
3. The ID of the session (SPID) you already used once to connect through this mechanism is different than that of the session ID you’re trying to use right now (i.e. attempting to connect over more than one session)

Now that this is sorted out, let’s change the startup parameter into Microsoft SQL Server Management Studio – Query, without the double quotes.

After I restarted the service and tried to connect again with the Query Editor I still got the some Login Failed error… Now, I was pretty sure that my startup parameter was correct. But what about my application name in my connection?

It seems when you open a Query Editor window without connecting to the instance with the Object Explorer, the application name is change to Microsoft SQL Server Management Studio!

However, when you open the SQL Server Management Studio when the instance is not in single user mode, you connect to the instance with the Object Explorer and you open a new query window, the application name of that session is Microsoft SQL Server Management Studio – Query

To conclude, the correct startup parameter should be Microsoft SQL Server Management Studio, and not –m”Microsoft SQL Server Management Studio – Query” as mentioned in the documentation.

After the restart of my instance, I was finally able to connect with the Query Editor to my instance that was in single user mode.

We have already filed 2 defects internally to get those issues fixed and the documentation will be changed.

Pieter

Comments

• Anonymous
  March 04, 2016
  Thanks for this post which is interesting an really easy to understand. Maybe because you are belonging to a group who is in the habit to help people who don't know how to explain the problems who they are facing. In TechDays/SQL Server Days , I has met a man who was an excellent lecturer ( a french 1st Field engineer ) posting sometimes in Technet blogs but for a while , I has supposed that I was reading on of his posts .Moreover , I was astonished by your last sentence : you have done a very good work ==> +1 and thanks again...
• Anonymous
  March 06, 2016
  The comment has been removed
  • Anonymous
    March 06, 2016
    I forgot to mention, the simplest way to modify the TCP port is by using the Configuration Manger (SQL Server Network Configuration --> Protocols)