-2

an audit has highlighted some (apparently) unencrypted connections on one of our 2 nodes availability groups. The IP address (10.x.x.x) is the IP of the other node in the AG. Does anyone know what the connections with NULL session_ID and (unknown) auth_scheme are?enter image description here

2
  • 1
    Are you asking question about highlighted connection or connections with session id in (85, 86) Commented Mar 11, 2022 at 9:28
  • 1
    AG nodes need to talk to each other (otherwise, how would they synchronize) using the endpoint. My guess is that those are the endpoint connections. They are not going through the same port as normal SQL traffic so I guess this is why they are not encrypted (to validate) Commented Mar 11, 2022 at 13:11

1 Answer 1

3

These connections are most likely the HADR Endpoints for your Availability Group. If you include more columns in your sys.dm_exec_connections query (protocol_type, client_tcp_port, local_tcp_port, num_reads and num_writes) you should find:

  • Both connections have a protocol_type of Database Mirroring
  • One of the connections has a client_tcp_port value of 5022 (if you used the default port during AG setup), a high number for num_writes and a low number for num_reads. This connection is the one sending data to the other AG replica
  • One of the connections has a local_tcp_port value of 5022 (if you used the default port during AG setup), a high number for num_reads and a low number for num_writes. This connection is the one receiving data from the other AG replica

If your findings match the above info, you can check sys.database_mirroring_endpoints and check the columns is_encryption_enabled and encryption_algorithm_desc to confirm that your HADR endpoints were created with encryption enabled.

Since the AG data connections don't use the normal SQL Server ports and don't operate as 'normal' connections, the session_id column is NULL for these connections, and they won't use normal encrypted connections, even if available. Instead, even on SQL Servers not configured for encryption, these HADR Endpoint connections are encrypted separately using the configuration of the HADR Endpoints.

From an auditing perspective, this can probably be dismissed as a false positive provided your HADR Endpoints have encryption enabled.

Reference: Transport Security - Database Mirroring - Always On Availability

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.