single user

SQL Single User Mode Logon Failed

Ran into an issue today with one of our SQL instances where we needed to bring it up in Single User mode to recover the sysadmin role, however every time we tried to login we got the fateful “Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)” error.

Found this great page on MSDN which you should pay close attention to if you are in a clustered environment as we are:

http://msdn.microsoft.com/en-us/library/ms188236.aspx

The important thing to note is that the cluster resource .dll will allows get the first and only connection before you have a hope in hell of getting it. The way around this is to start the MSSQL$INSTANCENAME using the net start command manually on one of the nodes and not using the Failover Cluster Manager.

This will allow you to use sqlcmd to generate the login you need to regain access.

Enjoy 😉