SQL Server and client encryption summary
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:
- Open a new query window in SQL Server Management Studio (SSMS) and connect to the SQL Server instance.
- Execute the following T-SQL command to check the value of
encrypt_optioncolumn. For encrypted connections the value will beTRUE.
SELECT * FROM sys.dm_exec_connections