Encrypt Connection - how do I know what certificate is being used for the current connection

Question

Encrypt Connection - how do I know what certificate is being used for the current connection

Maxer

SSCrazy Eights

Points: 8964
More actions
September 26, 2018 at 8:45 am

#379584

*When I connect using Trust Server Certificate - how can I see what certificate is being used?
Using SSMS I can connect to the SQL server, and check "Encrypt Connection" if I do that it fails saying:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)
I can then check "Trust server certificate" and it will work.
However, it seems that not every client app out there supports that (using Power BI Desktop for example does not seem to have a Trust server certificate option).
I was wondering, how do I know what certificate is being used by SQL and my local SSMS connection when I check "Trust server certificate"?
I know I can query sys.dm_exec_connections and see that the connection IS encrypted, but using what cert?
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

jasona.work SSC Guru Points: 50117 More actions · Answer 1

The certificate used for encrypting connections isn't stored in SQL Server, but in the OS. If you want to check the certificate, you need to go to SQL Configuration Manager in Windows, then SQL Server Network Configuration and right-click on Protocols for MSSQLSERVER. Then look in the Certificate tab and you can see the expiration, friendly name, and issued by / to fields for the certificate being used.

If it's a "self-signed" cert, that would explain the "...issued by an authority that is not trusted" message you're getting. There're ways to work around this, but they require making changes on every client that uses the server.

The better solution would be to obtain a certificate from a trusted provider (either a commercial certificate provider, or if you're on an Active Directory, it should have its' own certificate authority) and install that cert to the server, then switch the cert SQL uses to encrypt connections.

Maxer SSCrazy Eights Points: 8964 More actions · Answer 2

jasona.work - Wednesday, September 26, 2018 10:42 AM
The certificate used for encrypting connections isn't stored in SQL Server, but in the OS. If you want to check the certificate, you need to go to SQL Configuration Manager in Windows, then SQL Server Network Configuration and right-click on Protocols for MSSQLSERVER. Then look in the Certificate tab and you can see the expiration, friendly name, and issued by / to fields for the certificate being used.
If it's a "self-signed" cert, that would explain the "...issued by an authority that is not trusted" message you're getting. There're ways to work around this, but they require making changes on every client that uses the server.
The better solution would be to obtain a certificate from a trusted provider (either a commercial certificate provider, or if you're on an Active Directory, it should have its' own certificate authority) and install that cert to the server, then switch the cert SQL uses to encrypt connections.

Thanks, I checked that drop down and it is empty which is a bit confusing.

So no tickets are listed on the certification tab drop down.

jasona.work SSC Guru Points: 50117 More actions · Answer 3

Maxer - Wednesday, September 26, 2018 10:45 AM
jasona.work - Wednesday, September 26, 2018 10:42 AM
The certificate used for encrypting connections isn't stored in SQL Server, but in the OS. If you want to check the certificate, you need to go to SQL Configuration Manager in Windows, then SQL Server Network Configuration and right-click on Protocols for MSSQLSERVER. Then look in the Certificate tab and you can see the expiration, friendly name, and issued by / to fields for the certificate being used.
If it's a "self-signed" cert, that would explain the "...issued by an authority that is not trusted" message you're getting. There're ways to work around this, but they require making changes on every client that uses the server.
The better solution would be to obtain a certificate from a trusted provider (either a commercial certificate provider, or if you're on an Active Directory, it should have its' own certificate authority) and install that cert to the server, then switch the cert SQL uses to encrypt connections.
Thanks, I checked that drop down and it is empty which is a bit confusing.
So no tickets are listed on the certification tab drop down.

I would suspect in that case (but I can't confirm presently, I don't have a server that doesn't have a trusted cert) that it's using the OS built-in self-signed certificate.

Again, the best solution is to get a trusted certificate.