October 2, 2014No comments
By default, SQL Server 2008R2 when using mixed mode Windows and SQL Server authentication, sets up the SA account with a password policy, set to lock after a number of failed login attempts. This is particularly troublesome when a rogue process attempts to login with an incorrect / outdated set of SA credentials, and it’s all too easy to skip over setting up additional administrators with Windows accounts.
On my development server, where I have a number of projects underway, I naively missed the step of setting a local or domain account as an administrator, meaning SA was the only account with sysadmin privileges on the instance. This, paired with the default option of enforcing password policy on the account, meant it was too easy to inadvertently lock the SA account, losing access completely to the entire contents of the databases.
Apex SQL produce a number of SQL related tools, for the one I was trialling, one of the first steps of running their software is to setup a database connection, you enter a server / instance name, and choose Windows or SQL authentication. A helpful (but dangerous) feature is that this software appears to attempt to connect using credentials as you type, this leads to the SQL server being spammed with incorrect logins if you’re not quick, eventually leading to the account being locked.
Time to panic.
The trick, in this circumstance, is to make sure you are logged on to the server with an account with local administrator privileges. As long as you have this, you can leverage SQL’s administrative connection in Single User mode. To achieve this, shut down the SQL Server service for the instance – remember this will disconnect any / everyone on the instance, so only do this out of hours, when you have no choice, or on a server only you are connecting to.
Then, open up a command prompt with administrator privileges and navigate to the SQL executables for your instance, it’ll be something like: c:\Program Files\Microsoft SQL Server\MSSQL10_50.1\MSSQL\Binn
Run sqlservr.exe with the additional switch of -m and you’ll fire it up in Single User mode. Now, open up management studio and go and connect using Windows Authentication. With a bit of luck, you’ll be in.
Now, go unlock the SA account. You’ll have to change the password as part of the unlocking process, but go ahead and change it back once this has been completed if it’s needed.
With this complete, you can terminate the SQL Server running in single user mode by hitting CTRL + C and confirming with Y. Now, bring up the SQL Service, and normality should be restored.