SQL Server now uses the Transport Layer Security (TLS) to encrypt data. To do so, you need a certificate with its name matching the fully qualified domain name (FQDN) of your SQL Server and make sure it is issued for Server Authentication with a KeySpec AT_KEYEXCHANGE ( not AT_SIGNATURE ). To ease that process, sign your server certificate with your Enterprise Certificate Authority that is already trusted and installed on your clients computers.
Example Below in PowerShell to be performed by your administrators:
<# Create a certificate signed by our Enterprise Certificate Authority to authentify your SQL server and give it an encryption key it can exchange with your computers to encrypt the connections. Make it valid for 3 years and make that key exportable so you can install it on your server#>
$certSQLServerEncrypt = New-SelfSignedCertificate -Subject “YOURSQLSRV.YOURDOMAIN.local” -KeySpec KeyExchange -type SSLServerAuthentication -CertStoreLocation “cert:\LocalMachine\my” -NotAfter (Get-Date).AddYears(3) -KeyExportPolicy Exportable -Signer $certEnterpriseCA
Export your certificate from your administrator’s computer, with the key and preferably as a pfx file. Then import it on your SQL Server in the local computer certificate store (run certlm.msc). The SQL Server Service Account must have the necessary permission to access the TLS certificate. It means you need to add read permission for this account. To do so, locate the certificate under Personal\Certificate, right click on it, select All Tasks and Manage Private Keys… then add your service account (the one you see running the instance in SQL Server Configuration Manager)
Open SQL Server Configuration Manager (SQLServerManagerXX.msc XX being the version of your SQL server)
Under SQL Server Services, check the details of your service account (Log On As of your SQL Server instance)
Under SQL Server Network Configuration, right click on Protocols for MSSQLSERVER
– under the tab Flags, set Force Encryption: Yes.
– under the tab certificate, select the one that you’ve imported. When all condition in Certificate requirements are met, your certificate should appear in the drop down list in SQL Configuration manager. If not troubleshoot as described here.
– restart the SQL Server Service.
SQL Server will now enforce encryption with your specified certificate. It means there’s no need for the client to specify the option Encrypt when connecting to this SQL Server instance. TLS encryption is performed transparently within the protocol layer, simply upon authentication, an extra network roundtrip is required at connect time, for the server to exchange encryption key with the client.
After both application and server will encrypt and decrypts the packets.
To verify it is working, simply connect as usual with Management Studio, create a new query and execute the below statement:
SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID
Notice it can be verified also in the Properties panel, Connection Details, Connection encryption: Encrypted.
You should go to Options / Connection Properties and check Encrypt connection.
It means encryption is requested at the initiative of the client. There’s no harm, server will do it anyway, but then don’t expect a trust server certificate to popup ; instead you need to check also Trust server certificate. Not doing so would mean the SQL server would hold a certificate under Trusted People, named with your user id and holding an exchangeable encryption key. So the whole configuration the invert way around !
Sources:
- Microsoft: Enable encrypted connections to the Database Engine
- Microsoft: Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager)
- SQLShack: How to set and use encrypted SQL Server connections
- Stackoverflow: SSL Certificate missing from dropdown in SQL Server Configuration Manager627/ssl-certificate-missing-from-dropdown-in-sql-server-configuration-manager