02 March 2010

SQL Server 2005 (and 2008) Static Salt

While performing a database security review for a client, I noticed that the password hashes for the 'sa' user in the master.sys.sql_logins table all had the same salt. This was true on 4 separate SQL server instances across 4 different hosts.

Naturally, this piqued my curiousity, so I proceeded to investigate on as many SQL Server 2005 instances as I could get my hands on, and found that the salt was the same across the board.

To expound a bit:
If you run the following SQL statement:
SELECT password_hash FROM master.sys.sql_logins WHERE name = 'sa'

the whole password hash looks something like this:

The way that value breaks down is like so:
0x         : this is a hex value (the column is of type varbinary)
0100 : "throw away" constant bytes
4086CEB6 : the hash salt

the remainder of the value is the hashed password value.

Since we're only interested in bytes 3 - 6, we can use the SQL SUBSTRING() function to pull the part we care about like so:
  SELECT SUBSTRING(password_hash,3,4) AS sa_hash_bytes
FROM master.sys.sql_logins WHERE name = 'sa';

On each SQL Server instance I tested, the salt was the same

This was true across Service Packs, and differing versions of both the DBMS platform as well as OS.

Here's the output from 'SELECT @@version' on my test instances (minus the date and copyright):
Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
Express Edition on Windows NT 6.0 (Build 6001: Service Pack 1)

Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

I did some checking to see if this was a known issue, and was unable to find either an article/post describing this, nor an individual in the industry that had heard about it.

While this isn't a "sexy" BoF or anything, it does leaves SQL server administrative passwords open to password cracking (eg. by using a precomputed table of SHA1 hashes using the static known salt, one can dramatically decrease the time it takes to crack an sa user password...on any SQL Server 2005 or 2008 instance.) Additionally, once a password has been acquired, it may be possible to use that same password in other locations on a network if the administrators use a common password (or a common OS image for servers...).

The real risk this poses is fairly minor, since by default in the affected SQL Server versions normal users lack access to the column containing the password hash. However, there are a great deal of applications out there which use privileged accounts to access the database back end they use; and there are an even greater number of applications which contain SQL Injection vulnerabilities. In my mind, there's likely to be a fair amount of overlap in those 2 vectors, which would then leave a system potentially exposed to exploitation through this method.

Accordingly I decided to contact Microsoft. (I'll leave discussion about Full Disclosure for some other post) I have to say, it was pretty decent working with the MSRC, they were quite competent and very forthcoming. Whatever else can be said about Microsoft, it's clear that they have come a long way in dealing with vulnerabilities, which I am very happy to report.

The end result of all this is a Microsoft KB Article that explains more about the issue, along with some workarounds. According to that article, this will be fixed in SQL Server service packs at some point.

For those that are curious, the entire process took less than 3 months (I first reported the issue to Microsoft on December 11, 2009.) In my opinion, that's an acceptable time frame for a large company to address what is an admittedly minor security issue, particularly given the fact that there are a number of major (and minor) holidays which take place in that time span.


  1. WOW. Excellent post

  2. The issue is now fixed in SQL Server 2008 R2, SQL Server 2008 SP2 and SQL Server 2005 SP4. Thank you for reporting it.


  3. Please help me with this, i have :
    Microsoft SQL Server 2008 (RTM) - 10.0.1600 (intel X86)
    Copy Right 1988 - 2008Microsoft Corp.
    Enterprise Edition on Windows NT 5.1 (Build 2600 : SP3) ...
    - Does it mean i have Enterprise Edition ?
    - What else should i need to install to get final version of this installed Product ?