Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Securing the SA Account in SQL Server 2005

By Ken Johnson,

Securing the SA Account in SQL Server 2005

We've all heard of the 'SA' account, the built-in SQL authentication (user name / password combo) administrator account: that terrible thing that is the root of all vulnerabilities. Okay, maybe not all vulnerabilities but, enough SQL Server vulnerabilities that you occasionally hear about it.

Microsoft recommends using Windows Authentication, not mixed mode. SQL Server Authentication mode has been included in the last two versions of SQL Server allegedly for "backward compatibility" [1]. To my knowledge, it has not been deprecated so, it probably won't be going away any time soon.

With this in mind, what can we do to mitigate the risk posed by this account?

Even if you do use windows authentication, be sure to set a strong SA password (install as mixed mode, so you can set the SA password, then come back and change to windows mode). The authentication mode can be changed to mixed mode by changing a registry key and restarting the service [2]. If this happens, and you have not set the SA password, you now have an available SA login with no password (a great reason to lock down the registry, too!)

Microsoft has two definitions of a strong password. One states that a strong password should be at least seven characters in length [3]. The other states that a strong password should be at least six characters in length [4].

I've played around a little bit with NGSSQLCrack [5], and its authors are relatively certain that they can brute force any SQL Server password under 8 or 9 characters in 15 hours or less. Since we're not going to be using this account for general login purposes (right?), we shouldn't be stingy with the characters. There are 128 available, let's use a goodly portion of them. What's wrong with a 50 or 100 character password with mixed case, numerals, and special characters? If we weren't meant to use all those keys, they never would have been put on the keyboard. Remember, it's only for emergencies anyway; convenience is not an issue here. Maybe we could use something like the following:

ALTER LOGIN sa 
  WITH PASSWORD = 'Ple@se_Don''t_C0mprom1se_My_SQL_S3rv3r_Bec@us3_It_Is_V3ry_Ne@r_And_D3ar_To_My_H3@rt'

What if you must use mixed mode authentication, due to organizational inertia, legacy / 3rd party applications, or some other reason?

SQL Server 2005 provides a few useful features to help mitigate the risk associated with the SA account. It allows us to enforce password policy (length/complexity), to disable an account (including the SA account), and it also allows us to rename the SA account.

Password policy is a great tool to make sure people aren't creating SQL logins with weak or missing passwords. It also allows for locking accounts after a set number of failed logins. This helps prevent the password cracking tools from doing their job. I don't believe, however, that you can lock the SA account out in this manner.

Disabling the SA account is a good way to prevent its use, as well. If it's not enabled, we can't try to use or abuse it. Of course, this prevents us from using the SA account in an emergency; a capability your organization may require. Just for fun, let's disable the SA account:

ALTER LOGIN sa DISABLE

We can also now rename the SA account. I've seen organizations rename domain and local windows administrator accounts to help obfuscate their purpose. We can now replicate this behavior in SQL Server. Unfortunately, these domain and windows accounts all have an RID of 500 so, the bad guy windows hacker knows right where to go to break them. SQL Server has a similar situation. The product allows you to rename the account but, the SID is always 0x01. Does this give the bad guy SQL hacker a starting place for breaking the SA account? I suppose it might. After reading Jesper Johansson's article on securing the local and domain administrator accounts [6], I begin to wonder about the effectiveness of renaming the SA account. At any rate, until it's uselessness is confirmed, I think it remains a reasonable step to take. Let's give it a try:

ALTER LOGIN sa WITH NAME = alterEgo

If we were able to avoid mixed mode authentication, put a strong password on the SA account (maybe, even, rename and disable it), and to secure our registry, we've gone a long ways towards keeping the SA account locked up tight.

Footnotes

[1] http://msdn2.microsoft.com/en-us/library/aa176599(SQL.80).aspx
[2] http://support.microsoft.com/kb/285097
[3] http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_6hgz.asp
[4] http://msdn2.microsoft.com/en-us/library/ms143705.aspx
[5] http://www.ngssoftware.com/products/database-security/ngs-sqlcrack.php
[6] http://www.microsoft.com/technet/technetmag/issues/2006/01/SecurityWatch/?topics=y

Total article views: 16412 | Views in the last 30 days: 19
 
Related Articles
FORUM

Passwords - case sensitive in Login using SQL Server Authentication

Passwords - case sensitive in Login using SQL Server Authentication

BLOG

SQL Authentication – Forcing Password Changes

When you create a SQL Server login (with SQL authentication), you have the option of enforcing passw...

FORUM

sa account authentication

when sa password is different on 2 servers, how does authentication work

FORUM

Reset Password for linked server login

Reset Password for linked server login

FORUM

restrict windows authentication login

restrict windows authentication login

Tags
security    
sql server 2005    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones