Following one of my earlier blog posts about Always Encrypted, I thought I'd stick with the security theme for my latest one.  With the General Data Protection Regulation (GDPR) on the horizon, encrypting connections is one of the most common discussion points that comes up during customer workshops.  If you read the terms of the regulation, there is a need to account for the risks presented by "accidental or unlawful destruction, loss, alteration, unauthorised disclosure of, or access to personal data transmitted, stored or otherwise processed" (GDPR Article 32(2), "Security of Processing").  With that, let's see how we can secure encryptions to SQL Server.

Background

If you weren't aware, we've been able to secure communication channels since SQL Server 2000 and as the cryptographic protocols have become more and more secure over time, it's good to see that Microsoft has continued to ensure that the product hasn't been left behind. 

In January 2016, Microsoft announced support for TLS 1.2 encryption for SQL Server 2008, 2008 R2, 2012 and 2014.  That announcement has since been updated to include support for SQL Server 2016 and 2017.  Previously, Microsoft only supported SSL encryption in SQL Server, however given the spate of reported vulnerabilities against SSL, Microsoft now recommends that you move to TLS 1.2.  If you're wondering what the differences are; SSL and TLS are basically the same thing, the latter is simply a newer, more secure version of the former. 

Fundamentally, TLS provides you with the ability to encrypt connections between SQL Server and calling client applications.  When a client requests an encrypted connection to a SQL Server configured for TLS, an initial handshake takes place to negotiate the cipher suite from which further communication should take place.  Once agreed, SQL Server then sends its TLS certificate to the client, which the client must then validate and trust against its copy of the Certification Authority (CA) certificate.  Finally, providing the TLS certificate is trusted and it meets certain other requirements, a secure connection is established.  

The CA certificate represents the entity from which our TLS certificate was issued, which can be a Root CA or Intermediate CA, the latter being a replicated copy of the former for increased flexibility and scalability.  

It's worth mentioning that enabling encrypted connections can have a performance impact.  This is due to the extra network round trip when establishing a secure connection, as well as the encryption and decryption overhead which is handled by the Net-Libraries on the client and server.  In my experience, the overhead depends on how chatty your application is and the amount of data being transferred, so a period of testing is always recommended to compare throughput times.

Certificate enrolment

To get started, you'll need a certificate which can be purchased from a third-party or requested from an internal CA.  Self-signed certificates are great when testing but aren't recommended for production workloads due to their susceptibility to "man in the middle" attacks.  Since SQL Server 2005, a self-signed certificate is automatically created at startup to encrypt the contents of the login packets, even if a certificate is not being used for channel encryption.  Starting in SQL Server 2017, that self-signed certificate is now generated using a SHA256 algorithm.  Whilst this is more secure than its predecessor, it's still recommended that certificates are requested from a trusted CA when encrypting connections.    

The certificate must meet certain requirements for SQL Server to load it correctly.  As you'll see, there's nothing particularly special, although there are a couple of considerations.  Firstly, the Subject property on the certificate must specify a Common Name (CN) that is the same as the host name or fully qualified domain name (FQDN) of the SQL Server.  The same applies to the DNS name field in the Subject Alternate Name (SAN) property.  The SAN property allows you to specify alternative server or domain names, which you could use when connecting to SQL Server.  For example; if my SQL Server host name is CONTOSO-SQL01, then:

• The Subject property CN field should be equal to CONTOSO-SQL01.contoso.com
• The SAN property DNS field should contain CONTOSO-SQL01.contoso.com and CONTOSO-SQL01

Providing the client has the CA certificate that signed the original TLS certificate you requested, no further configuration is necessary.  This is very common in organisations with a Root or Intermediate CA, where the corresponding CA certificates are already installed on client machines.  If you purchase a third-party certificate, you'll need to import their CA certificate onto the client as an additional step.

Once the certificate has been imported into the local machines certificate store (on the SQL Server), the SQL Server service account will need read permissions to the private key so that the certificate can be loaded for encryption.  Without this read permission, the certificate won't load correctly and the SQL Server service will fail to start.  An error will also be recorded in the SQL Server Error Log stating that fact.   

If you want to encrypt connections to a SQL Server failover cluster instance, the process is a bit more convoluted.  As before, you should make a request for a certificate and place it in the local machine certificate store on the active cluster node.  But this time the CN field should contain the FQDN of the clustered SQL Server Virtual Network Name (VNN).  For example, if my SQL Server VNN is CONTOSO-SQLFCI, then:

• The Subject property CN field should be equal to CONTOSO-SQLFCI.contoso.com
• The SAN property DNS field should contain CONTOSO-SQLFCI.contoso.com and CONTOSO-SQLFCI

Once that bit is out of the way, fail over the clustered instance and reboot the previous active node for the change to take effect.  On the new active node, the same certificate should be imported, and the same steps carried out again to update the Thumbprint in the registry.  For more information about enabling encrypted connections on clustered installations, I encourage you to read this article.  If you have any problems along the way, check there are no invisible characters within the Thumbprint value which may have been copied accidentally (yes this has happened to me!).  This article provides a safe method for copying the Thumbprint value using certutil.exe which I highly recommend.

Update, November 2018: I'm pleased to say that Microsoft has recently announced that certificate management has been improved in SQL Server 2019 (CTP 2.0).  Not only can you view and validate certificates close to expiration, you can also deploy them across all your Availability Replicas and failover cluster instance nodes using SQL Server Configuration Manager!  This is fantastic news as it simplifies the whole configuration process for highly available deployments!

Finally, if you are using Availability Groups with Listeners, a certificate should be installed on each Availability Replica as before.  However, this time the Availability Group Listener name should be included in the DNS field of the SAN property.  For example, if my Availability Replica host name is CONTOSO-SQL01 and the Availability Group Listener name is CONTOSO-AG-LST, then:

• The Subject property CN field should be equal to CONTOSO-SQL01.contoso.com
• The SAN property DNS field should contain CONTOSO-SQL01.contoso.com, CONTOSO-SQL01, CONTOSO-AG-LST.contoso.com and CONTOSO-AG-LST

SQL Server configuration

Once you've successfully requested and installed the certificate, it needs binding to the database engine service in SQL Server Configuration Manager.  Simply expand "SQL Server Network Configuration", right-click "Protocols for <instance>", select "Properties" and choose your certificate from the drop down list on the "Certificate" tab. 

The next step is to enable encryption.  If you want to ensure that all clients connecting to SQL Server use encryption, you can force encryption on the server by setting "Force Encryption = Yes" in SQL Server Configuration Manager.  However be aware, clients that do not support encryption will fail to connect.  

Alternatively, if you'd prefer to be more selective and only allow specific clients to request encrypted connections, you can specify "Encrypt = True" on the application connection string.  So providing the server has a valid server certificate installed, the connection between that client and server will be encrypted.  You may have heard of the connection string parameter; "TrustServerCertificate".  This is responsible for determining whether the channel is encrypted by skipping the certificate validation process, which means the client doesn't validate the TLS certificate against the issuing CA certificate.  Generally, it's recommended that you set "TrustServerCertificate = False" when enabling encryption on connection strings.  By not trusting the server certificate, you are forcing the transport layer to validate the certificate chain, which is inherently more secure as it helps to prevent man-in-the-middle attacks.

Finally, once the database engine service has been restarted, a similar entry to what you see below will be logged in the SQL Server Error Log, confirming the certificate has been load successfully:

The certificate [Cert Hash(sha1)"E4A3B32A024198D981B385D44E75D810A5425BD1"] was successfully loaded for encryption

How do I know it's working?

To ensure that your connections are being encrypted, launch SQL Server Management Studio (SSMS), connect to the instance (you may need to tick the "Encrypt Connection" option if you're controlling encrytion on the client) and execute the following query:

SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID

What about Microsoft Azure?

Encrypting connections to SQL Server virtual machines in Microsoft Azure is the same as on-premises; a certificate is needed from a CA issuer and encryption needs forcing within the client application or SQL Server.  As you would expect, Azure SQL Database provides a managed way of configuring encrypted connections.  Within the application connection string, you just need to add the following parameters; "Encrypt = True" and "TrustServerCertificate = False" and that's it!  You're all set!

Tweet