Configuring SQL Server for SSL Encryption

Question

I'm new to the DBA role and I've been tasked with configuring a SQL Server install for a client, with the requirement that databases will be distributed across two instances. Connections to one of the instances require encryption under an SSL certificate so I'm hoping that I could gather some advice on how best to implement and test the encryption.

I've obtained a trusted certificate from a Certification Authority and installed this on the server, and set the properties on INS1 protocols to force encryption using the new cert.

When I query the Exec Connection DMV the encrypt_option value shows as True for all active SPIDS. However, I'm able to query the instance under an encrypted connection via SSMS on my local machine, and I don't have a copy of the certificate installed locally. I was under the impression that without a corresponding copy of the certificate client side my connection would be refused as I would need access to the public / private keys in order to decrypt / encrypt any data exchanged over the connection.

Questions

• What is the expected behaviour if the client does not have a copy of the certificate installed and ForceEncryption=True on the server?
• Should a connection to the instance be refused, or should an unencrypted connection be allowed assuming the client has the required credentials to access the instance?
• Could it be that I'm allowed to establish an encrypted connection without a copy of the certificate installed client side, due to the fact that the issuing CA is a Trusted Root CA rolled out as part of our Group security policy?

Considerations

Additionally; external clients will be connecting to the instance via a Web Server in our DMZ, then routed to an internal app server which communicates with the DB server. In this example would each device in the chain need a copy of the certificate installing (client machine, web server, app server, database server)?

Unfortunately as an inexperienced DBA there's probably a whole host of things I haven't even considered, so any advice that can be provided will be greatly appreciated. It would be good to have input from people who've implemented a similar solution if possible.

Response to comment

Can you elaborate on "that databases will be distributed across two instances"?

The client has requested that the databases that will be hosted on this server are split across two instances, with the first instance hosting databases that require encrypted connections and the second instance hosting databases which allow unencrypted connections.

Many thanks

Answer 1

When the client connects to the server, the server provides its certificate. If the client is not explicitly configured to trust whatever certificate the server returns, it will check the client keystore to see if it can trust the certificate. The client keystore does not have to contain a copy of the server's certificate if it trusts the chain of certificate authorities that issued the certificate.

It is the exact same principle as to how your browser establishes an HTTPS connection to a web server. Although you may not know it, the browser's keystore is configured to trust numerous certificate authorities, so as long as the certificate that is returned by the site was created by one of those certificate authorities, it will trust it. This is what allows you to establish an HTTPS connection to your bank's website, for example, without installing certificates from the bank. The bank used GoDaddy or DigiCert or a similar public certificate company, and browsers are configured by default to trust these "known good" certificate generators.

For example, an organization typically installs root and infrastructure certificates in the keystore of every Windows system on their network. Then when SQL Server (or a web server or whatever) has a certificate installed that was generated by that organization's key infrastructure, most native Windows processes will trust the certificate because they'll use a Microsoft SQL Client driver, which refers to the Windows keystore to determine what certificates to trust.

A Java SQL client, such as Microsoft JDBC and jTDS, will NOT use the Windows keystore, so one would need to install the root and infrastructure certificates into the keystore that the associated Java process is using (a file named "cacerts", typically, but JDBC allows the keystore file to be specified). The other option is to configure the connection string such that whatever certificate is returned by the server is trusted. However, this would present the risk of connecting to a rogue server and it would trust its certificate.

There are a few other SQL clients out there, such as DataDirect Wire Protocol, that have options to specify a certain keystore, trust the server certificate, etc.

Could it be that I'm allowed to establish an encrypted connection without a copy of the certificate installed client side, due to the fact that the issuing CA is a Trusted Root CA rolled out as part of our Group security policy?

Yes, as explained above.

What is the expected behaviour if the client does not have a copy of the certificate installed and ForceEncryption=True on the server? Should a connection to the instance be refused?

I might amend this question to, "What is the expected behavior if the client does not trust the certificate..." since it doesn't have to have a copy of the certificate as long as it trusts the chain that generated it. ForceEncryption is exactly what it sounds like--it will NOT allow an unencrypted connection. The client has to trust the certificate that is returned and establish an SSL connection in order to connect and login to SQL Server. If the client doesn't trust the certificate, the client will not be able to login and you'll see an error in the SQL Server error log.

Additionally; external clients will be connecting to the instance via a Web Server in our DMZ, then routed to an internal app server which communicates with the DB server. In this example would each device in the chain need a copy of the certificate

No, the only system that needs to trust the certificate that SQL Server returns is the system that has a connection to the SQL Server. For example, with a web application, the web server that connects to SQL Server would need to trust the certificate, or the chain that generated the certificate, that is returned from SQL Server. The browsers that connect to the web application would need to trust the certificate that the web server returns. The browsers are not connecting to SQL Server, and the SQL Server's certificate would not be forwarded by the web server to the browsers. The browsers are not establishing a connection to SQL Server, so they don't need to trust the SQL Server's certificate.

Answer 2

In addition to Tony's great answer, it's worth noting that you can include TrustServerCertificate=True in a SQL Server connection string (docs). This tells the client that it should trust any certificate that the server presents. This enables connections to be encrypted even if the certificates aren't set up right, e.g. if the server has a self-signed certificate.

Chain of events is something like this:

• Client tries to connect to server, connection string has Encrypt=True and TrustServerCertificate=True.
• Server responds and presents its certificate.
• If the certificate isn't issued by a known CA, at this point the client would normally say "No way, that certificate looks dodgy!". But because the connection string has TrustServerCertificate=True it says "Who cares what the certificate looks like" and accepts the connection and continues.

The security hole is that your clients might connect to a nefarious server pretending to be your server, or a man-in-the-middle server that looks at all the data going past. Besides this (significant) security issue, the connections are otherwise encrypted.