Skip to content

sql-server-and-client-encryption-summary.md

Latest commit

 

History

History
59 lines (46 loc) · 5.98 KB

sql-server-and-client-encryption-summary.md

File metadata and controls

59 lines (46 loc) · 5.98 KB
title SQL Server and Client Encryption Summary
description 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.
author suresh-kandoth
ms.author sureshka
ms.reviewer randolphwest, maghan
ms.date 08/26/2025
ms.service sql
ms.subservice configuration
ms.topic concept-article

SQL Server and client encryption summary

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.

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 [!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.

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 [!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.

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 [!INCLUDE ssnoversion-md] connections using the Transact-SQL (T-SQL) commands. To do this, follow these steps:

  1. Open a new query window in [!INCLUDE ssnoversion-md] Management Studio (SSMS) and connect to the [!INCLUDE ssnoversion-md] 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;

Related content