How to Enable Encrypted Connection to SQL Server

Microsoft SQL Server

Enable Encrypted Connection to SQL Server

Enabling encrypted connection to SQL Server increases the security of data transmitted across networks between SQL Server instance and SQL clients/applications. Since, encryption demands some additional processing, enabling encryption can downgrade the SQL Server performance. Following article describes how you can enable encrypted connections to SQL Server Database Engine.

Server & Certificate Prerequisites

  • The SSL certificate must be issued for server authentication. The certificate name (CN) must be the fully qualified domain name (FQDN) of the machine. You can verify this as Enhanced Key Usage property of the certificate, which must specify Server Authentication (
  • The server must have an installed (imported) certificate and SQL client machines must be set up so as to trust the root authority of the certificate.
  • You must be running SQL Server Configuration Manager under the same user account as the SQL Server Service unless the service is running as a Local System, Network Service or Local Service.
  • The certificate must be stored either  in a local computer certificate store or the current user certificate store.
  • The system time must be such, which is after the “Valid from” property of the certificate and before the “Valid to” property of the certificate.
  • The client must be able to verify the ownership of the certificate used by the server.

We will go through the following points to enable encrypted connection with SQL Server :

  • Provide with (install) a certificate on the server
  • Export the server certificate
  • Configure server to accept encrypted connections
  • Encrypt a connection from SQL Server Management Studio

Provide with (install) a certificate on the server

Providing with a certificate is the process of installing a certificate by importing it into your machine.

  • On the Start menu, click Run. In the opened dialog box, type MMC and click OK.
    Run Menu
  • In the MMC console, on the File menu, click Add/Remove Snap-in.
  • In the Add/Remove Snap-in dialog box, click Add.
  • In the Available Snap-in dialog box, click Certificates, and then click Add.Add Remove Snap In
  • In the Certificates snap-in dialog box, click Computer account, and then click Finish.
  • Now, in the Add/Remove Snap-in dialog box, click OK.
  • Go to Certificates snap-in, expand Certificates, expand Personal folder, and then right-click Certificates, point to All Tasks, and then click Import.Import Certificate
  • At Certificate Import Wizard, browse the certificate path and add to the computer, close the MMC console.

Export the server certificate

  • Go to Certificates snap-in, locate your certificate in the Certificates/Personal folder, right-click the Certificate, point to All Tasks, and then click Export.Export Certificate
  • Complete the Certificate Export Wizard, storing your certificate file at your convenient path.

Configure SQL server to accept encrypted connections

  • Open SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for <server instance name>, and then select Properties.

SQL Server Configuration Manager

  • At Properties dialog box, on the Certificate tab, select the desired certificate from the drop down menu for the Certificate box, and then click OK.
  • On the Flags tab, in the Force Encryption box, select Yes, and then click OKSQL Express Properties to close the dialog box.
  • Restart the SQL Server service.

Encrypt a connection from SQL Server Management Studio

  • Open SQL Server Management Studio
  • On the Object Explorer toolbar, click Connect, and then click Database Engine.Microsoft SQL Server Management Studio
  • In the Connect to Server dialog box, complete the connection information, and then click OptionsConnect to SQL Server.
  • On the Connection Properties tab, click Encrypt connection.
  • Click on Connect.
(Visited 25,755 times, 1 visits today)

Leave a Reply

AlphaOmega Captcha Classica  –  Enter Security Code

This site uses Akismet to reduce spam. Learn how your comment data is processed.