Expose connection encryption information to clients

[shueybubbles]

Is your feature request related to a problem? Please describe.

For SSMS we want to give the user full details about their connection security, including encryption status, certificate details, and TLS protocol version.
Such information is needed for both failed and successful connections. For failed connections we want to show the cert validation errors along with the public key cert information so the user can make an informed choice about whether to set Trust Server Certificate or Host Name In Certificate in their connection dialog.

Describe the solution you'd like

• Provide a more detailed data structure in SqlException that contains the certificate information when cert validation fails
• Expose TLS and cert information on SqlConnection objects that are in the Open state.
• Consider exposing cert validation callbacks like you do for token acquisition so apps can have more fine grained control.

It'd be great if this functionality enables an app like SSMS to install the server cert locally to automate the client config steps documented at https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/special-cases-for-encrypting-connections-sql-server?view=sql-server-ver16

Describe alternatives you've considered

We could try to negotiate a TLS connection to the server out-of-band from the SQL connection to get the cert and do our own validation.

[walterpg]

This would help all integrators who must implement interfaces requiring this information. Currently SQL Server looks bad (unsafe) when it is necessary to explain that connection security feedback is not available with a preferred API. Incomplete and/or inferred security info is not useful, and difficult to trust.

It would really be nice to push this out to DbConnection, but that is another story I suppose.

[cheenamalhotra]

In the meantime, noting down an alternative:

If you have VIEW SERVER STATE or VIEW DATABASE STATE permissions, you could fetch this information from sys.dm_exec_connections [corrected] using the connection you want to get information for:

select session_id, encrypt_option, protocol_type, CONVERT(BINARY(4),protocol_version) as tds_version
from sys.dm_exec_connections
where session_id = @@SPID

[walterpg]

@cheenamalhotra :
The first DMV mentioned is an Analytics-only object, and neither it nor the query involves TLS information.

The info needed here are connection negotiation artifacts. Maybe SQL Server squirrels that away somewhere, but it seems more efficient to collect it on the client side, where it should already reside in a connected AuthenticatedStream, etc.

[cheenamalhotra]

Oops, yes I meant sys.dm_exec_connections, and also yes, this should be ideally available via a driver API.

[shueybubbles]

Having the information available during connection is a bit higher priority for administrative applications. Similar to how the web browser and remote desktop clients work, we need to be able to present the certificate information to the user to let them decide whether to install it/trust it etc.

[walterpg]

@shueybubbles:
Agreed. Those users might also prefer to discern other dubiously positive negotiated results, like in a web app. Even (or especially) after SqlClient app deployment. Platform or database config changes can inadvertently result in less secure connections:

• TCP instead of pipes
• TLS1.0
• NTLM auth

Today those results are not available to a security-conscious client app.