SQL Server Logins

This episode is going to be about extracting hashes from SQL server logins from the database and also cracking them. It’s going to be lead by Mike, a fantastic SQL Server and SQL server security specialist. He’s going to deliver very nice demos!

Mike:

Today I’ll show you how to get those hashes from the SQL server and how you can check if your users are using long passwords or if those passwords are easy to crack by using dictionary attacks. Of course, you can do it inside the SQL server and use other tools. It’s very important that users are aware that using simple passwords will allow you to bypass your security and to get to your data, which is very valuable.

SQL server is a popular and very important service

Paula:

This subject is very relevant, especially because when we are used to cracking password hashes in let’s say, Windows environment, we all know how to do that. The SQL server, it’s not a very popular subject, but there’s a little problem almost in every infrastructure out there. We just don’t change passwords of SQL server logins. We just set them up once and they are always like this. What if, for example, one of the persons is fired from the company? The password probably isn’t changed, like for the SA account.

Mike:

Exactly. The same is with the connection strings that software developers are using one password and then it’s stored and copied to all other machines. This the same password and no one is changing those passwords. The worst thing is when you hard code something in your software. Then you cannot change is easy.

Paula:

So guys, if that sounds interesting, you want to know how to extract and crack SQL server logins, passwords, stay with us and watch the full episode.

 

Okay, let’s do it.

First I’ll start by:

• Creating a login
• On the level of the server, let’s go to the security
• Inside the logins, we can create a new login.
• I will create a login and test with SQL server authentication.
• I will provide the password. It will be a very simple password: pass.
• I will disable Enforce Password Policy.

When we are using Windows operating system servers, and we are enforcing password policy for the users in Windows operating system, we can also enforce the same policies on SQL server authentication logins inside our SQL server.

In this case, I am disabling this so it will not enforce full complexity for my passwords, which I’ll show you how easy it is to get and recover the passwords for the users.

• I’m creating a simple login –  this login test.
• Click okay.
• I will create another login, which will be called test2 with the same settings and the same password.
• Also, disable Enforce Password Policy.

Where the information about the logins is stored?

This is on the level of the server, so probably we’ll find something in the database master. Let’s go to the master database. In the views, let’s use a filter to search for the name containing something like login. Let’s search it, system views and we are seeing here, it says Logins.

So this is in the system views.

I have the same settings for the filters here, log in, and this is why I see only a few system views. Let’s query this one, Select Top 1000 Rows. We see here all the columns that there are in this view.

Let’s see at the end of this table to find our new logins. Let’s find out what is the password. The password is rather hard to read from this perspective, but what can we see when we try to extract those using one of the functions in the transactor scale?

Let me switch to my tab. We can extract login properties. This is one of the functions that we have in the SQL Server. I will be extracting password hashes for two different users:

1. test
2. test2

Remember that that two test user has the same password, which is actually “pass.” So let’s extract it.

I’m using union, and this is not union all, so if these two values extracted from different users are the same, then I will get only one record here, not two.

I already see that those two hashes are different, but I can copy it, paste it. As you can see, the beginning is the same and the rest is different. As we can assume, those two users have different hashes because we are using salt.

The hash algorithm

In SQL Server 2012, 2014, and 2016, we have a new algorithm for creating hashes, which is using SHA-512 to create the hash. How the hash is created for the user? Let’s see. We are starting with a simple password. This is the password that we’ll be using. Then we are selecting, or actually assigning to the salt variable a new random string. Actually, it’s a varbinary, so it’s not a string, it’s a random number generator. We are using CRYPT API.

Next, we’ll be calculating the hash. Let’s see how the salt is created. I have the code right here, so I will be declaring the variable and assigning four bytes for the varbinary for the salt and displaying it at the lower.

Execute.

Every time we are executing this code, a different value is generated for us and stored in this variable. Every time I’m changing the password or I’m generating the password for the new login, the different salt is generated for me.

Let’s see how it’s created

This is the beginning of the hash and it’s the prefix which is showing which version of the hash actually is used for us for storing the password. Then we are using the salt, so the first two bytes is just a prefix, then it’s four bytes of salt, and then we are using SHA2-512 to generate the hash of the password. The password is concatenated and this is used to generate the hash that is appended after the salt in the hashes.

Let’s run it

As you can see, the result is as-is. I’m displaying the password. Let me copy this with the headers. We are starting with the password. Then I’m displaying the salt, so this is the string that’s generated. Then there is a hash. The hash is starting as the same with the prefix, the same part as is displaying the salt for this hash, and then the rest of the hash. So I will comment on this one.

Is it a true password?

The last thing is that we are checking if the password is for the corresponding hash. In other words, if it’s the true password. We have a function, which is called PWDCOMPARE. We need to pass the string, the varchar actually, like a password and the varbinary as the hash. The result is 0 or 1, depending on if it’s the correct password for the correct hash.

Let’s test this one and verify if the hash is correct for one of the passwords that we have. This user is cqure. I’m assuming this is the password, pass, but I’m not sure. Let’s test this.

I’m executing this one, getting the password, hash, and putting it here. So I have two variables, probably pass for this user, cqure, and the hash, which I extracted using login property from the system view.

Let’s run it, execute, and the result is that this is the correct password for this user.

If I modify the password or the hash, the result will be 0, so it’s plain that is not the correct password.

How can we check if our users are using difficult passwords for their SQL server logins?

First, we need to extract the hashes from the system view. How to do it? I will prepare a new table, which will contain only the login name and the password. I’m using this in the secure database so I’m simply getting those hashes from the master database and from the syslogins.

Let me drop this table and I’m creating a new table which will call system_logins2 and I’m getting only the login name and actual password hash from the view. I’m omitting the logins that don’t have passwords and building logins of the SQL server.

Let’s run it.

If I just select everything from this new table, I will get the hashes. I can copy the hashes to the file that I will name hashes2. So I replaced it, saved the file, and now I will rename this file also from previous attempts.

Hashcat – a tool for recovering password based on the hashes

I will switch to the cmd and here I have the hashcat. Hashcat is a very popular tool for recovering passwords based on the hashes. We can use different techniques. I’ll start with hashcat64 with the –help, to display the help. I will scroll up. I have multiple switches here that can control the behavior of the hashcat. The most important is here, the hash-type, so what is the hash in the input file. In this case, we need to find the SQL server 2012 or 2016 hash version. The second part is the attack-mode. Let’s scroll a bit lower. This is the least of the hash modes, so different hashes that the hashcat can try to recover using, for example, brute-force attack. Somewhere here, it’s the SQL Server version 2012, 2014. Actually, 2016 is using the same algorithm for creating password hashes so we can use this mode. We’ll be using the brute-force attack, mode number 3 here, this is attack mode.

Of course, we can use other approaches like a hybrid wordlist or hybrid mask wordlist, so you can use different approaches for cracking the passwords extracted from the SQL server.

Let me put the comment

We’re starting with the cracking of the hashes. After a few seconds, I see that it’s already recovered the three passwords out of six. Now it’s trying to find the four corrected land passwords. Every time I’m hitting Enter, it’s displaying the new progress. Now it’s five correctors here and it’s on the 50% of the cracking of five-character length passwords using the brute-force. Of course, it will take some time to crack all the passwords I have here, so let’s quit it. Now let’s go to the folder and see in the recovered passwords. Oh, I had two the same hashes in there, so basically, I see two same passwords here.

Next, let’s go back to my SQL server. You’ve seen the approach of using hashcat. Of course, you can use different switches to crack the passwords using the hashcat. For example, use GPU for this purpose or use the dictionary attack, but I will show you also how you can implement your own dictionary check on all the hashes.

You’ve seen already what is containing my table, the system logins. Now I’m using simple select, which is extracting all the passwords from this table, system_logins2. It’s joining it with the table, which contains only one column.

Actually, it’s pass.

This very small table, because pass is very small. It is used only for the example, but I have also different tables that are very similar. I have two more, actually here. One is almost this pass_medium. It contains almost three and a half million of different passwords. I have pass_small and there are one million passwords there.

I can use the different select statements to compare if actually, one of the passwords used by the users for signing in to the SQL server is one of those on the list.

Using PWDCOMPARE

How can I do it?

I’m comparing, using PWDCOMPARE, the pass from this table, all of the passwords, which the hash they extracted for logins from the system view. This is very fast, on this very, very small table, because it’s like 5 or 10 passwords in this very small table, but it’s showing the results.

Here you can see, I have cqure, test, test2, and this contains only the pass. I used hashcat with the brute force for cracking this password, but also, if I’m using some standard password, I can find it for the website with our admin and login that I have.

If there is no match, then I get null.

 

14 minutes to run one million passwords

If I run it on a big table, actually, for one million passwords, it took approximately 14 minutes to run and test all the passwords. So never run it on the production environment. Copy those hashes to your own test environment and then run it and test if users are using very simple passwords. Remember that if you add another login, it will increase the number of checks that it must perform.

As you see, it’s quite easy. One more thing, it’s interesting that we don’t have, by default, the lockout policy. After failed attempts, the account is not locked by default. You can do some other protection there, but by default – you can’t.

Paula:

So you can do it as many times as you want to, yeah?

Mike:

Yeah.

Paula:

That’s pretty good. Guys, that’s about it in this episode. If you’ve got some questions about what Mike just did and about our episode, don’t forget to post your comments in the blog post and on our Facebook page.

And if you want to learn more interesting techniques for securing SQL Server, check out this intensive training from CybeBytes series.