Document toolboxDocument toolbox

Microsoft SQL Server: Enable SSL encryption

Owned by Thomas Wais
Last updated: Jul 14, 2021 by Abir Haque
2 min read

Microsoft SQL Server allows encryption of transmitted data via SSL (Secure Socket Layer).
While that increases security, there is also a loss of performance, when encryption is active.

If the whole data traffic is encrypted via SSL, an additional roundtrip is needed on connection setup and all data must be encrypted by the sender and decrypted by the receiver.

 


Create SSL certificate

First open the dialog for certificate creation: 

  1. Control Panel > Administrative Tools > IIS Manager > Server Certificates > Create Self-Signed Certificate. 

  2. Name the certificate and export it after clicking OK. 

  3. Close the IIS Manager afterwards.

This step is only necessary, if there isn't already an SSL certificate available, otherwise it can be skipped.

 

 

 

Microsoft Management Console

Run the Microsoft Management Console:

  1. Start > Run > mmc. 

  2. Open File > Add/Remove Snap-in > Certificates > Add > Computer account. 

  3. Select the computer, which shall be managed by the Snap-in and click Finish > OK. 

  4. Expand Certificates, right click on the Personal folder and open All Tasks > Import. 

  5. Follow the assistant's instructions and import either the previously created certificate or the pre-existing one. 

  6. Close Microsoft Management Console and restart the SQL Server-Service. 

  Make sure the Service-Account has access to certificates. It is possible it must be run as a local account. 

 

 

 

SQL Server Configuration Manager

Run the SQL Server Configuration Manager:

  1. Expand the SQL Server-Network configuration, right click the option for protocols and select properties. 

  2. Select the option Yes for the field Force encryption in the Flags tab and click OK. 

  • If the option ForceEncryption is enabled, the whole client/server communication is encrypted and clients, which don't support encryption don't get access. 

  • If the option ForceEncryption is disabled, encryption can be requested by the client, but it is not required. 

  1. Select the correct certificate and click 

OK to close the dialog window.

  1. Restart the SQL Server-Service.

  2. SSL-encryption is now active on the Microsoft SQL Server.