| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic |
|---|---|---|---|---|---|---|---|---|
Configure Client Computer and Application for Encryption |
Learn how to configure the client computer and application for encryption using self-signed certificates and a certificate automatically by SQL Server. |
suresh-kandoth |
sureshka |
randolphwest, vanto |
01/30/2025 |
sql |
configuration |
how-to |
A client computer must trust the server certificate so that the client can request Transport Layer Security (TLS) encryption, and the certificate must already exist on the server. The most common scenario for [!INCLUDE ssnoversion-md] encryption involves environments that:
- Forces encryption for all incoming client connections to [!INCLUDE ssnoversion-md].
- Use certificates from a public commercial certification authority that Windows already trusts. The corresponding root certificate for the CA is installed in the Trusted Root Certification Authorities certificate store on all the computers in your network.
In this scenario, you don't need to perform extra steps for successful encryption after configuring [!INCLUDE ssnoversion-md] for encryption as per the procedure described in Configure SQL Server Database Engine for encrypting connections. This article provides the procedures for encrypting connections to [!INCLUDE ssnoversion-md] for less common scenarios that aren't covered in Configure SQL Server Database Engine for encrypting connections.
Note
For a complete list of participants in the Microsoft Trusted Root Program, see List of Participants - Microsoft Trusted Root Program.
Use a certificate issued by a public commercial certificate authority and only some clients need encrypted connections
-
Configure the certificate on [!INCLUDE ssnoversion-md] as per the procedure documented in Configure SQL Server to use certificates.
-
Specify the encryption keyword in connection properties to Yes or True. For example, if you're using Microsoft ODBC Driver for [!INCLUDE ssnoversion-md], the connection string should specify
Encrypt=yes;.
Use a certificate issued by an internal CA or created by using New-SelfSignedCertificate or makecert
After completing both the procedures documented in Step 1: Configure SQL Server to use certificates and Step 2: Configure encryption settings in SQL Server in the article Configure SQL Server Database Engine for encrypting connections, use one of the following options to configure your client application for encryption.
Option 1: Configure client applications to Trust Server Certificate. This setting causes the client to skip the step that validates the server certificate and continue with the encryption process. For example, if you're using [!INCLUDE ssmanstudiofull-md] (SSMS) 20 and later versions, you can select Trust Server Certificate on the Login page (or on the Options page in earlier versions).
Option 2: On each client, add the certificate's issuing authority to the trusted root authority store by performing the following steps:
-
Export the certificate from a computer that's running [!INCLUDE ssnoversion-md] by using the procedure documented in Export server certificate.
-
Import the certificate by using the procedure documented in Export and import certificates.
After you configure the certificate for [!INCLUDE ssnoversion-md] use as documented in Step 1 in Configure SQL Server Database Engine for encrypting connections, use one of the following options to configure your client application for encryption:
Option 1: Configure client applications to trust the server certificate and specify the encryption keyword in connection properties to Yes or True. For example, if you're using Microsoft ODBC Driver for [!INCLUDE ssnoversion-md], the connection string should specify Encrypt=Yes;TrustServerCertificate=Yes;.
For more information about server certificates and encryption, see Using TrustServerCertificate.
Option 2: On each client, add the certificate's issuing authority to the trusted root authority store and specify encryption parameters to Yes in the connection string:
-
Export the certificate from a computer that's running [!INCLUDE ssnoversion-md] by using the procedure documented in Export the certificate from a computer that's running [!INCLUDE ssnoversion-md].
-
Specify the encryption keyword in connection properties to Yes or True. For example, if you're using Microsoft OLEDB Driver for [!INCLUDE ssnoversion-md], the connection string should specify Use Encryption for Data = True;
-
Enable encryption on [!INCLUDE ssnoversion-md] using the procedure Step 2: Configure encryption settings in SQL Server documented in Configure SQL Server Database Engine for encrypting connections.
-
Configure client applications to trust the server certificate. Trusting the server certificate causes the client to skip the step that validates the server certificate and continue with the encryption process. For example, if you're using [!INCLUDE ssmanstudiofull-md] (SSMS) 20 and later versions, you can select Trust Server Certificate on the Login page (or on the Options page in earlier versions).
Configure client applications to trust the server certificate and specify the encryption keyword in connection properties to Yes or True. For example, if you're using Microsoft ODBC Driver for [!INCLUDE ssnoversion-md], the connection string should specify Encrypt=Yes;TrustServerCertificate=Yes;.
No extra configuration is required on the [!INCLUDE ssnoversion-md] for this scenario.
Warning
TLS/SSL connections encrypted using a self-signed certificate don't provide strong security, because the length of the key in the self-signed certificates is shorter than the key in the certificates generated by the CA. They're susceptible to man-in-the-middle attacks. You shouldn't rely on TLS/SSL using self-signed certificates in a production environment or on servers connected to the Internet.