SQL Server 2008 R2 - Gandalf Edition - How To Address A Locked sa Account With No Other Means To Connect To SQL

I had a conversation with a client the other day that went something like this:

Client> Hey Matt, we can't currently use {name of software package} because it says that the sa account on {insert DB server name here} is locked out, can you help?
Me> Sure, let me take a look
I then attempt to connect with Windows Authentication with every domain and local user I can think of.
Me> How does the software connect to SQL Server (thinking that the login for the software may have enough rights)
Client> It uses the sa account
Me> Ummm, why?
Client> Oh that's just the way the vendor set it up back in 2007
Me> Do you have another SQL or Windows account that has SQL SysAdmin rights?
Client> {cricket, cricket}
I then check the documentation for the server (I surprisingly actually found something, albeit not much at all) and the only account documented was the sa account.

The client follows the best practice and either remove (SQL 2005) or do not add (SQL 2008 and up) the local Administrators group, so I was left with no means to connect to the server.  

This is when I realized that SQL Server was going all Gandalf on me:

I then flashed back to the last time I had to address this situation on a server running SQL Server 2000.  With SQL 2000 a locked sa account meant you needed to "rebuild" your system databases.  Which is to say that you're replacing them with fresh new copies completely void of any configuration information (logins, connected databases, scheduled jobs, etc...).

I was not looking forward to that prospect.

However, after a bit of Googling, I found that there was a feature introduced in SQL 2005 to allow access without "rebuilding" your system databases (this server was running SQL Server 2008 R2, and this process worked perfectly).  If you start SQL Server in Single User mode (also known as Maintenance Mode, hence the parameter of -m), any member of the local Administrators group can connect to SQL Server with full SysAdmin rights.

I fired up SQL Server Configuration Manager and stopped the SQL Server (MSSQLSERVER) service.  I then brought up the properties of the service and added "; -m" to the end of the Startup parameters on the Advanced tab (in my testing adding this Startup parameter through the Windows Services console did not work).  

Once I started up the SQL Server service, I was able to connect with a local administrator account and unlock the sa account.  I also created a dedicated SQL login for the software to use.

Finally, I added a couple other domain administrator accounts (that normally have full access to all of the client's database servers) and granted them SysAdmin rights.

Phew, bullet dodged.

Many thanks to Raul Garcia for your blog post describing this process on SQL 2005 (coincidentally written around the same the vendor installed the software package that used the sa account): http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx