The title “Script Out Passwords” was very appealing and the post included a query that will script out any password.

seems powerful but is it real?

As suspected, it was just another April fools joke

In the above code the string “We cannot really recover passwords…” was converted to binary and wrapped with a cast function, so when the query is executed the binary code is decoded using a conversion to text function

Clearly, That’s not really good encryption since anyone can simply decode the binary string using a cast / convert function and therefore it shouldn’t be used to hide any sensitive data.

However, In SQL Server credentials are stored in a hashed form using a cryptographic algorithm called SHA

• Secure Hash Algorithms (SHA) are a family of cryptographic hash functions and you can read more about it here

SQL Server passwords are hashed with an addition of a binary string called Header and another random string that is called Salt and it is used just like salt in order to cover the password and hide it.

The hashing is performed using a function called HASHBYTES which accepts two parameters, the first one is the desired hashing algorithm and the second is the text to hash

I did a test by hashing the string 123456 and received the following result:

But what if I will create a login with the same value as the password, will the server generate a hash that matches one of the values from my test?

Let’s find out.

I created a login and pulled the generated password hash from the logins table, and I’ve failed.

the encrypted string didn’t even match the results I had from encrypting the same password in any existing algorithm.

but why?

Well, the answer is written above, the hashed string stored in the system table contains the Header and the Salt in addition to the original password

When they’re hashed together it looks like this

In order to emphasize the common denominator and the way to decipher the Header and the Salt, I will create a few more logins and retrieve the passwords

So it turns out that there is a common denominator, that’s the Header I‘ve found before and his value indicates the SQL Server version.

Now let’s try to hash the same value with the Header appended to the start

With the Header, the hashed values begin to resemble the values generated by the server after saving the password

But they are still different because the Salt is missing

I’ve since improved my test by generating a file with 2 million passwords and is available for download here

The next step was to write a script that encrypts those passwords accordingly, separate the passwords from the Salt, and then compares them with the hashed values from the server logins table.

and guess what — it worked!

I’ve discovered that passwords like LetMeIn, 123456, trustno1, 1qaz2wsx and pa$$word was common in our secured servers. the next step was to send a lot of emails and make sure that all those passwords will be updated to something a bit more secure ASAP.

do you think your server’s security is better?

now you can actually find out.

The script can be found here: Find weak passwords script

Pro tip: importing the password list into a predefined table will make the process run much faster

What exactly does the script do?

• Collect all passwords from the system table
• Hashes all the passwords from the password table according to all the existing algorithms (or those defined in the “alg” table variable) and the Salt that is decoded from the string
• Execute a small dictionary attack and output the results

Stuff you should know

Executing the script took me less than a minute with simple hardware and was tested on all the Microsoft SQL Server versions from 2008 and above

It should be noted that running time is dependent on the number of algorithms tested,

therefore more algorithms = more running time!

I have tested the SHA-2 256 & 512 algorithms only since they are the only ones that are considered safe to use today (MD2 was created in 1989 and considered deprecated)

Summary

Although there is an enforcement policy on passwords, there are millions of common passwords collected every year from sites and leaked databases. and of course, the human factor that makes people choose LetMeIn or 123456 as a legitimate password.

But luckily we can monitor this by maintaining an updated list of bad passwords and comparing it against the logins table every once in a while.

The script can be used to deploy a Brute Force attacks on the server and reveal passwords

The idea is to have a random string that is easy to remember.

Unfortunately if it’s easy to remember, it’s something nonrandom like ‘Susan’.

And if it’s random, like ‘r7U2*Qnp’, then it’s not easy to remember.

— Bruce Schneier