SQL Server: sa locked out automatically

I had this issue at a customer of ours. The error message "Login failed for user 'sa' because the account is currently locked out" surprised me a bit. I didn't know this was possible. Actually, it is even possible to not have any admin anymore in the database, by locking out sa.

Luckily there are solutions that I would like to share with you.

You have this behaviour when you set the setting on your sa account "Enforce password policy" (Security/Logins/sa/Properties). If you uncheck this property, the sa account won't be able to lock anymore.

Now what do you do when the sa account is locked? You should run the following T-SQL Statement:

ALTER LOGIN sa WITH PASSWORD = 'yourpassword' UNLOCK

Like I said, this way, it is perfectly possible to not have a user with admin rights in your database: if you only activated SQL Server authentication. With a simple hack, you can change this as well. Just change the registery key "HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode" to 2 for Mixed mode. Then restart the SQL Server service. Now, you should be able to log in with a local administrator account and unlock the sa account like mentioned above.

Hope this was useful Smile.

Sources: MSDN / Urig

   

Comment List
Related
Recommended