Installing a certificate for SQL Server

I am going to assume that you have a certificate already loaded into the certificate store.  If not that will be a blog post for another day.  If you are running SQL Server as a local system or the service account is in the administrators group, you do not need to worry about granting permissions to the keys.

1. Start by opening mmc.exe and add the certificate plugin for the local system.  
2. Once loaded, find the certificate that you want SQL Server to use.  Right-click on the certificate and select "All Tasks-> Manage Private Keys..." Now you want to give the SQL Server service account read access to the keys. 
3. If your hostname does not match the common name of the certificate you will need to perform an extra step later on. While you have the certificate screen open in mmc, right-click on the certificate and copy down the certificate hash.  You may need it later on.  
4. Next add the sql server configuration manager snapin to the mmc console.  Expand SQL Server Network Configuration.  Right click on protocols for MSSQLServer.  Click on the certificate tab and select the certificate you just loaded. 
   If you do not see your certificate you will need to assign it manually by modifying a registry key. To set the certificate you need to modify the following key:  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer\SuperSocketNetLib.  You will set the Certificate value to the certificate hash that you recorded in step 3 above.
8. Press OK.  Now click on SQL Server Services and restart the Sql Server service.  If everything works out the service should start up.  If it fails to start check the logs here: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log
9. Open up SQL Server Configuration Manager and expand SQL Server Network Configuration and right click on protocols for MSSQLServer.  Change the box from Force Encryption No to Yes.
10. Verify that connections are being made using ssl by running the following query:
    If the column encrypt_option is true, then the session is encrypted
11. If you don't want to force all users to use ssl, then the client can add the following to their connection string:  Encrypt=True;TrustServerCertificate=True;