MS SQL Server 2008 sa account locked out and Windows Authentication login is greyed out

Solution 1:

Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out:

Resolution

Start the instance of SQL Server in single-user mode by using either the -m or -f options. Any member of the computer's local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role.

To start the SQL with -m (from a console running as administrator):

net stop mssqlserver
cd "c:\program files\Microsoft SQL Server\...\MSSQL\binn"
sqlservr -c -m

Or, if you have a named instance:

net stop mssql$<INSTANCENAME>
cd "c:\program files\Microsoft SQL Server\...\MSSQL\binn"
sqlservr -c -m -s <INSTANCENAME>

There are alternative means as shown in How to: Configure Server Startup Options, but I always preffer start from command prompt for something like this. After you start the instance with -m:

1. Open with SSMS using Run as Administrator
2. At the first prompt don't click connect but say cancel
3. Open a new query window and then connect to the database with that query window

4. Now add yourself to the SQL sysadmin group

   create login [domain\you] from windows;

   exec sp_addsrvrolemember 'domain\you', 'sysadmin';

Now shutdown your SQL Server and restart it normally (start back the service). domain\you is now a sysadmin on the server and can administer it. You don't need to run SSMS as administrator.

Solution 2:

You can set the mixed mode of SQL Server (if you only activated SQL Server authentication) via the registry:

Default instance:

"HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode"

to 2 for mixed-mode or 1 for integrated.

After restarting the service you should be able to logon with a local administrator and reactivate the SA account again.

Another solution is use the tool - SQL Server Password Changer. It can reset your sa password as well as unlock your account in case it was locked out or disabled.

Solution 3:

You'll probably need to put it in single-user mode (Unlike in earlier versions, local Windows admins aren't automatically granted access to SQL Server 2008): http://blogs.ameriteach.com/chris-randall/2009/12/11/sql-server-2008-forgot-to-add-an-administrator-account.html