How to Enable Encrypted Connection to 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 (1.3.6.1.5.5.7.3.1).
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.
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.
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.
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.
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.

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 OK 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.
In the Connect to Server dialog box, complete the connection information, and then click Options.
On the Connection Properties tab, click Encrypt connection.
Click on Connect.