| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic |
|---|---|---|---|---|---|---|---|---|
SQL Server and client encryption summary |
Learn about the steps required to encrypt all connections to the SQL Server, enable encryption connections from specific clients and check if the encryption works. |
padmajayaraman |
v-jayaramanp |
sureshka, randolphwest |
12/08/2022 |
sql |
configuration |
conceptual |
This article provides a summary of various scenarios and associated procedures for enabling encryption to [!INCLUDE ssnoversion-md] and also how to verify encryption is working.
| 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 | [!INCLUDE sssql16-md] and earlier versions use the SHA1 algorithm. [!INCLUDE sssql17-md] 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. |
| 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 | [!INCLUDE sssql16-md] and earlier versions use the SHA1 algorithm. [!INCLUDE sssql17-md] 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. |
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 [!INCLUDE ssnoversion-md] connections using the Transact-SQL (T-SQL) commands. To do this, follow these steps:
- Open a new query window in [!INCLUDE ssnoversion-md] Management Studio (SSMS) and connect to the [!INCLUDE ssnoversion-md] 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