Today I need to install SQL Server on a new development server. Even though “it’s just dev” I want my data to be secure while in transit. This will be a single stand-alone instance on an AWS EC2 virtual machine.
If I were going to configure this for a failover cluster or high availability, I would refer to more detailed documentation that may be found here:
Configure SQL Server Database Engine for encryption – SQL Server | Microsoft Learn
Certificate Management (SQL Server Configuration Manager) – SQL Server | Microsoft Learn
INSTALL CERTIFICATE
To start with, I have obtained a copy of the certificate file and password from my company’s operation team. I will open my local certificate store on the EC2 instance and navigate to install the new certificate.
Next, I will add access for the SQL SERVER SERVICE ACCOUNT that runs my SQL instance.
I do this by RIGHT clicking over the CERTIFICATE name, and selecting ALL TASKS / MANAGE PRIVATE KEYS…
On the SECURITY tab, I select the ADVANCED button.
In the next window, I click the ADD button.
On the next page, I click the SELECT A PRINCIPAL link.
Then I find my SERVICE ACCOUNT and add it.
I will REBOOT the server instance so that the new certificate is registered and ready to use.
SQL CONFIGURATION MANAGER
Once I’ve opened up the SQL Server Configuration Manager, I navigate to the PROTOCOLS item under SQL Server Network Configuration, and select PROPERTIES.
On the first tab, FLAGS, I choose YES to FORCE ENCRYPTION.
In the second tab, CERTIFICATE, I select my certificate from the dropdown list. If the certificate is not there, it may not have installed properly, or I may have forgotten to reboot my server.
After clicking OK, I REBOOT the SERVER INSTANCE once again.
VERIFICATION
I will now check and verify my end-to-end encryption is set to go.
USE [master]
GO
SELECT DISTINCT (encrypt_option)
FROM sys.dm_exec_connections;
GO
End-to-end encryption is now configured.