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
Filed under: Blog Tagged: security, sql server, syndicated