Encrypt SQL Server connections

SQL Server connections can and should be encrypted where possible but must when passing through public circuits.

The overview of the process of setting up SQL Server connection encryption is as follows.

  1. Get a certificate issued by the CA in your domain. If the data has to pass over Internet then cert from a publicly trusted issuing authority is required.
  2. Import the certificate using Microsoft console.

    Ensure the certificate is issued as the FQDN of the server any mismatch will make it useless for SQL Servere at least.

    3299_mmc_snap-in

    To make sure your SQL Server can use this certificate, select the certificate on the right pane, then click the All Tasks –> Manage Private Keys… menu item:

    sql-ssl-acl

    You will get a usual ACL editor dialog. Click Add and select the account that runs your SQL Server instance. If you use SQL Server 2012, it by default runs with a managed service account that you can reference as NT Service\MSSQL$instancename. After selecting the account grant Read permission to it (you don’t need Full control!):

    sql-ssl-acl

  3. Configure SQL Server to use the certificate to forcefully encrypt the connection using SQL Server configuration manager.

sqlserverencryptionstep2

  1. Explicitly specify that that you require encryption on your connection via connection string or Management studio.

sql-ssl-ssms-encrypt

 

 

Advertisements
About

IT professional with wider interest in technology

Tagged with: , , , , ,
Posted in security, sql, SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: