Unknown database connections



  • 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



  • 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: https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/transport-security-database-mirroring-always-on-availability?view=sql-server-ver15




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2