An ideal way to verify if the connections are using TLS 1.2 protocol or not is through some network monitoring tools like Netmon or Wireshark. However from SQL Server, we can monitor them using extended events and can be done only for SQL Server 2012 SP2 and above. 

Below is the Process:

  • Open SSMS and connect to the instance. 
  • Create a new extended event session 
  • In the Events selection screen, select the Channel “Debug” and uncheck everything else as shown below 

 

 

 

 

 

 

 

 

  • Select the event “trace” and click on Configure button on the top

 

 

 

 

 

 

 

 

 

  • In the Filter predicate tab select the following:
    • Field: function_name
    • Operator: “=”
    • Value: “Ssl::Handshake”

 

 

 

 

 

 

 

 

  • Click on OK and run the session

To confirm if SQL Server is using the certificate configured:

  • After configuring the certificate restart SQL Service.
  • Open SQL Server Error Log and you should find an entry as below

“The certificate [Cert Hash(sha1) “<hex number>”] was successfully loaded for encryption”

  • Make a note of the hex number
  • Open Certificate MMC and right click on certificate and click on Open.

Click on the Details tab and select the Field “Thumbprint” which should be the same as you had seen in SQL Error Log

 

 

 

 

 

 

 

 

 

 

 

I hope you found this post helpful.

Leave any feedback in the comment and i will get back at the earliest.

-Hari Mindi


2 Comments

Jeff Romatoski · February 13, 2019 at 7:26 pm

I am trying to follow these instructions. I am using SMSS 2017. When I get to the step to add the Event called “trace” I do not see and event with the name “trace”. Is there an equivalent in SMSS 2017?

    harimindi · February 14, 2019 at 7:41 pm

    It is dependent on version of SQL Server being used. The one i used on the screenshots is SQL Server 2016 with SSMS 2017. If you are using appropriate SQL Server then please check if you have selected selected “debug” in Channel column shown during the event selection. By default “debug” is unchecked and you would find “trace” event only when you select debug channel.

Leave a Reply

Your email address will not be published. Required fields are marked *

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