0

Despite correct installation/configuration (in my opinion) the query ...

SELECT DISTINCT [net_transport]
    , [protocol_type]
    , [encrypt_option]
    , [auth_scheme]
  FROM [master].[sys].[dm_exec_connections];

... returns FALSE in the [encrypt_option] column for the protocol type "Database Mirroring":

screenshot query results

I have successfully ...

  • installed and configured 2 SQL Server instances (SQL Server 2019 Enterprise Edition),
  • configured "Force Encryption" to "Yes" in the protocol properties (SQL Server Configuration Manager),
  • set up the required endpoints with the ENCRYPTION = REQUIRED ALGORITHM AES option and
  • set up an availability group.

The instance uses a self-generated certificate that is created during startup. However, I have the same picture in another cluster where explicitly generated certificates (AD) are used.

What have I possibly missed?

I found a similar, but also still unanswered question here: https://www.sqlservercentral.com/forums/topic/unencrypted-connections-in-availability-group

1 Answer 1

1

... returns FALSE in the [encrypt_option] column for the protocol type "Database Mirroring"

Unfortunately it's a limitation of the DMV, this doesn't mean the connection isn't encrypted. You can verify this with a packet capture.

2
  • Phew, thanks! I didn't really want to believe in a bug or limitation. Is this limitation documented somewhere (SQLDocs, ...)?
    – D.C.
    Commented Aug 29, 2023 at 13:42
  • It is not. I was going to update the DMV to show status of other endpoint types but I left the team before I was able to do so. Commented Aug 29, 2023 at 13:50

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.