“In transit” encryption to SQL Server

To encrypt connections to the SQL Server engine, there are a few options:

  1. IPsec, that encrypts the whole network packet
  2. SSL (Secure Sockets Layer) using “Force Encryption”, which only encrypts the packets sent to the database
  3. IPsec + SSL

“In transit” encryption is the encryption of the data packets at the network level. IPsec does this well, but is set through the operating system and encrypts ALL packets that come and go to the server. If the network stream needed to be analyzed to troubleshoot a problem, IPsec would have to be turned off. This might involve the network analyst, the server admin or a combination of the two. If the DBA does not wear one or both of those hats, it’s extra resources that may have to be tapped.

Using SSL with SQL Server is a relatively easy task. It has the advantages of only encrypting the traffic being sent to the SQL Server engine and can be controlled (for the most part) solely by the DBA.

I won’t go into the implementation of IPsec, but will offer this site.

Encryption using SSL has a few choices: use a certificate from a trusted root authority, create your own certificate or use the self-signed certificate that gets created when SQL Server starts.

A certificate from a trusted root authority needs to be purchased from a vendor such as GoDaddy (Google for more vendors).

You can create your own certificate using the IIS Resource Kit. Here’s a link that has a decent explanation; the Microsoft documentation will have more information.

For the above two options, the certificate will need to be imported into the SQL Server Windows server and into any clients wanting to connect to the database engine.

To install a certificate (from SQL Server Books Online):

  1. On the Start menu, click Run, and in the Open box, type MMC and click OK.
  2. In the MMC console, on the File menu, click Add/Remove Snap-in.
  3. In the Add/Remove Snap-in dialog box, click Add.
  4. In the Add Standalone Snap-in dialog box, click Certificates, click Add.
  5. In the Certificates snap-in dialog box, click Computer account, and then click Finish.
  6. In the Add Standalone Snap-in dialog box, click Close.
  7. In the Add/Remove Snap-in dialog box, click OK.
  8. In the Certificates snap-in, expand Certificates, expand Personal, and then right-click Certificates, point to All Tasks, and then click Import.
  9. Complete the Certificate Import Wizard, to add a certificate to the computer, and close the MMC console. For more information about adding a certificate to a computer, see your Windows documentation.

To enable SSL in SQL Server using the imported certificate, open the ‘SQL Server Configuration Manager’. In the left hand pane, right click ‘Protocols for MSSQLSERVER’ and choose ‘Properties’. (If you’re connecting to a named instance, it will be ‘Protocols for “named instance“‘):

SQL Server Configuration Manager

Choose the ‘Certificates’ tab, then choose your imported certificate from the drop down menu.

Choose the ‘Flags’ tab and set ‘Force Encryption’ to ‘Yes’. SSL will now be implemented once the SQL Server service is restarted.

To enable SSL using the self-signed certificate that SQL Server creates on each service start, simply set ‘Force Encryption’ to ‘Yes’ and restart the service. No certificate needs to be installed (see above).

Notes:

The self generated and self-signed certificates offer the weakest security. The self-signed certificate encryption method does not protect against “Man in the Middle” attacks.

“In transit” encryption could have a performance impact, as there is additional overhead in the encryption and decryption of the packet.

Clients not using the SQL Server connection libraries may not be able to communicate with the engine when encryption is turned on. Tests can be done using the self-signed certificate.

Regardless if “Force Encryption” has been enabled, SQL Server always encrypts the authentication process when a SQL Server login connects to the engine.