1

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.

  • 1
    Which version of SQL Server? – mendosi Nov 8 '16 at 3:18
  • The issue with the certificate happened on SQL Server 2012 but I would like to understand how wire encryption works on 2012, 2014 & 2016. – Guru Josh Nov 8 '16 at 3:20
  • When you created the certificate was it a Server Authentication (1.3.6.1.5.5.7.3.1) certificate with the correct KeySpec and FQDN of the server technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx ? – Spörri Nov 8 '16 at 9:02
  • I created the certificate by following the instructions in this tutorial: blog.didierstevens.com/2015/03/30/… – Guru Josh Nov 8 '16 at 10:24
  • And have you imported the public key for the root cert into the certificate store. that's probably what's stopping you from starting the server. – Spörri Nov 8 '16 at 10:35
2

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

  • I successfully created the certificate with "Certreq -new -f csr.inf Certificate.cer" (note that I have replaced cer.inf with csr.inf to match the originally nominated file name). I changed the "CN" to the FQDN of the server machine. I noticed that this automatically imported the certificate into the local machine store. After adding this certificate in SQL Server Configuration Manager Protocols, I was unable to restart the service. Removing the certificate enabled the service to be restarted. – Guru Josh Nov 9 '16 at 7:04
  • I was able to restart the service after adding read permissions for the certificate in the certificate store. The next problem is that client machines are able to connect despite there being no certificates installed on the client machine. I was expecting either that client machines would not be able to connect or that there would be some kind of "untrusted certificate authority" warning. I am testing this on a local area network. – Guru Josh Nov 9 '16 at 11:25
  • 1
    You are creating a certificate that is signed by your local root in the windows domain. you can double check that by looking at the certificate. All domain computers will trust the local root cert – Spörri Nov 9 '16 at 12:10
  • Is there a way that I can simulate the server and clients being on a different domain/workgroup and force the clients to authenticate as if they were on a different domain/workgroup? If the server and all clients are on the same domain/workgroup, is the use of a certificate for wire encryption superfluous? Will it achieve the same result by simply setting "Force Encryption" to "Yes" and omitting the certificate altogether? – Guru Josh Nov 9 '16 at 13:30
  • Yes, you can create a certificate and import that to a workgroup server along with the public key for the root to trusted roots. This is always better than the self signed certificate as you now know that all computers that connect do have an ad certificate installed – Spörri Nov 9 '16 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.