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=Trueon 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
Best Answer
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.
Yes, as explained above.
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.
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.