It’s that time again. The second Tuesday of each month we have a blog party called T-SQL Tuesday. The host picks a subject and we all blog about it. It was originally started by Adam Mechanic (b/t) almost 5 years ago. This month Sebastian Meine (b/t) is hosting and he’s picked passwords as our subject.
Let’s start by saying that P@ssw0rd1 is not a good password.
For anything.
Ever.
That has absolutely nothing to do with what I wanted to talk about but it seemed worth pointing out.
Over the last couple of years I’ve written about transferring SQL Server passwords using the password hash a couple of different times. (Here and Here) and of course I use it in my sp_SrvPermissions script. So SQL Server stores the passwords for SQL Logins as a hash. But what exactly is the password hash?
Hash algorithms are also one way. That means that even though you have the hash value and the hashing algorithm it would be all but impossible to re-construct the original value. So because SQL Server only stores the passwords as hashes it’s all but impossible to retrieve someone’s password. I can think of one very specific way to get out a clear text password (cc Argenis Fernandez (b/t)), but it only works under certain circumstances and I’m not going to describe it here.
So if SQL only saves the HASH how does it know you’ve typed in the correct password? Well, every time you type in your password it gets hashed. That hash value is then compared to the stored hash value and if there is a match then you’re in.
SQL Server uses one of the SHA hash algorithms. You can tell which one by using the system function LOGINPROPERTY (”,’PasswordHashAlgorithm’). I’m not sure how it determines which to uses but I would guess it’s a matter of SQL Server version.
Here is an example of one of the hashes on my SQL 2012 instance using SHA-2:
0x0200029F2AA1A1242AF60B3EE3432C6D6E1343E0D96180430DAC2B5FD25C79A106F56B7476D197228C503CCDA3C72574FEA48D24382F007AB5399C2C19324CA39DC77986FB01
As you can see it’s fairly long, and given that the output of a hash algorithm is fixed-length all SHA-2 hashes are going to be this long. Of course there are several SHA-2 algorithms with different lengths but I’m fairly sure SQL Server only uses one of them.
There is of course always the possibility of a collision, or two strings generating the same hash value. But as I understand it the possibility of two strings generating the same hash is amazingly small. In fact here is a really good answer on stackoverflow that discusses it.
Hashing is one of the simplest methods of encryption because of course there is no need to store an encryption or decryption key. It’s encrypted using the hash algorithm and there is no decryption it. This does however make it perfect for storing passwords. Once the password is stored as a hash it’s all but impossible to decrypt it. At that point it’s all on you to pick a good password.
123456 is another really bad idea for a password. Just saying.
Filed under: Microsoft SQL Server, Security, SQLServerPedia Syndication, T-SQL Tuesday Tagged: Argenis Fernandez, hashing, microsoft sql server, security, T-SQL Tuesday 
