Tag: sql server

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/

The sa account is a well, known, built in account for SQL Server. Years ago, in previous

versions, I’d see people often use the “sa” account for development, usually with a blank password. Even those speakers and experts often showed demo code with a blank “sa” password.

Ugh, a horrible example that was repeated over and over until the SQL Server setup program stopped allowing blank password. However people then had “12345” or other simple things to type on stage. I guess some habits never change.

In any case, I saw someone ask recently about changing the “sa” password in response to auditors’ requests. This person asked about how to randomly set this on a regular basis.

It’s easy. Even if you have Windows Auth only, you can use this script.

DECLARE @pwd UNIQUEIDENTIFIER = NEWID()
, @new VARCHAR(50);

SELECT @new = CAST(@pwd AS VARCHAR(50))

EXEC sp_password @new = @new, @loginame = ‘sa’

That seems to work fine in testing. I get a new sa password each time, whether I have Windows Auth or Mixed mode set. If I change the password with only Windows Auth, the last password set works if I change to mixed mode.

This is a great little script to set up in a job and run it monthly. This will protect your sa account in case someone ever enables mixed mode.

If you need the account, just change the password then, for the job/application that needs to run. Then run your job again to reset it.