SQL Server – Configure to Encrypt Data Traffic in Both Directions

certificateencryptionsql serverssl

How can I ensure that SQL Server data traffic is encrypted over the wire both from server to client and from client to server?

I have set Force Encryption to Yes and that alone seems to hide query information from Profiler and MS Message Analyzer. Is that all I need to do? A lot of documentation states that a certificate is also required.

So I created a p12 certificate, imported it into the certificate store and selected it from the "Certificate" tab in SQL Server Configuration Manager Protocols. Then I was unable to restart the service even after removing the certificate.

I'd like to get some clear instructions on how to achieve wire encryption.

Best Answer

There are two possible ways to ensure that you encrypt connections from clients to the SQL Server. The first option is to setup IPsec either manually on both clients and server or by using a IPSec policy or you can setup SSL encryption. The simplest way to do so is to use the self signed certificates from the server but as those are exchanged during the connection handshake their security is limited so it's recommended to use certificates from a trusted root.

You can create a test certificate with MAKECERT.exe by following TheSQLDude instructions and copy the self created root certificate to all the clients.

So to set up SSL encryption over the wire in SQL Server you either need a certificate signed by a trusted provider, which means that your SQL Server needs to have a valid FQDN as no certificate providers create certificates for invalid Fully-Qualified Domain Names (eg .local). Or you can also install your own trusted PKI infrastructure for instance Active Directory Certificate Services (ADCS).

This is needed as all the clients need to trust the root certificate that signs the certificate you install on the SQL Server.

When creating a certificate for SQL server the certificate needs to be created with certain extended properties most importantly it needs to be setup as Server Authentication (1.3.6.1.5.5.7.3.1) which means you might need to create a template in ADCS or you can create the certificate CSR using certreq

For this you need to create an csr.inf file 

[Version]
Signature = "$Windows NT$"
[NewRequest]
Subject = "CN = SERVER.DOMAIN.TLD"
FriendlyName = SERVER.DOMAIN.TLD
MachineKeySet = true
RequestType=Cert
;SignatureAlgorithm = SHA256
KeyLength = 4096
KeySpec = 1
KeyUsage = 0xA0
MachineKeySet = True
Exportable = TRUE
Hashalgorithm = sha512
ValidityPeriod = Years
ValidityPeriodUnits = 10
[EnhancedKeyUsageExtension]
OID=1.3.6.1.5.5.7.3.1

You can then create a CSR to send to a CA

certreq.exe -new csr.inf keycertrequest.csr

or get a certificate from ADCS by in an elelvated command prompt

Certreq -new -f cer.inf Certificate.cer

And then you can export that certificate from your personal store and import that to the store for the SQL Server account.

When you have a certificate installed you can enable encryption in the SQL Server Configuration manager. It's recommended to only use TLS 1.2 but then you need make sure you have updated the SQL Server native client and your SQL Server to the latest update. This is all well explained by SentryOne and in the release announcement

Related Question