There are many reasons you may want to ‘recover’ a password instead of resetting it. Or if you are like me, you are always interested in ‘how’ rather than ‘why’. It doesn’t really matter what your reasons are for recovering a password. What does matter is that you understand just what is involved in recovering a SQL Server login password and how you can protect yourself from a potential attack.
I am not going to go into all the minor details of Microsoft’s method of protecting the SQL Server login passwords. I will instead show you how to obtain free tools from the internet that anyone can run and just how quickly you can recover a password.
Anyone like to play games on their PC?
I personally do not play games, but my kids do. They have computers that have large and powerful graphic cards inside of them. I found out in my quest that this is a really good thing. A GPU (Graphics processing unit) is way more powerful than a CPU (Central processing unit) for running the needed algorithms for recovering a password. I will show methods that do not require a GPU, however, the time needed to recover a password on a CPU will be significantly longer.
Let’s get started.
My first demo system is;
4 GB Ram
ATI Radeon HD 7970
My second demo system is;
16 GB Ram
No video card. It’s my Lenovo T420 laptop.
Windows 7 Pro
I will use a tool called Hashcat available from http://www.hashcat.net. There are 4 tools available from that site. We will only be using 2 of them. I would encourage anyone interested in the work they are doing to look at all their tools.
If you are lucky enough to own a NVIDIA or RADEON video card, you will need the file called oclHashcat-lite, which you can get from http://hashcat.net/oclhashcat-lite/ (http://hashcat.net/files/oclHashcat-lite-0.13.7z)
If you only have a regular old PC with no GPU (most of us I am guessing) you will need hashcat from http://hashcat.net/hashcat/ (http://hashcat.net/files/hashcat-0.42.7z)
CPU Only method
Download and extract the files.It doesn’t matter where you download them to, but I placed mine on the desktop.
Inside the folder are 4 exe’s for windows and 4 bin’s files for Linux. Based on your CPU architecture, you must choose the proper file. My laptop is a 64bit system so I will be using the exe called hashcat-cli64.exe. The files in the zip are shown below.
Before we start, we need to create a file called Hashes.txt. You can name it whatever you want really, but for my demo, I am keeping it simple. After you create that file, you need to open it up and enter the SQL hashes. These hashes are the same for SQL Server 2005, 2008 and 2008 R2. There are some minor changes in SQL 2012 and I will explain those in a bit. For now, let’s open a 2005 – 2008 R2 instance and the run the following query.
-- Requires VIEW permission on the login.
-- When requesting the password hash, also requires CONTROL SERVER permission
LOGINPROPERTY(sys.syslogins.name,'PasswordHash') as password_hash
where LOGINPROPERTY(sys.syslogins.name,'PasswordHash') is not NULL
-- Any SQL Server authentication login can see their own login name,
-- and the sa login. To see other logins, requires ALTER ANY LOGIN,
-- or permission on the login.
select name, password_hash
Both return the same results. I am showing both so you know how and where you can get the hashes. This also works in SQL 2012, but SQL 2012 hashes are longer. You would change the options in one place to use them. See below.*
Open up the Hashes.txt file created earlier and copy and paste the password_hash column only into the file as shown below.
I entered 4 hashes. These hashes are for 4 separate logins on my instance. For demo purposes, the passwords are only 5 characters long but contain upper case, lower case, numbers and special characters. I made them only 5 characters long for the sake of time. Like I said earlier, the CPU only method is not very speedy.
Let’s get crackin’ (recovering)
Open a DOS prompt and cd into your hashcat-0.42 directory and enter the following command.
hashcat-cli64.exe -a 3 --pw-min=2 --pw-max=12 -m 131 -p : -o "C:\Users\galbin\Desktop\hashcat-0.49/SQL_passwords.txt" -n 2 "C:\Users\galbin\Desktop\hashcat-0.49/Hashes.txt" -1 ?l?u?d?s ?1?1?1?1?1?1?1?1?1?1?1?1
** remember, you are not galbin, so change that to your name. ;-)
You can see below how I performed this step.
The EULA comes next (shown below). Press Enter and accept the EULA by typing YES.
The tool will start working. Press Enter to get a status screen.
In a little while, depending on your CPU speed and number of cores, it will create a new file called SQL_passwords.txt.
While we are waiting I will explain the options. First let's look at hashcat-cli64.exe.
-a 3 – the attack mode. 3 will indicate we are using brute force. **
--pw-min=2 --pw-max=12 – at least 2 characters long and not more than 12 characters long.
-m 131 – this means it is a SQL 2005 – 2008 R2 hash. You would use -m 1731 for SQL 2012 and 2014. All else is the same.
-p : -o "C:\Users\galbin\Desktop\hashcat-0.42/SQL_passwords.txt" - Our output file name and location.
-n 2 – The number of thread counts to use.
"C:\Users\galbin\Desktop\hashcat-0.49/Hashes.txt" – the name and location of our hash file.
-1 ?l?u?d?s – **the type of characters we want to try using brute force. l = lower case letters, u = upper case letters, d = numbers and s = special characters. (!@#, etc). You could also just use ?a for ALL.
?1?1?1?1?1?1?1?1?1?1?1?1 – number of position to the pw-max. Since we said pw-max = 12, we place twelve ?1’s. If we only wanted to go out to 10, we would only use ten ?1’s.
When the process is complete, we are left with a file called SQL_passwords.txt. That file will contain the recovered passwords listed by the hashes. You can see the passwords below, separated from the hash by a colon.
On my laptop, you can see it took 6 minutes. Of course, these were very short passwords. They were only 5 characters long. If you were to attempt this with passwords that were 8 characters long or more, it might take months to recover the passwords. Not a very practical solution to recover the passwords. When I have long passwords to recover, I move over to my Ubuntu server.
GPU method - very fast and sort of alarming
Ubuntu is NOT required to make this work. You could also use Windows with a GPU installed, however, my system is not running Windows. The command line is almost the same as the previous method, with a few differences.
When using oclHashcat-lite, the cracking is done with one hash at a time. If you wanted to pass in multiple hashes like we did above, we would use oclHashcat-plus. However, I wanted to demonstrate pure speed. And oclHashcat-lite is by far the fastest method.
I will not explain How to install and configure Ubuntu with a Radeon GPU. Those instructions are here: https://hashcat.net/wiki/doku.php?id=linux_server_howto
For the demo, I want to show how we can recover a single password. In my example above, I will choose the first hash, which is saved in the file SQL_passwords.txt. You can see it below in the first line:
It took 6 minutes to recover with the CPU. When I run on a GPU system it takes less than 2 seconds.
The command I pass is:
./oclHashcat-lite64.bin -m 132 -p : -o "/root/HashCat/oclHashcat-lite-0.13/SQL2008_passwords.txt" --gpu-temp-abort=100 --pw-min=4 --pw-max=12 -1 ?l?u?d?s 0x0100e8ad828763d96e4fbf45d2db223940dcca99b2ebfd03dec0 ?1?1?1?1?1?1?1?1?1?1?1?1
-m 132 means SQL 2005 – 2008 r2. This is not ready for 2012 yet, but will be soon. There are methods to recover SQL 2012 passwords using SHA1 512 salt.hash methods, but these methods are not explained here.
Everything else should look the same or at least understandable from the previous example. I do pass a temperature to quit at so I do not kill my GPU card. You can see the –gpu-temp-abort=100 command. At 100 degrees Celsius, it will automatically stop.
The hash is also shown. 0x0100e8ad828763d96e4fbf45d2db223940dcca99b2ebfd03dec0
Remember, we get the hash from the T-SQL example above. I run the command and the process starts.
That's right. 2 seconds. A 5 character password that contains Upper case, lower case letters, numbers and special characters. The output file it saves looks the same as the CPU version.
I was able to get 0x0100e8ad828763d96e4fbf45d2db223940dcca99b2ebfd03dec0:H@rd1
If I wanted to get a recover a hash from an 8 character password that only contains lowercase letters and numbers, it only takes 2 minutes and 39 seconds!!
That is really the message here folks. How long and complex are your passwords? I can recover a 8 character password in under 3 minutes.
I knew an associate once that would tell me he used random strings of numbers 14 characters long. He would swear up and down that his passwords were safe. On my GPU system I can recover 14 characters of numbers in less than 40 minutes.
Are your SQL Login passwords safe? Are they long and complex or made easy for you to remember? While tools like Hashcat are great for helping you administer a database, they can also present a huge problem. Check them now and often to keep your data safe.
I removed the references to --outputfile format because it is not needed for these examples. I also updated the scripts for version 0.49. I did not update the Ubuntu portion as I no longer have access to Ubuntu servers for testing.