Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to recover a SQL Server login password.

By Geoff Albin,

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;

  •             Core i-5
  •             4 GB Ram
  •             ATI Radeon HD 7970
  •             Ubuntu Server

My second demo system is;

  •             Core i-5
  •             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.netThere 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
select name, 
       LOGINPROPERTY(sys.syslogins.name,'PasswordHash') as password_hash 
 from sys.syslogins
 where LOGINPROPERTY(sys.syslogins.name,'PasswordHash')  is not NULL

or

-- 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
 from sys.sql_logins

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=4 --pw-max=12 -m 131 -p : -o "C:\Users\galbin\Desktop\hashcat-0.42/SQL_passwords.txt" --output-format=0 -n 2 "C:\Users\galbin\Desktop\hashcat-0.42/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=4 --pw-max=12 – at least 4 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. All else is the same.
  • -p : -o "C:\Users\galbin\Desktop\hashcat-0.42/SQL_passwords.txt"  - Our output file name and location.
  • --output-format=0  - The format of the output file.
  • -n 2 – The number of thread counts to use.
  • "C:\Users\galbin\Desktop\hashcat-0.42/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:

0x0100e8ad828763d96e4fbf45d2db223940dcca99b2ebfd03dec0:H@rd1

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" --outfile-format=3 --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.

Conclusion

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.

Total article views: 18416 | Views in the last 30 days: 101
 
Related Articles
SCRIPT

Login Password Policy

This script lists all of the SQL logins with their password policy

FORUM

sql server 2005, password character set

sql server 2005, password character set

FORUM

Reset Password for linked server login

Reset Password for linked server login

SCRIPT

Find weak login passwords in your server

Use this script to search for SQL Server logins who use weak password

FORUM

Moving Sql server 2000 user login and passwords to Sql server 2005

Moving user logins and passwords

Tags
password cracking    
passwords    
security    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones