Daily Archives: September 14, 2012

How to encrypt SQL TDS connections

If your SQL clients (for example developers’ SQL Management Studios) and SQL Servers are not really “close” to each other, you should encrypt the tabular data stream (TDS) channel with SSL.

First, you will need a valid certificate. It must be issued by a trusted third party and the FQDN on it must exactly match the name of the SQL Server. To make your life easier, don’t trick with DNS aliases and DNS suffixes.

Next, import the certificate to the Personal Certificates store of the Local Computer account. Start the Microsoft Management Console (mmc), add the Certificates snap-in and when asked, select the Computer account. Expand the Personal\Certificates branch, then you can find the Import… menu item in the All Tasks group of the context menu:


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:


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!):


Start SQL Server Configuration Managert and navigate to the SQL Server Network Configuration –> Protocols for [instance name] branch, then open the Properties dialog:


Click the second, Certificate tab and select the certificate from the list:


If you don’t see your certificate here, check the certificate’s validity, FQDN, name resolution and permissions.

On the first, Flags tab, you can enable Force Encryption to make sure that all clients encrypt the communication:


By forcing the encryption you enable only those clients, that specify Encrypt=True in the connection string. If the client is SQL Server Management Studio, you can enable encryption on the Connection Properties tab by checking the Encrypt connection checkbox:


You can verify the encryption by Wireshark, or you can query the properties of the active connections directly from SQL Server via the sys.dm_exec_connections dynamic management view. This query lists the most important connection properties:

SELECT session_id, net_transport, client_net_address, local_net_address, 
       local_tcp_port, auth_scheme, encrypt_option 
FROM sys.dm_exec_connections

You can sleep well, if you see TRUE in the encrypt_option column.


Technorati-címkék: ,,,