Does the SSL connection work for SQL Server without Certificate?

Question

when I connect to a SQL Server using SSMS, i checked the checkbox "Encrypt Connection" and "Trust Server Certificate". On SQL Server side, I didn't add any certificate to SSCM (SQL Server Configuration Manager)

But when I query sys.dm_exec_connections for my connection, the column "Encrypt_option" is TRUE.

I want to know if the connection is SSL encrypted connection?
If yes, what server certificate is being used?

Answer 1

See the following from the SQL Server Client Documentation

"SQL Server always encrypts network packets associated with logging in. If no certificate has been provisioned on the server when it starts up, SQL Server generates a self-signed certificate which is used to encrypt login packets."

Refer: https://learn.microsoft.com/en-us/sql/relational-databases/native-client/features/using-encryption-without-validation?view=sql-server-ver16

Answer 2

Hi @David Qin ，

To enable encryption to be used when a certificate has not been provisioned on the server, SQL Server Configuration Manager can be used to set both the Force Protocol Encryption and the Trust Server Certificate options. In this case, encryption will use a self-signed server certificate without validation if no verifiable certificate has been provisioned on the server.

You can try to enable TrustServerCertifiate (be set to true) in the client configuration,

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.