In this post we will take a look at how easy it is to see the queries and results that get sent between SQL Server and the applications using it. Simply put, why you should be encrypting your database connections if you are serious about SQL Server Security.
Why Should I bother?
By default much of the communication between SQL Server and many of the applications that connect to it are sent in clear text. Now while you might think that your systems are secure, behind firewalls, or isolated from the internet, in reality, all it can take is one rogue admin on the inside, or a determined individual who has gained access to the business, to compromise a system. The prime example of this is Edward Snowden and the NSA. As such, when we design systems we should take a defence-in-depth approach, of which encrypted connections are one of these layers of protection.
How easy is it to see what is going on
In order to demonstrate just how easy it can be to get hold of the information inside the TDS packets I will be using Network Monitor from Microsoft, this will capture the network packets sent and allow me to see the details of what is being sent. Other tools such as Wireshark will also provide a level of insight into what is being sent between the application and SQL Server. I have configured three Windows Server 2012 R2 systems, one with the client (SQLCMD), one with SQL Server, and finally one which will act as a router between the two subnets that each server is on. This configuration can be seen below;
While this example is simplified, it should be noted that most enterprise grade network equipment and virtualization platforms allow for port mirroring. This is where the traffic on a network port is copied and sent out via another port, normally for monitoring purposes. In Hyper-V it is as simple as setting a dropdown in the Advanced Settings on the Network Port for the VMs.
What can we see
Once Network Monitor is up and collecting on the adapters that are going to be used for sending and receiving the network packets. It is simply a case of issuing a T-SQL statement to the server that I want information from, in this case I will simply be querying for the names of the databases on the server.
So, once this statement has been issued and we have the results, it is time to look at what we captured in Network Monitor. First of all it is important to understand that we are going to look for a pair of packets, the query we sent and the results that we got. As we can see below, finding the packet can be achieved quite quickly as we know a lot about them already. Namely that it will be using the TDS protocol, and we know where they are originating and their destinations. Once we have the outbound TDS SQL Batch, we can look into the payload of this packet, and it becomes very clear what we just asked for.
Now we just need to locate the Response from the server, again this is easy to do given what we know. And here we can see that the payload contains quite a bit of information about the results that we want, additionally we get a bunch of useful metadata about data types.
The net result of this is that I can glean important information about the system, directly from the results but also metadata that tells me about the schema. From the initial query I will get objects and relationships, then from the response I can get information around the data types that are used along with the actual data that is being returned to the query. In this case database names, however it could be sensitive data such as financial, personal or technical specifications for a new product. If it is in the database and being queried then it is all fair game.
Real World Example
One scenario where encrypting connections between SQL Server and applications is for monitoring tools. Typically a monitoring system will pull large amounts of metadata about a system including server configuration, databases, objects, etc. By having this obscured, it can make life a lot more difficult for anyone trying to get details of an environment. So selecting a monitoring tool that will work in this scenario is very important. If you are using SQL Sentry Performance Advisor or Event Manager you can be confident of enabling encrypted connections without impact to the functionality of the monitoring system.
One thing to consider before setting up TLS for encrypting your SQL Server connections you should read the “SQL Server support for TLS 1.2 – Read This First!” blog post by Aaron Bertrand (Twitter | Blog). It covers important information about which builds support TLS and details on some potential gotchas that you can encounter.
What Are My Options?
There are two main options that will work with SQL Server 2005 and above, these are IPsec and TLS/SSL. IPsec is a security protocol that operates at Layer 3 of the OSI model, providing end to end security for the transmission of data between two systems. Be it Client-Server, Server-Server or Client-Client.
The other option open to us is to use SSL/TLS between the client application and SQL Server, again providing end to end security for the connection. However, unlike IPsec which operates at a low level in the OSI model, SSL/TLS are implemented in Layer 5.
Both of these will provide a level of protection against attackers between the client and the server, and it is perfectly possible to combine them to make life difficult for those attempting to get at the data in the system. When selecting which option to take it is important to understand how much effort is involved, for IPsec you would typically be looking to have this setup at the infrastructure level and need to involve the network admins to get this working. In the case of securing the SQL Server connections this requires less work and can commonly be implemented by the DBA team, once you have the appropriate certificates. Details on the process for configuring SQL Server to support encrypted connections can be found here in Books Online.
Once this has been configured, as we can see below, it is not possible to identify the SQL Batch or the Response payloads. We simply see the headers and the encrypted payload.
How Does This Impact Security?
While we can clearly see that we can protect our data and database schema by encrypting our SQL Server connections, it should also be noted that we should think about protecting some of our admin related queries. If you still have applications that need to make use of SQL Logins, you will be pleased to know that SQL Server does not send the username and password in clear text during the authentication process. However, if you are creating logins, then this data will be sent in clear text if the connection is not encrypted. So, if I need to add a SQL Login, I give it a name and then pick a really secure password that no one can guess.
When we send this command to the SQL Server to create the login, if the connection is not encrypted, then like with the previous statement we will see the payload.
As a result our system security is not quite as strong as we would like it to be, and that secure password that we created for our SQL Login is not so secure. Anyone who captured the packet with the T-SQL statement creating the login will now have both the login name, and the password. Additionally they will know which server it is on and can now go and log in with ease.
Hopefully by now you will have been convinced that there is a very real need for using encrypted connections, in order to improve the security of your SQL Server systems. This is not a silver bullet that will solve all of your security woes, it should be considered another layer to a larger security approach. As with anything, it is all about defence-in-depth, from making sure the doors are locked, all the way through to making sure only the appropriate users have access. While not the simplest feature to enable, it is not the most complex, either. With planning and testing this can be made to work very effectively to help aid in the protection of the data that we manage in our jobs today.
