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.