Edit

SQL Server and client encryption summary

  • Article

This article provides a summary of various scenarios and associated procedures for enabling encryption to SQL Server and also how to verify encryption is working.

Encrypt all connections to the server (Server-side encryption)

Type of certificate Force encryption in server properties Import server certificate on each client Trust Server certificate setting Encrypt property in the connection string Comments
Self-signed certificate - automatically created by SQL Server Yes Can't be done Yes Ignored SQL Server 2016 (13.x) and earlier versions use the SHA1 algorithm. SQL Server 2017 (14.x) and later versions use SHA256. For more information, see Changes to hashing algorithm for self-signed certificate in SQL Server 2017. We don't recommend this approach for production use.
Self-signed certificate created by using New-SelfSignedCertificate or makecert - Option 1 Yes No Yes Ignored We don't recommend this approach for production use.
Self-signed certificate created by using New-SelfSignedCertificate or makecert - Option 2 Yes Yes Optional Ignored We don't recommend this approach for production use.
Company's certificate server or from a Certificate Authority (CA) that's not in the List of Participants - Microsoft Trusted Root Program - Option 1 Yes No Yes Ignored
Company's certificate server or from a Certificate Authority (CA) that's not in the List of Participants - Microsoft Trusted Root Program - Option 2 Yes Yes Optional Ignored
Trusted root authorities Yes No Optional Ignored We recommend this approach.

Encrypt connections from specific client

Type of certificate Force encryption in server properties Import server certificate on each client Specify Trust Server certificate setting on the client Manually specify encryption property to Yes/True on the client side Comments
Self-signed certificate - automatically created by SQL Server Yes Can't be done Yes Ignored SQL Server 2016 (13.x) and earlier versions use the SHA1 algorithm. SQL Server 2017 (14.x) and later versions use SHA256. For more information, see Changes to hashing algorithm for self-signed certificate in SQL Server 2017. We don't recommend this approach for production use.
Self-signed certificate created by using New-SelfSignedCertificate or makecert - Option 1 No No Yes Yes We don't recommend this approach for production use.
Self-signed certificate created by using New-SelfSignedCertificate or makecert - Option 2 No Yes Optional Yes We don't recommend this approach for production use.
Company's certificate server or from a CA that's not in the List of Participants - Microsoft Trusted Root Program - Option 1 No No Yes Yes
Company's certificate server or from a CA that's not in the List of Participants - Microsoft Trusted Root Program - Option 2 No Yes Optional Yes
Trusted root authorities No No Optional Yes We recommend this approach.

How to tell if encryption is working?

You can monitor communication using a tool such as Microsoft Network Monitor or a network sniffer and check the details of packets captured in the tool to confirm that the traffic is encrypted.

Alternatively, you can check the encryption status of SQL Server connections using the Transact-SQL (T-SQL) commands. To do this, follow these steps:

  1. Open a new query window in SQL Server Management Studio (SSMS) and connect to the SQL Server instance.
  2. Execute the following T-SQL command to check the value of encrypt_option column. For encrypted connections the value will be TRUE.
SELECT * FROM sys.dm_exec_connections

See also

Next steps