SQLCMD In Single User Mode

Question

SQLCMD In Single User Mode

RonMexico

Hall of Fame

Points: 3474
More actions
September 11, 2018 at 12:59 pm

#363044

This is driving me crazy because I've done it a few times with no issue and now I can't figure out why I'm getting error messages. I first stop all services for the instance using the Configuration Manager. I then open a command prompt as an administrator and run this NET START MSSQL$<instance name> /f /T3608 to start it in master-only recovery mode. The instance starts successfully so I try to run SQLCMD -S <servername\instacename> in the same command prompt window. However, I keep receiving the error "Login failed for user 'xx\xxxxxx'. Reason: Server is in single user mode. Only one administrator can connect at this time." Is something else possibly connecting as soon as I start the service? According the the ERRORLOG the service account used to start the SQL service is the same one that is returning in the error message saying only one can access.
I've even tried running .\sqlservr.exe -c -m -s <instancename> and then the SQLCMD code in another command prompt once the service starts but have the same issue there. Am I overlooking something simple?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

RonMexico - Tuesday, September 11, 2018 12:59 PM
This is driving me crazy because I've done it a few times with no issue and now I can't figure out why I'm getting error messages. I first stop all services for the instance using the Configuration Manager. I then open a command prompt as an administrator and run this NET START MSSQL$<instance name> /f /T3608 to start it in master-only recovery mode. The instance starts successfully so I try to run SQLCMD -S <servername\instacename> in the same command prompt window. However, I keep receiving the error "Login failed for user 'xx\xxxxxx'. Reason: Server is in single user mode. Only one administrator can connect at this time." Is something else possibly connecting as soon as I start the service? According the the ERRORLOG the service account used to start the SQL service is the same one that is returning in the error message saying only one can access.
I've even tried running .\sqlservr.exe -c -m -s <instancename> and then the SQLCMD code in another command prompt once the service starts but have the same issue there. Am I overlooking something simple?

Try limiting the single connection to allow just a SQLCMD connection by using -m"SQLCMD"

Sue

RonMexico Hall of Fame Points: 3474 More actions · Answer 2

Sue - Thank you! I was able to get it to work using both NET START MSSQL$<instancename> /f /T3608 /mSQLCMD and .\sqlservr.exe -c -m "SQLCMD" -S <instancename>

As a side note, once I limit it to just SQLCMD then I can see a bunch of failed logins so something was definitely grabbing the connection before I could get to it.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

RonMexico - Tuesday, September 11, 2018 1:55 PM
Sue - Thank you! I was able to get it to work using both NET START MSSQL$<instancename> /f /T3608 /mSQLCMD and .\sqlservr.exe -c -m "SQLCMD" -S <instancename>
As a side note, once I limit it to just SQLCMD then I can see a bunch of failed logins so something was definitely grabbing the connection before I could get to it.

Thanks for posting back.
It is a pain as there are more and more things that connect right away depending on what is implemented, being used, configured on the instance. I've just gotten into the habit of specifying the application to connect. It doesn't hurt anything and saves me a few cuss words.

Sue