Paula’s part:

First of all, I will show you the environment. This is the client simulating the application. What I have over here is a regular query, I’m using the CQURE database and I’m doing: “SELECT custid, company name, contact name” etc.

When I’m executing this query, you can see that what I’m getting, actually is the regular table. There is actually some kind of information that I’m able to grab here, so I’m going to refresh that.

Parameter sniffing in SQL Server

So very regular query that the applications do. Now, I’m going to refresh the logins, where you are able to see that we have regular logins here, nothing really suspicious. My job would be to sniff and spoof the query that is done by the application to the server. You are able to see that it actually runs in a clear text and we are able to see it’s content. That’s going to be a part of our demonstration right now.

Right now I’m going to switch to another machine, which is a SQL server itself. What we’re going to do, we will open up Wireshark over here, just let me do that very quickly. Well, we could just click on the icon, and that is also an option. After that, we’re going to sniff the traffic, sniff these particular queries using the TDS traffic. What I did over here, I put the filter TDS and that means that we’re going to be filtering the TDS related traffic. So now we have to just start the capturing and wait for the query to be done from the application to the server. Of course, it’s not an application, it’s just a regular management studio.

But what I want to show you is that, that particular traffic, these queries, they by default run in a clear text.

So that’s a bit of a problem and I can tell you that from our experience as well, the vast majority of SQL servers within the organizations are misconfigured like this. So, these queries actually run in a clear text, and as you see we have “SELECT custid, company name” etc. So, that’s something that we’re able to sniff and eventually spoof in our demonstration.

Replacing queries with Kali Linux

Next, I’m gonna move to the attack area where I’m going to configure and run different types of scripts and replace this particular query, so “SELECT custid” etc. by using Kali Linux.

What you can see over here is the SQLInject script where I’m going to specify your “SELECT custid, companyname…,” so that’s the query that you already know. I’m going to replace that by using “-i” parameter with “CREATE LOGIN hacker WITH PASSWORD” and then I’m specifying the password here:

Of course, it depends on who is performing right now the whole analysis, but eventually, what is important, is that we are able to see that in a clear text. So, I started the whole attack right now and as you see, I’m doing to execute the query so that we have something to replace. This time we can see that commands completed successfully, it’s not really the table that we are getting from that.

Here you’re able to see that there is a “SQL traffic discovered” and we found a string and we have replaced that. Eventually, we have managed to execute our code, our query on the SQL server and then when we’re going to refresh the logins – let’s do that. Refresh, then you can see that I’ve got a cocker login in this box, which has been actually created just by replacing the regular query that was running in the clear text.

This is pretty much what you can do with the vast majority of SQL servers with an environment. At the very end now it’s time to learn how you’re able to make that particular queries and, in general, the traffic that is in between SQL server and applications not to be spoofable.

Would you deal with that? Test yourself. 

Mike’s part:

I have two servers. One is the SQL server SQL01, and on this one, I have Wireshark running. Let’s verify that it’s capturing the TDS packets right now. I’m going to Admin PC, and this is SELECT ‘Hello, CQURE Hacks Weekly.’ I’m executing this couple of times, actually three. Let’s see. Go to the SQL Server 1. Now you can see that it is actually executed, and those are those packets with the data in it. Let’s clear it again. Continue without saving.

Encrypting SQL Server

Now, I will go back to the Admin PC, disconnect and create another connection here. But this time, I will select Encrypt Connection in the options, but without unchecking ‘Trust Server Certificate.’ Actually, I will encrypt the connection, but no matter what is on the other side, I’m trusting the Server Certificate. Even if it’s self-sign, and I’m executing it again a couple of times with a query, going back to the SQL. And now, you can see only the previous disconnection. No other connections to the TDS packets. Let’s go and restart it. Go back.

Basically, this is when you have a padlock here at the bottom of the SQL server. Now it is protected, no one can see it. But the Man-in-the-middle attack is still possible because I can just create a self-sign certificate, and my SQL server management here will trust this certificate.

How about changing this connection?

Connection > Connect > Options > Do Not Trust Server Certificate. I need to have a proper certificate. Connecting to SQL01. Connect. Waiting for the error. It’s still trying. “The certificate chain was issued by an authority that is not trusted.”

No matter if I type the fully qualified domain name cqure.lab or not, it is trying to verify the certificate chain. It is self-sign, so it’s impossible.

Using trusted certificate in SQL Server

Now let’s go to the SQL01 and let’s prepare for the encryption with using the certificate that is trusted. I can import or request a new certificate for the computer. I’m doing this from my local PKI. I will see the template that was published on my Certificate of Authority. Still waiting for it. Computer > Enroll. In a couple of seconds, I should see the brand new certificate that was issued for my SQL01 server. Finished.

This is an SQL01.cqure.lab. I cannot use in the subject of the name, as you will see, short names right now because Subject Alternative Name only has the fully qualified domain name. It’s not enough to have the certificate. I need to configure it in SQL Server Configuration Manager. I’m going to the SQL Server Network Configuration. Right-click on the protocols for the proper instance. You can enforce encryption on the server site. But remember, if it’s only enforced on one of the sites, it’s still possible to perform the Man-in-the-middle attack.

You should always enforce it here and also in the connection stream from the client. On the certificate tab, now we can choose the SQL01. This is the brand new certificate that we just created. The server needs to be restarted. But before restarting it, remember one thing – the SQL server is running under the virtual account NT Service and this is the name of the virtual account.

So I am copying it, and I need to grant privileges on this certificate to get to the private key. ‘Manage Private Keys’ > Add > Locations > SQL01 (because this is a local account) > Pasting the name > OK. We do not need to have full control over it, just read.

Now I can restart the server. Restart > Going to the Admin PC. Again, let’s check the connection options. Encrypt Connection. Trust Server Certificate when using the short name SQL01. Connect. “The target principal name is incorrect.” So I cannot do it. Let’s try with the longer name, SQL01.cqure.lab. Connect. And now it’s encrypted. You see the padlock. It will not be visible even if I hit ‘F5’ couple of times.

So let’s go there. On SQL Server, checking the Wireshark. Nothing in here. Even if I go and modify the settings to the default ones or disconnect. The default one for connecting is Trust Server Certificate and Do Not Encrypt. Login > Connect. There is no padlock here. (typing) “Can you see me”. Executing this a couple of times. And going back to the SQL01. Nothing is here. No TDS packets in the Wireshark, because this was enforced by the server configuration, to encrypt everything. But remember, the best settings is to encrypt it on the client side, and on the server side.

I hope this will help you in protecting your connections to the SQL Server. If you want to gain a few more skills – join our intensive training from CyberBytes series.