Edit

Special cases for encrypting connections to SQL Server

  • Article

The client computer must trust the server certificate so that the client can request the SSL encryption, and the certificate must already exist on the server. The most common scenario for SQL Server encryption involves environments that:

  • Forces encryption for all incoming client connections to SQL Server.
  • 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 additional steps for successful encryption after configuring SQL Server for encryption as per the procedure described in Configuring SQL Server for Encryption. This article provides the procedures for encrypting connections to SQL Server for less common scenarios that aren't covered in Configuring SQL Server for Encryption.

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

  1. Configure the certificate on SQL Server as per the procedure documented in Configure SQL Server to use certificates.
  2. Specify the encryption keyword in connection properties to Yes or True. For example, if you're using Microsoft ODBC Driver for SQL Server, the connection string should specify Encrypt=yes;.

Use a certificate issued by an internal CA or created by using New-SelfSignedCertificate or makecert

Scenario 1: You want to encrypt all the connections to SQL Server

After completing both the procedures documented in Step 1: Configure SQL Server to use certificates and Step 2: Configure encryption settings in SQL Server documented in Configuring SQL Server for Encryption, use one of the following options to configure your client application for encryption.

Option 1: Configure client applications to Trust Server Certificate. This setting will cause the client to skip the step that validates the server certificate and continue with the encryption process. For example, if you're using SQL Server Management Studio, you can select Trust Server Certificate on the Options page.

Option 2: On each client, add the certificate's issuing authority to the trusted root authority store by performing the following steps:

  1. Export the certificate from a computer that's running SQL Server by using the procedure documented in Export server certificate.
  2. Import the certificate by using the procedure documented in Add a private Certification Authority (CA) to Trusted Root Certification Authorities certificate store.

Scenario 2: Only some clients need encrypted connections

After configuring the certificate for SQL Server use as documented in Step 1: Configure SQL Server to use certificates in Configuring SQL Server for Encryption, 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 SQL Server, the connection string should specify Encrypt=yes;Trust Server Certificate =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:

  1. Export the certificate from a computer that's running SQL Server by using the procedure documented in Export the certificate from a computer that's running SQL Server.
  2. Import the certificate.
  3. Specify the encryption keyword in connection properties to Yes or True. For example, if you're using Microsoft OLEDB Driver for SQL Server, the connection string should specify Use Encryption for Data = True;

Use the self-signed certificate automatically created by SQL Server

Scenario 1: You want to encrypt all incoming connections to SQL Server

  1. Enable encryption on SQL Server using the procedure Step 2: Configure encryption settings in SQL Server documented in Configuring SQL Server for encryption.

  2. Configure client applications to trust the server certificate. Trusting the server certificate will cause the client to skip the step that validates the server certificate and continue with the encryption process. For example, if you're using SQL Server Management Studio, you can select Trust Server Certificate on the Options page.

Scenario 2: Only some clients need encrypted connections

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 SQL Server, the connection string should specify Encrypt=yes;Trust Server Certificate =Yes;.

Warning

SSL connections that're encrypted by 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 that're generated by the CA. They are susceptible to man-in-the-middle attacks. You shouldn't rely on SSL using self-signed certificates in a production environment or on servers that're connected to the Internet.

Note

No additional configuration is required on the SQL Server for this scenario.

Next steps