SQL server security

We’re going to talk about SQL Server networking security and focus on two deadly mistakes that are made by people who configure SQL Server in their enterprises.

We’re going to talk about SQL Server networking security and focus on two deadly mistakes that are made by people who configure SQL Server in their enterprises. I’ve got with me Greg and Mike from CQURE Team.

Greg is a specialist in enterprise security and Mike is a specialist in SQL Server and SQL Server security.

GREG: My perspective of SQL Server is just a service within the operating system. It uses some networking to communicate with the external parties. And I will try to cover this communication because we can spoof the communication. We can misconfigure protection of this communication. I will play more at network layer and hacking the network layer of SQL Server security communication.

MIKE: I will show 2 hacks: how to get username and password and how to bypass even most sophisticated protection inside the database. When you are sniffing TDS packets.

First hack: accessing SQL Server to get user data


Let’s start with the first hack. And, in this particular hack, we will be accessing our SQL Server and we will try to get SQL Server login, username and password, which is sent during the authentication of the user wide screening connection to the SQL Server.

Stand Up Management studio and I will connect to verify that I can do this and that I have a correct user to my database server. This is SQL02 and connecting from the Windows 10 machine. Let’s connect. Currently, I’m connecting as an admin and I’m using Windows integrated authentication. I have an SQL database, in which I have some tables, like customers and employees.

sql server security

Let me go to the security setting and:

  • as you can see here, I have only “Charlie”.
  • I will create a new SQL login. It will be “Bob”.
  • SQL authentication with some password.
  • Okay. This time I created a Bob user and I can try to connect with this new login.
  • SQL authentication.
  • Let me switch to that Bob and try to connect.

It’s possible for me to connect to the Server. Remember that if we are working with two connections in a single SQL Server Management Studio, always verify on which connection you are currently working.

We have CQURE and I can expand it. You see that I’m currently signed-in here at the bottom as Bob to this CQURE database. I will disconnect.

To perform this hack I will use Kali Linux but it can be any system able to perform ARP spoofing and other attacks on the network.

I will be trying to listen the network traffic that is from the SQL server to the Windows 10 virtual machine and from Windows 10 to the SQL Server, so both ways.

Let me go and see what is the IP address of the virtual machine of Windows 10.

  • “ipconfig” and it is “” and let me go to ping SQL02
  • This is, so this is the second IP address

sql server security

  • “arp -a” will display information about the actual physical address of this computer that’s hosting the SQL Server.
  • And, of course, “ipconfig /all” is getting us the information about our physical address, so it’s this one.

To perform this network sniffing I will be using Kali Linux and here I have the console.

I will start performing ARP spoof, so it will be sending ARP responses to both machines saying that “okay, this is the client and the target machine”. This is the first one. Here I’m getting it in reverse format, so actually it’s the client and the second one is the server. So, I’m spoofing to the Server and to the Windows 10 client.

sql server security

If I go back to Windows 10 machine and now I display “arp -a”, you’ll see that Linux machine and SQL02 has the same MAC address. So, I’m actually changing the IP address and the MAC address for this IP address and as you can see here, before it was actually different MAC address. So, it’s successfully ARP spoofing.

sql server security

All the traffic that is going to the SQL02 will go actually to the Linux machine.

What can I do next?

I need to set up the “IP_forward” parameter. And the next part is to add an iptables pre-routing rule that will redirect the traffic going to the SQL02. It will be redirected to our local port 1433.

I’m going to make this rule and the next part is to use msfconsole. This is actually Metasploit. Of course, test can still connect to our SQL Server connect database engine as Bob. Try to connect. Now it’s not working because I’m ARP spoofing and redirecting actually with the IP tables the traffic to go directly to this KALI Linux machine.

Let’s go back to the KALI machine and type “workspace”.

  • I’m in default so “workspace -a” – let’s create a new workspace and this is just for demo.
  • I’m in the new workspace and now I can “search capture/mssql” for the module, which I need to use.
  • Let’s copy this one and paste it.
  • I’m using this auxiliary module for Metasploit and I need to “set SRVPORT 1433” to use  a default port.

sql server security

Now the last part is to just hit run. And it’s starting listening on local port 1433. Let’s try to do it.

  • Now I’ll try to connect here as Bob.
  • Failed.
  • Let’s go back to the Kali Linux and see what we have here.
  • We have Bob and plain text “Password” for this user!
  • I use the password to create a login on SQL server.

sql server security

  • When I want to exit now I can switch to my iptables rule and disable it.
  • I’m disabling this rule right now. So, it’s not running anymore.
  • Right now I can connect to SQL02 because I’m using IP forwarding
  • I can disconnect it.

How I achieve this that I can grab this password?

Of course, the password is not sent in the plain text and if I start Wireshark, I enabled the TDS traffic sniffing.

  • Let’s try to connect as Charlie. And you see that I have pre login messages and nothing is there that I can see the password in it. Actually, it’s not sent in the plain text.
  • Import
  • What is happening when I’m using Metasploit? The Metasploit is starting a process which is listening on port 1433 and is saying that its very very old version of the SQL Server actually its SQL Server 2000 and it’s forcing the client, even the newest client 2016 or 2017 as Management Studio, to fall back to old authentication mechanism protocol and this is when the password is all almost sent in plain text.

It’s easy to get your password if it’s possible to spoof and redirect the traffic and to force your connection to fall back to old version.

Performing another type of the attack


Let me stop Wireshark. And now I will try to perform another type of the attack. It’s very similar to SQL injection. But this time it won’t be working as SQL injection directly. Let me show you one thing here: In TDS packets there is a plain text for the actual query. This is one of the queries that was executed on the SQL Server when the user connected with the Management Studio.

sql server security

So, if it’s possible to get this query and to change the content of this query, it will be executed within the context of the login that is connected to the server. Let’s try to do it.

First, we need to capture… let’s say that I’ll be doing this as an admin, on CQURE database. I’ll be doing this query.

  • I’ll open it from the file.
  • I have it on the desktop.
  • This is a very simple query.
  • When I execute this query, it’s getting me the results at the bottom.
  • To do this attack I will run again the same query.
  • The filter for TDS on Wireshark.
  • “Continue without saving”
  • Now I’m waiting to capture some queries.

sql server security

If I execute again I should see the same query SQL batch here. Thi is SELECT. I will copy the value of this field, so: “select custid”, “companyname”, “contactname address, city FROM customers”. Quite a simple query.

And, what I will do next.

sql server security

I will use one of the scripts that are using Ettercap to capture the packets and then change the content of the TCP/IP packets and replace the content of it for (using regexp) to inject new query. The only thing to remember when you are trying to write this kind of script is that the TDS packets don’t have any checksum on the query itself but the TCP/IP packet has the checksum on the length of the data inside the packet.

So, the easiest way is to get a very long query – this one is not so long, but it’s enough to create a login named “hacker” with the password “password1”. And we just need to add just spaces at the end of the query to be the exactly same length as the query that we’re trying to replace.I will stop ARP spoof here on both ends. This script, which I have here, is also starting ARP spoofing by itself.

So, not to mess it up: this is the same query that I copied and if I hit enter, it’s creating a payload and it’s waiting for new queries. If the query’s different, for example, “select top 1”, it will not be replaced.

  • There is “SQL traffic” discovered, so we see that we are listening for it again. But until we have the same query executed again, nothing will happen.
  • “Found our string” and replace it.

sql server security

  • You see in the SQL Server Management Studio, we do not see anymore the results for this query.
  • If I hit F5 again to execute the same query, I will get: “The Server principal ‘hacker’ already exists”. So, probably someone will notice it. The TDS packets after the first replacement.
  • I will exit it.

Let me go to Wireshark again.

  • I’m searching for this select. And of course, it was found in the data here. This is the same select which was replaced with this one.
  • Next, if I go to the logins and refresh, you will see that there is a hacker.

How can we protect ourselves? First of all, it is necessary to connect with options “encrypt connection”. When I select this “encrypt connection”, the connection to the database will be protected.

sql server security

  • If I open the same file and execute it.
  • Of course, I need to turn on listening on Wireshark again. Let me switch there.
  • ARP spoofing.
  • Again, I should see in Wireshark the query that is executed. Let’s try to do this.
  • I’m refreshing.
  • This time I do not see any queries here. Why is that?
  • When I go to Wireshark to see that this time I’m starting the TLS exchange.
  • There is no information about any TDS packets here because they are encrypted.
  • I do not see the query that is sent there and the response.

sql server security

The last thing is that in this connection options when it’s encrypting, we should disable this “trust server certificate” checkbox.

To do this, we actually should go to the SQL Server Configuration and on the Server Network Configuration force the encryption and use the appropriate certificate from the list.

So, when we enforce this one and use the proper certificate, we can safely connect to our server. Remember to verify that at the bottom of the Management Studio you see the padlock showing that there is an encrypted connection to the server.

Making personal attacks


GREG: After you saw two attacks performed by Mike, it’s the time for my attacks.

The first one will be very similar to Mike’s one, because it’s about forcing SQL Server client to use clear text and then grabbing this clear text authentication out of the wire. But I will use a different cheat to make server client connect to the SQL Server.

Instead of playing on the ARP level and physical address resolution, I will work on the level of name resolution in Windows, which is significantly higher in terms of the TCP/IP stack, which also makes the attack easier to be performed.

  • Here I have my client. I will try to ping some server.
  • “Ping sql3” and I have no SQL3 in my environment, so no one replied.
  • If we start to think about the name resolution (and how Windows client knows what is the IP address of SQL3), we have to go to the network properties > “ncpa.cpl” > properties of the interface > TCP/IP > Advanced > WINS and here you can see NetBIOS, which is used for name resolution.
  • We try to monitor the situation with Wireshark. If I launch Wireshark I can see if I can try to ping someone non-existing
  • “ping sql4” this time. You can see that there is a broadcast being sent across the network with the question who is the SQL4 for a guy in this network.

sql server security

Of course, no one replied to this query.

My client returns information that the host cannot be found. And to spoof an identity of a different machine with work on this level with broadcasts, so I will create a process replying to such broadcasts saying I’m the one you are asking about.

Using other tools that help with the attack


I will use here the responder utility – “responder.exe”. Originally, it was a Python script but I’m using Windows version, which makes it even simpler to use by regular users. The responder is sending information about fake IP addresses. This is the one thing responder is doing. Another thing the responder is doing, which is really great, is acting like SQL Server, so you don’t have to use Metasploit or any other third party software. The responder itself emulates SQL Server in the way, which is enough for lowering down the authentication level and grabbing the input being sent by the client.

sql server security

  • I will run responder.exe. It has a couple of parameters you can easily verify those with “-h”.
  • I will use the only one which is about the IP address to be spoofed.
  • The IP address of this machine is so I will run responder “-i” and it simply starts.
  • Here you can see a bunch of servers being run by responder including SQL server.
  • Here I have the console.
  • I return to my machine now
  • I can ping “ipconfig /flushdns”.
  • I can try again with SQL4 and of course now responder sends his own IP address as the SQL4. It appears her. In the responder console you can see that the sql4 was sent to this poor victim as a poisoned answer. Having exactly the same situation and having running SQL Server emulator I can, for example, run all the ODBC configurations. Actually, any sequel Server client will work here.
  • I can add a new connection and the name will be “SQL1”.
  • I will connect to my SQL server machine. It doesn’t matter because responder responds any query, including this one.
  • If I click next, I can say I would like to authenticate against SQL Server with “sa” account and the password.
  • Next, and I can click okay.
  • If I switch back, I can see the message that the “previously captured hash for sa” was in the database because I tried it previously.
  • So, I can try with another password to make it visible.
  • Now it is connecting again.
  • I will try to see this and now… well… this is not what I intended, so I will break it and I will remove responder database. I will go to “logs,” I will remove everything.
  • I will run the responder again. Hoping this time it will grab the information from the SQL Server client. If I click next right now, I have a connection again.
  • I will return to my responder and now you can see the password was really captured (Secret123111), because SQL client tried to connect to my SQL Server with “sa” and this password.

sql server security

This is another thing why I used the sa password, which is definitely not the best practice. But it is so common I can emulate it this way just to pay attention this is something you should really effort in your protection environment.

Second attack: performed with the SQL Server


It was the first attack. Very similar to Mike’s one. At the same time, a bit easier to be performed in practice.

Another attack, which can be performed with SQL Server or through SQL server is not the attack on the SQL Server, but this is an attack with the SQL Server. It relies on the fact that within the SQL Server (I have it here) we can configure multiple instances, so here you can see that I have installed two different SQL Servers running on the same machine – the SQL Server with a default or unnamed instance and with the named instance.

The named instance is running.

  • We go for the configuration
  • Here you can see the configuration of SQL Server services.
  • If I open “SQL Server (Instance 1)” under properties you can see that this particular Server, if you go for the network configuration > instance 1 and then TCP/IP, you can see > under IP addresses that there is no TCP port. It is how named instances are working in practice. Right now, this is the dynamic part.
  • But if I restart the server… if something happens… if I restart the Server, the TCP port may be totally different.
  • It is a kind of a nightmare for firewall management. But this is how SQL Server is working.

sql server security

  • You can fix the port number and make it a constant one here. But by default, it’s very dynamic. So it is kind of issue for the SQL Server client how to connect to the proper TCP/IP port because at the very end the client must connect to the proper port.
  • And, on the SQL server machine, we have one very special service which is SQL Server Browser. SQL Server Browser is a kind of the librarian knowing about every single instance and then sending the information to the client. It’s kind of a directory.

So, if I run a SQL Server browser service, I will start it and I will see that the process ID is 4916. I can do “netstat -ano | findstr /i 4916,” you can see my 4916 process is listening on 1434. This is the default port. This is UDP port, on which browser listens to clients.

sql server security

And it simply says if you want to connect to the instance named “instance 1,” the TCP port would be this and that. And after every single restart, he will have the right information in his database so it will send the client to the proper one.

At the same time please do remember it’s UDP based. So, if it’s UDP based, it may be attacked in multiple ways. Actually somewhat comparable to the way we attacked NetBIOS and the name resolution. Because UDP is relatively easy to send a packet saying it’s coming from a totally different source.

The purposes of using UDP


We can use it for two purposes – such possibility of having such attack. The first one is to send the packets to the poor victim acting as a victim, so let’s imagine I’m doing ping saying I am the host A, which is not true. But the reply of the ping will return to the host A because this is the source. We can do a very similar thing with the UDB packets.

And here with this particular service, another thing we can use is amplification.

Amplification means I can send a relatively small amount of data to this service and he will reply to me with something significantly bigger. And this is the thing of what I can do against this particular port.

I will show you github repository about mssqldos.py – it is a Python script. I will not run it right now, but this is the script sending packets to the SQL Server browser asking about some information and spoofing the identity of the asking party. So, the SQL Server browser will send back the answer. The answer will be significantly bigger and at the same time, it will be sent to the victim, to the one host identity which we have just spoofed. If we perform it in a proper scale, we can actually kill the performance or kill the bandwidth for this victim.

The conclusion is: please do not expose SQL Server browser to untrusted that hosts especially if it’s about a large number of them. We can find some SQL Server browser exposed to the internet, which is not the smartest thing I definitely can imagine. Those may be used for attacking other victims.

Please care about your 1434 and about you SQL Server browser.

You can remove SQL Server browser totally – configuring your SQL Server clients with the proper port number. If you do not use named instances, you also do not have to use SQL Server browser. So SQL Server browser is very handy and very comfortable thing, but at the same time if you are security oriented, you can skip it. You can skip using it because it makes your environment a bit unsafe. But the management is significantly more complicated in such environment. It’s up to you.

As you saw, we had a couple attacks on the SQL Server, the one that Mike presented to you and those I tried to do on my own and misusing SQL Server protocol to attack some innocent victims.

All those ways can be limited and managed. And in a properly configured environment, no single attack like we presented should be in practice.

Fingers crossed for your SQL Server and please: do not be scared with the SQL servers because it’s a server like any other, maybe a bit more complicated, but at the same time, in terms of communication, it’s nothing really more complex.