Quickly Check If SSL For SQL Is Enabled

To quickly check SSL is configured on your SQL Server you can run the following query.

SELECT session_id, encrypt_option
FROM sys.dm_exec_connections

This query dumps the information about the connections to your SQL Server and any of them using a secured connection will have a ‘True’ value in the encrypt_option column.

What if you don’t see any SSL secured connections?

If you don’t see any connections with the encrypt_option value as true then the SSL is probably not configured on your SQL Server.

You are not forcing encryption with the SSL installed on the server and the client connecting does not have a copy of the certificate.

  Solution – Set the Force Encryption Flag to Yes

This is a simple toggle box to change on the SQL Server configuration though it does require a service restart. (This means downtime!)

You can check the setting of this by logging onto the SQL Server and checking the values in the SQL Server Configuration manager protocols for your instance of SQL Server. Expand the SQL Server Network Configuration section and right-click on the Protocols for <instance name> and open the properties. Here you will see the Flags tab is probably set to No. You can change this, but as noted a restart will be required. It can also be set to Yes without properly provisioning a certificate in the certificate tab. When this is done SQL Server will generate a self-signed certificate to use. If you have your system configured this way you will want to ensure that any connection strings have the ‘TrustServerCertificate’ value passed or you may not be able to connect properly.

[divider]Force Encryption does force all information sent across the network be encrypted, but it does so at the cost of proper certificate validation. An assumption is made that the server and clients are who they say they are via the self-signed certificate. It does open the door to possible man in the middle attacks, but is still better than no encryption whatsoever.

[divider_top][icon_box icon=”users” title=”Who is Chris Bell?”]

Chris Bell, SQL Server MVP, MCSE & MCITP, is a 20 year veteran of using Microsoft products & SQL Server to create solutions for businesses, organizations and individuals. Chris speaks, blogs, writes articles and makes media of all kinds regarding SQL Server at WaterOxConsulting.com. Chris is also the founder of WaterOx Consulting, Inc. which features SQL Server consulting services along with the destination location week-long training series: SQL Summer Camp.

Click here to contact Chris or to follow him on twitter. [/icon_box]