A successful connection still leaves its authentication method to be checked. The auth scheme and encryption columns show different parts of that answer. Joining connection details with session information lets you identify the currently connected driver interfaces and the settings they actually negotiated.
Join the Connection to Its Session
sys.dm_exec_connections describes the connection's transport and negotiated properties. sys.dm_exec_sessions adds login, application, client interface, and client version information. Join them on session_id to read those details together. A connection and a session are related concepts, but not every server row represents a normal user request.
I start with current user sessions and keep the session identifier in the output. That gives the review a concrete connection to investigate. The next query includes connection_id because multiple connection rows can complicate a simple session-level count, especially with multiple active result sets.
The driver interface name is useful inventory evidence, not a guaranteed complete package version. Some client properties come from the client and should not be treated as independently verified identity. The query describes what SQL Server reports for established connections. It does not scan every application installation or prove the settings used by disconnected clients.
SELECT c.connection_id,c.session_id,s.login_name,s.program_name,
s.client_interface_name,s.client_version,c.encrypt_option,c.auth_scheme,
c.net_transport,c.protocol_type,c.protocol_version
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s ON s.session_id=c.session_id
WHERE s.is_user_process=1
ORDER BY s.client_interface_name,c.session_id;Treat the auth scheme as evidence about this connection's authentication, then investigate the client and account context separately.
Keep the Auth Scheme Separate From Encryption
auth_scheme identifies how the connection authenticated. encrypt_option describes whether the connection uses encryption according to SQL Server's connection metadata. A Kerberos connection is not automatically proof of transport encryption, and an encrypted connection does not automatically mean Kerberos authenticated it.
NTLM and KERBEROS describe Windows authentication negotiation. Kerberos depends on the correct service principal name, account configuration, and the connection path. NTLM can appear when that negotiation does not produce Kerberos. SQL authentication appears through its own authentication scheme rather than those Windows choices.
I check the exact connection route before troubleshooting a Kerberos result. Local connections, aliases, host names, and protocol choices affect the context. Test the route the application uses, not an unrelated administrator connection. Security reviews become confusing when a test from the server itself is treated as evidence for a remote application's authentication. The lock icon has a talent for hiding several separate questions.
Group the Current Inventory Without Losing Detail
The grouped query shows combinations of client interface, encryption, authentication, and transport. COUNT_BIG counts connection rows within those combinations. Keep that label explicit. It is not automatically a count of distinct users, applications, or physical computers.
Review unencrypted connections and unexpected authentication choices individually using the first query. The grouped output helps prioritize the investigation, while session and connection identifiers provide the follow-up. A group with many connections can simply represent a pool with multiple open connections.
What combination do you expect for the application under review? Record that expectation before calling a result compliant or noncompliant. The required transport, account type, and certificate policy belong to the environment's security design. This query supplies current evidence, but a generic list of values cannot replace that design. Compare like-for-like routes and client configurations when confirming a proposed change.
SELECT s.client_interface_name,c.encrypt_option,c.auth_scheme,c.net_transport,
COUNT_BIG(*) AS ConnectionRows
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s ON s.session_id=c.session_id
WHERE s.is_user_process=1
GROUP BY s.client_interface_name,c.encrypt_option,c.auth_scheme,c.net_transport
ORDER BY ConnectionRows DESC;
Leave Raw Version Numbers Intact
client_version and protocol_version are numeric encodings. Do not format them as ordinary dotted application versions without using the documented interpretation appropriate to that field. A raw integer is honest evidence. An attractive invented version string is not.
The client interface name helps identify the interface family, while the numeric fields provide additional protocol and client context. Confirm detailed driver deployment versions through the approved application inventory when that is the actual requirement. A live connection alone does not expose every installed component or patch.
Retain the original numeric values when exporting the query result. Spreadsheet formatting can turn identifiers or large numbers into misleading displays. Keep a capture timestamp and instance identifier in the review record too. Those make it clear which server and moment the result describes, without treating a changing DMV as a permanent inventory database. The session list is a snapshot, and snapshots need a date.
Check Your Own Auth Scheme Before Broad Conclusions
The following query isolates the current connection. It is useful when comparing a local administrative session with a remote application route. The selected session identifier is generated by SQL Server, so there is no guessed application identifier to maintain.
Use the same connection string options and network path as the route being tested. An administrator opening SSMS with a different host name or protocol can get a different authentication result. That result is real for the administrator's session, but it does not replace evidence from the application.
If the DMV query requires additional permissions, use an approved administrative account or request the narrow supported permission for the server version. Do not grant broad application privileges merely so a diagnostic query can see every session. Server-level DMV permission requirements differ across versions, and the security review should not create an unrelated permission problem while collecting evidence.
SELECT session_id,net_transport,protocol_type,encrypt_option,
auth_scheme,client_net_address,local_net_address,local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id=@@SPID;Repeat the Check Across Meaningful Windows
This inspection covers current connections only. Short-lived application connections can disappear before you sample them. Sleeping pooled sessions can remain after the business activity that opened them has ended. Choose observation windows that cover the applications the review needs to include.
When connection settings change, establish new connections for validation. Existing pooled connections can retain their original negotiated properties until replaced. Capture the new session evidence and compare it with the intended authentication and encryption policy. Also validate that the application still connects through its normal route.
Keep the detailed inventory and grouped overview together. The first explains individual exceptions, while the second reveals the overall pattern. Use the auth scheme to answer the authentication question, encryption metadata to answer the transport question, and application inventory to answer the deployment question. A useful review keeps those conclusions distinct and supported.
Related reading on this blog: Why Server Authentication is Disabled? What Mode is SQL Server Using Currently? and Network Protocol and IP Address.
A successful connection is not a complete security check, it is a session with properties you can inspect.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
