When I started working with SQL Server, the sa account was “the” account used for admin operations. This was the default account for many DBAs and as a result, it couldn’t be locked out.

This changed in SQL Server 2005, which is a good thing. We don’t want unlimited attacks on the sa account with brute force password guesses. I wasn’t aware of this, as I haven’t had an issue with attacks in a long time. However Jeff Moden pointed out to me recently that we can lock out sa.

I decided to test.

First, I went to the local policy on my desktop and checked the security policy. No lockout was set, which probably makes sense for consumer OSes. I took a minute to then set my lockout to 5 attempts with a 30 minute timeout.

I then restarted my SQL instance. I couldn’t get this to lock me out at first, so I decided to ensure the policy applied.

I then tried logging in with the sa account 6 times with the the wrong password. Each time I got this message. Including the 7th time with the correct password.

No note about being locked out. However when I check the properties for sa, I find the login is locked out.

I could uncheck the box, but I can easily use T-SQL as well.

ALTER LOGIN sa  WITH PASSWORD=’test’ UNLOCK

Please don’t use a password like this. I actually ran this to test and then reset the password to something more complex.

Reference

ALTER LOGIN – https://msdn.microsoft.com/en-us/library/ms189828.aspx

SQL Authority – http://blog.sqlauthority.com/2009/04/23/sql-server-fix-error-18486-login-failed-for-user-sa-because-the-account-is-currently-locked-out-the-system-administrator-can-unlock-it-unlock-sa-login/

Filed under: Blog Tagged: security, sql server, syndicated