Best Practices to Secure the SQL Server sa Account

Problem

I know that best practices say to secure the SQL Server sa account. However, I’m not sure what all I should to do to protect my SQL Servers. What steps should I take?

Solution

Any time you have a well-known account, like administrator on a Windows system or sa for SQL Server, you should take certain steps to secure it. Let’s look at specifically what you should do with sa:

1. Set a hard to guess password.
2. Rename sa.
3. Disable sa.
4. Ensure that no other accounts exist named sa.

Set a Hard to Guess Password

Even if you’re using Windows authentication only, be sure to set a hard to guess password for the sa account. After all, the difference between a SQL Server accepting only Windows logins to accepting both Windows and SQL Server logins is a registry change and a restart.

Preferably, when choosing the password use a password generator so that the password will be hard to remember. While passwords generated by such generators can be memorized (most of us have done it), this tends to happen because the account is used over and over and the password is typed in repeatedly. If you never use the password, it’s likely not going to stick in memory.

But what if you do have to retain it for disaster recovery purposes? In that case, following the standard procedures for your organization with respect to protecting such accounts and passwords. Your Windows administrators should already be facing the same issues with respect to preserving particular passwords for domain controllers and particular accounts and passwords to be able to administer your Active Directory environment. The sa logins should be treated with similar security.

Rename the SQL Server sa Login

If you open up SQL Server Management Studio and you see something like this in the Security folder, you likely need to rename sa:

However, the way to check to see if this is the original sa account is to query sys.sql_logins like so:

SELECT name
FROM sys.sql_logins
WHERE sid = 0x01;

The sid, or security identifier, is important. The sa account always has 0x01. This query determines whether the login named sa is the original sa account or a new one. We’ll get to what to do if it’s a new one in a bit. If it’s the original, you should see the result like the one below:

So how do we rename it? Well, we cannot use the GUI if we open the properties. Note the name is grayed out:

The trick is to right-click on sa and choose rename:

Another option is to use T-SQL:

ALTER LOGIN [sa]
WITH NAME = [old_sa];

Then, if I refresh the Logins folder, I’ll see the sa account renamed.

Disable the SQL Server sa account

You shouldn’t stop at renaming the sa account. You should also disable it. While someone who has the permission to determine what the login is for the sid 0x01 probably can rename the account, this is a simple measure and worth the seconds it takes to accomplish. It’s another step an attacker would have to overcome to compromise the login. There are two ways to disable the account. The first is via the GUI:

You can also do so via T-SQL (make sure you specify the correct login since you should have already renamed it):

ALTER LOGIN [old_sa]
DISABLE;

Ensure no other logins are named sa

The recommendation against using the sa account for apps is well over a decade old. However, despite that, there are still apps today that still want to use “sa” specifically. As a result, some will include creating the sa account in their application deployment, even if the application is capable of using a different account. Therefore, it is always a good idea to periodically check to see if a login has been created called sa on each of your SQL Servers. You can visually check via the GUI or you could write a simple script that executes the following query against all of your SQL Servers:

SELECT sid, name
FROM sys.sql_logins
WHERE name = 'sa';

Obviously, you want no results to come back:

But will this stop you from being able to detect if sa is being used to try and connect? No, it won’t. If you are auditing for failed logins, you’ll see the following entry in your SQL Server logs if someone tries to connect. Note that it indicates that the login doesn’t exist.

What about Impersonation, Database Ownership, and SQL Server Agent?

Renaming and disabling the sa account won’t stop internal processes from being able to use the sa account. Therefore, if you have databases whose owners are sa, there isn’t a problem. This is a good thing, because some databases, like master and tempdb, require the sa account as the owner. Also, having SQL Server Agent jobs owned by sa won’t fail, either. The impersonation still works. Therefore, there’s no reason NOT to rename and disable the sa account.

What about Service Packs and Cumulative Updates?

In theory, these should install just fine even with a renamed and disabled sa account. In practice, however, there have been a couple of hiccups. Therefore, my standard practice is to script the rename back to sa and enabling of the account right before the application of any kind of update to SQL Server and then to script the rename and disabling of the account immediately after the update. This is the safest approach I have found.

Next Steps

• Learn how to use SQL Server profiler to detect if someone is logging in as sa.
• Know what to configure so you catch failed logins against your SQL Server.
• See how to audit when the passwords last changed on your SQL Server-based logins.
• Understand why using Windows groups is considered a best practice for SQL Server security management.