In my
previous post, I covered the steps necessary to enable and enforce encryption for all connections to Microsoft SQL Server. Now, this was nothing more than a step-by-step of what to do. It covered nothing about why or how important this is. Hopefully, I can impress upon the reader how important it is to encrypt connections. Considering the effort involved is extremely minimal - less than an hour of work - this should be the first step after installing SQL Server.
How Vulnerable is your Data?
Just to prove how easy it is to get access to the underlying data, I installed
Wireshark on my dev machine (actually, I already had it installed - it's a great tool!), started it up and entered the filter "tds" (all lower case). This shows me all the SQL statements going out of my machine and all the responses that come back. While the responses are still
TDS encoded, it isn't difficult to parse out the data - especially strings - just by looking at it. This can give me access to a boatload of information.
The only thing SQL Server
really tries to protect is SQL authentication. Starting with SQL Server 2005, the authentication process attempts an
SSL handshake before authentication occurs. For the purposes of this post, I am assuming the client supports SSL authentication and the handshake is successful. Please note that this is very different than
Extended Protection for Authentication, which is an additional layer on top of SSL encryption. Also, the encryption described here is limited to authentication. After authentication succeeds, the SSL channel is shut down and the connection reverts to an unencrypted state.
Anyway, when connecting to a SQL Server that isn't configured to use SSL, the server will search for an appropriate server authentication certificate (with accessible private key and matching the NetBIOS name or FQN ) in the Windows server certificate store and use that. If one is not found, a self-signed certificate is generated on-the-fly and is used specifically to protect the authentication portion of the connection. Any subsequent SQL statements or RPC calls are left unencrypted - and so are the results that are sent back.
Note: SQL Server actually checks for certificates at startup. If a certificate is added or becomes accessible after startup, SQL Server will not use it. Related, if a certificate is removed or becomes inaccessible after startup, SQL Server gracefully falls back to using a self-signed certificate where necessary.
Configuring SQL Server to Require Encryption
If you haven't read it yet, please, please, please, read my
previous post. Getting SQL Server configured to require encryption is extremely simple. You can even use a self-signed certificate if need be. As long as connections to your SQL Server are unencrypted your data is vulnerable to anyone with a packet sniffer and access to your network.
Configuring a Data Source to Require Encryption
Configuring a Data Source to require encryption on the client side is also easy. Both the
SQL Server driver and the
SQL Server Native Client driver in the
ODBC Data Source Administrator provides a checkbox for enabling encryption. On the fourth page of the wizard, simply check the
"Use string encryption for data" setting and save the Data Source. All connections using that Data Source will now require encryption and perform client side validation of the server's certificate.
Note: This only tells the client to require encryption. It does not enable encryption, something which must be done on the server.
SQL Server Native Client driver wizard
What Validation Checks Occur on the Client Side?
Since validation occurs (I'm assuming) via
WinVerifyTrust, all the bells and whistles that come with certificate validation in Windows occur. Although, there are a couple of small differences in how the
SQL Server driver and the
SQL Server Native Client driver handle client side validation. The
SQL Server Native Client driver includes more detailed descriptions of any SSL errors, while the
SQL Server driver simply returns a generic SSL failure error. In addition, the
SQL Server Native Client driver includes a principal name check, which compares the server name in the Data Source (excluding the SQL Server instance name) against the common name in the server's certificate.
For example, if the SQL Server is using a certificate with a FQN but a Data Source only specifies the NetBIOS name, the validation will fail. So, sqlserver\myinstance will fail if the server's certificate has a common name of sqlserver.domain.com, but sqlserver.domain.com\myinstance will succeed. The same goes for specifying the IP address instead of a server name.
Digging a little deeper into the validation check and how WinVerifyTrust works, the certificate must be signed by a trusted root certificate or have intermediary certificates that are signed by a trusted root certificate. The certificate can be purchased from any Certificate Authority, can be created from an internal
Windows Server Certificate Authority, can be created from an internal
OpenSSL Certificate Authority (as long as the certificate authority roots are distributed to all Windows machines), or even - and this is a little wacky - a self-signed certificate (as long as the certificate is distributed as a trusted certificate - not necessarily trusted root certificate - to all Windows machines).
Yes. Even a self-signed certificate will work. As long as the client machines trust the certificate, client validation will pass. If you don't enable encryption on the client side, the certificate doesn't even have to be trusted on the client machines.
Given that any SQL Server should only be internally accessible and client side validation is not required, there is not really even a reason to purchase an SSL certificate. Any certificate will do, as long as it fits the requirements for SQL Server.
Notes
Use Cases
For the purposes of this discussion, I have included a list below of all the applicable scenarios relating to client and server encryption settings. Hopefully, this gives an idea of the underlying functionality and answer some common questions.
- Client does not require encryption and server is configured to use a specific certificate but not require SSL. Both the SQL Server driver and the SQL Server Native Client driver will work without errors. The connection will be unencrypted, although the authentication process will use the server's certificate for encrypting the authentication handshake. Certificate validation is completely skipped on the client side.
- Client does not require encryption and server is configured to require SSL. Both the SQL Server driver and the SQL Server Native Client driver will work without errors. The connection - authentication and otherwise - is encrypted using the server's certificate. Certificate validation is completely skipped on the client side.
- Client requires encryption, but server doesn't have access to a certificate & key. Both the SQL Server driver and the SQL Server Native Client driver will generate SSL errors and abort the connection. Since the client is demanding an SSL connection and the server only has a self-signed certificate it generated on-the-fly, the certificate validation fails.
- Client requires encryption, but server is not configured to use SSL - although it has access to a certificate and key. Both the SQL Server driver and the SQL Server Native Client driver will work without errors (if the certificate passes validation checks on the client side). Since the client is demanding an SSL connection, the server does its best to find and use one. If more than one server authentication certificate and private key are accessible, you have no guarantee which certificate it will use.
- Client requires encryption and server is configured to use SSL but not require it. Both the SQL Server driver and the SQL Server Native Client driver will work without errors (if the certificate passes validation checks on the client side). Since the client is demanding an SSL connection, all interaction is encrypted using the server's certificate.
- Client requires encryption and server is configured to require SSL. Both the SQL Server driver and the SQL Server Native Client driver will work without errors (if the certificate passes validation checks on the client side). Since the client is demanding an SSL connection, all interaction is encrypted using the server's certificate.
Client Side Errors
During the course of my testing, there are three SSL errors that can occur on the client side. Well, at least, three errors that I found when using the [Test Data Source...] button in the ODBC Data Source Administrator window. There are probably many more. One from the SQL Server driver and two from the SQL Server Native Client driver. I have included screenshots below of each.
Generic SSL error from the SQL Server driver
Certificate Chain SSL error from the SQL Server Native Client driver
Principal Name SSL error from the SQL Server Native Client driver
