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.
- 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?
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.