locked
Enable SSL Connection encryption for Always-on RRS feed

  • Question

  • Anyone has a type to implement SSL connection encryption to Always-on AG ?

    Like what cert need to be create/request from CA ?  Steps to request the cert which has FQDN of the listener ?

    Friday, March 13, 2020 4:09 AM

Answers

  • Hi sakurai_db,

    >>May I know if I can issue a cert to CN=aglist.domain.com with SAN DNS=Node1.domain.com DNS=Node2.domain.com import this cert to each node. And also copy the  Thumbprint of this cert to the key.

    Yes. In a clustered environment, this key will be set to Null even though the correct certificate exists in the store.  

    >>and on client side, just like SSMS , uncheck Encrypt Connection. …. How to ensure that connection is using the certificate to encrypt the connection ……???

    When the Force Encryption option for the Database Engine is set to YES, all communications between client and server is encrypted no matter whether the “Encrypt connection” option (such as from SSMS) is checked or not. You can check it using below T-SQL.

    -- To check whether connections are encrypted between server and clients
    USE master
    GO
    SELECT encrypt_option FROM sys.dm_exec_connections
    GO

    Best regards,
    Cathy 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Monday, March 16, 2020 9:29 AM
  • Hi sakurai_db,

    >> so do I need to set the thumbprint of this cert explicitly to the reg key ??

    You need to do it only in SQL Server clustered environment.  Please refer to the part of Enable a certificate for SSL on a SQL Server clustered installation in this MS document. Or please refer to the last part of the blog.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by sakurai_db Wednesday, March 18, 2020 4:24 AM
    Tuesday, March 17, 2020 6:52 AM

All replies

  • anyone has the idea ?
    Friday, March 13, 2020 6:25 AM
  • Hi sakurai_db,

    We need to configured both SQL nodes in our AG cluster to be SSL enabled. Please follow the steps from below links.

    SQL 2014 AlwaysOn AG – SSL
    Availability Group Listeners and SSL Certificates

    Hope they could help you.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, March 13, 2020 9:11 AM
  • If you would like to use encrypted connections in a clustered environment then you should have a certificate issued to the fully qualified DNS name of the failover clustered instance and this certificate should be installed on all of the nodes in the failover cluster. Additionally, you will have to edit the thumbprint of the certificate in the registry because it is set to Null in clustered environment.
    Advertisement

    The following steps should be performed on all of the nodes in the cluster:

    1. Navigate to the certificate in the MMC Certificates Snap-in and double click to open the certificate.
    2. Copy the hex value from the Thumbprint property on the Details tab to Notepad and remove the spaces.
    3. Start Regedit and copy the hex value to this key: HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<YourSQLServerInstance>\MSSQLServer\SuperSocketNetLib\Certificate
    4. You will have to reboot your node, so it is recommended to failover to another node first.

       
    Friday, March 13, 2020 9:27 AM
  • May I know if I can issue a cert to CN=aglist.domain.com

    with SAN

    DNS=Node1.domain.com

    DNS=Node2.domain.com

    import this cert to each node. And also copy the  Thumbprint of this cert to the key.

    ???

    And I have another concern. If from SQL server manager we have set Force Encryption .

    and on client side, just like SSMS , uncheck Encrypt Connection. …. How to ensure that connection is using the certificate to encrypt the connection ……???

    Friday, March 13, 2020 11:38 AM
  • Hi sakurai_db,

    >>May I know if I can issue a cert to CN=aglist.domain.com with SAN DNS=Node1.domain.com DNS=Node2.domain.com import this cert to each node. And also copy the  Thumbprint of this cert to the key.

    Yes. In a clustered environment, this key will be set to Null even though the correct certificate exists in the store.  

    >>and on client side, just like SSMS , uncheck Encrypt Connection. …. How to ensure that connection is using the certificate to encrypt the connection ……???

    When the Force Encryption option for the Database Engine is set to YES, all communications between client and server is encrypted no matter whether the “Encrypt connection” option (such as from SSMS) is checked or not. You can check it using below T-SQL.

    -- To check whether connections are encrypted between server and clients
    USE master
    GO
    SELECT encrypt_option FROM sys.dm_exec_connections
    GO

    Best regards,
    Cathy 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Monday, March 16, 2020 9:29 AM
  • Yes. In a clustered environment, this key will be set to Null even though the correct certificate exists in the store.  

    so do I need to set the thumbprint of this cert explicitly to the reg key ??
    Monday, March 16, 2020 10:36 AM
  • Hi sakurai_db,

    >> so do I need to set the thumbprint of this cert explicitly to the reg key ??

    You need to do it only in SQL Server clustered environment.  Please refer to the part of Enable a certificate for SSL on a SQL Server clustered installation in this MS document. Or please refer to the last part of the blog.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by sakurai_db Wednesday, March 18, 2020 4:24 AM
    Tuesday, March 17, 2020 6:52 AM