T-SQL Tuesday #58: Passwords


September 9, 2014 by Kenneth Fisher

T-SQL Tuesday

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.

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?

Hashing is the transformation of a string of characters into a usually shorter fixed-length value or key that represents the original string.

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:


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.

2 thoughts on “T-SQL Tuesday #58: Passwords

  1. […] Measuring is hard! Insights using Data Visualisation Power BI In A Jiffy: Composition in Power View T-SQL Tuesday #58: Passwords Azure DocumentDB 101 with Ryan CrawCour PowerShell Snippets A Great Learning Tool Take Care When […]

  2. […] T-SQL Tuesday #58: Passwords […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: