Best Practices to Secure the SQL Server sa Account


By:   |   Updated: 2015-08-18   |   Comments (3)   |   Related: More > Security

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:

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:

The sid, or security identifier, is important

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

we cannot use the GUI if we open the properties

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

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.

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:

Disable the sa account

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:

Ensure no other logins are named sa

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?

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


Last Updated: 2015-08-18


get scripts

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, January 10, 2018 - 5:03:04 PM - SQL Matt Back To Top

 I've always been on the fence about the use of SA because some of the sites that I have operated will have multiple domains with AD rarely having been configured correctly to enable the use of the proper groups.  It has been used as a quick-fix until I could get around to creating a dedicated 'admin' ID that identifies me and allows auditing.

 Seeing what you have here has swayed me to advise to turn it off.


Monday, November 09, 2015 - 2:30:51 PM - Jonathan Back To Top

Hello,

Would you happen to be able to provide your source of information regarding the statement "Renaming and disabling the sa account won't stop internal processes from being able to use the sa account. "?

Thanks!


Tuesday, August 18, 2015 - 1:49:27 PM - Password hashing notes Back To Top
"sa" passwords can easily be a full 128 characters long and be as close to truly randomly generated as your organization can supply. As the article says, store them however you securely store data you don't intend to use, but might have to copy and paste someday.


download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools