Security is a very vast subject – especially when it is about securing data in your SQL Server instance. Recently, I was working on revamping logins for a couple legacy applications and noticed a very interesting behaviour of Microsoft SQL Server.

Creating a Login

If you have created SQL Server logins using T-SQL in the past, i.e. using the CREATE LOGIN statement, you would recollect that using passwords not confirming to the standard complexity requirements required the use of a clause – CHECK_POLICY. Here’s an example:

USE [master]

GO

–Attempt to create a login that does not meet policy requirements

CREATE LOGIN ComplexityTest WITH PASSWORD = ‘ComplexityTest’, DEFAULT_DATABASE = [master];

GO

Executing the above statement generates the following error:

Msg 15118, Level 16, State 1, Line 2
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.

CHECK_POLICY clause

The above statement attempts to create a login using a low-complexity password which does not comply with the security policies of most organizations. While not advisable to do so, some legacy application may require the use of such passwords. It is therefore required that we use an additional clause – CHECK_POLICY = OFF when creating the login.

USE [master]

GO

–Attempting to create a login that does not meet policy requirements using the CHECK_POLICY statement

CREATE LOGIN ComplexityTest WITH PASSWORD = ‘ComplexityTest’, CHECK_POLICY = OFF, DEFAULT_DATABASE = [master];

GO

Notice that the CREATE LOGIN statement now succeeds.

Getting the password Hash

In the interest of security, most database creation and configuration scripts of these legacy applications do not have the password written on them in plain-text. The passwords are generally represented as a hashed value. To get the hash value of the password, one can use the following script:

USE [master]

GO

–Fetching the password hash value as a VARBINARY

SELECT name,

       CAST(password AS VARBINARY(32)) AS PasswordHashValue,

       sid,

       status,

       dbname

FROM sys.syslogins WHERE name = ‘ComplexityTest’;

GO

We will be using this hash value to re-create the login, but first let’s drop the existing login first:

–Cleanup

USE master

GO

DROP LOGIN ComplexityTest

GO

Creating Logins using Hashed Passwords

Now, let us re-create the same login as above, but using the hashed password this time:

–Re-create the same login with a hashed password

–NOTE: The CHECK_POLICY clause is not used

CREATE LOGIN ComplexityTest

WITH PASSWORD = 0x02009D7C372DBC2BE69433F4652733D77BCC72B7D2D7DC60C94011A933908AA7 HASHED,

DEFAULT_DATABASE = [master];

GO

Note that we used the HASHED keyword to indicate that the password supplied was not a string value, but a hashed representation of the password. Also, we did not specify the CHECK_POLICY keyword. Based on the above tests, one would expect this query to fail as well. But, it doesn’t.

Instead, the login is successfully created. If we look at the login properties using the Object Explorer in SSMS, we see that according to SQL Server password policy has been enforced (which is clearly not the case because the password used is fairly simple and fails the complexity checks. Attempting to login using this login and the simple password also succeeds.

Conclusion – This is not a bug!

The big question at the end of this experiment is whether this behaviour is a bug with SQL Server or not? In my humble opinion, it is not a bug.

Any system should not have the ability to decrypt hashed passwords – even if the hash is generated by itself. When the encrypted hash is used, the system should proceed with the assumption that all has been taken care of as expected and is in-line with the organization’s security policies. In this case, that is exactly what is happening. SQL Server does not decrypt the hash to derive the password in plain-text and validate whether it complies with the defined security standards or not. This is also the reason why when creating passwords using the HASHED keyword, SQL Server requires that one does not define the CHECK_POLICY value explicitly.

The password hash is not reverse engineered by SQL Server because it is a one-way hash. SQL Server cannot reverse the hash to determine whether the hashed password is complex or not.

Reference:

• CREATE LOGIN: http://msdn.microsoft.com/en-us/library/ms189751.aspx

Until we meet next time,

Be courteous. Drive responsibly.