locked
Connections still show as encrypted after certificate key expires RRS feed

  • Question

  • I have to start by saying I do not fully understand certificates, keys, and the issuance/renewal of them, so please forgive any simplistic and/or incorrect terminology/understandings of mechanics. I have been testing using a certificate to encrypt connections for SQL Server, and I am not clear how things are handled when a cert auto-renews.

    I have gone through the steps of importing a cert, grant read permissions on the cert to the SQL engine service account, and successfully select the certificate via SQL Config Mgr and force encryption. After restarting the SQL engine service, querying dm_exec_connections shows [encrypt_option] values of TRUE for all connections.

    The confusion comes in when the cert renews (we have it set to auto-renew every 4 hours). In MMC -> Certificates I end up seeing a different thumbprint for the cert, which makes sense since it was renewed. But when I now look in SQL Config Mgr, the cert is no longer selected in the "Certificate" drop-down (but it is present for selection if I expand the drop-down). Also, if I look at the registry key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer\SuperSocketNetLib", the value for "Certificate" does not match the thumbprint in MMC. I also see the new key in C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys. Despite all this, connections to SQL remain encrypted ([encrypt_option] = TRUE), even with a restart of the SQL engine.

    It seems as though SQL never stops using the older, expired key for the cert. In fact, if I go ahead and select the cert again in SQL Config Mgr, and try to restart the service, it fails to start because of the lack of read permissions on the new key. Is this expected behavior? Are there long term risks to things failing if SQL is just left using the old key? Is there a way for SQL to auto-magically use the new key, or is this just another maintenance routine one needs to handle if you want it to use the latest incarnation of the cert? Any insight is much appreciated.

    Thanks!


    • Edited by EBond Tuesday, July 24, 2018 12:52 PM
    Tuesday, July 24, 2018 12:51 PM

All replies

  • Hi Ebond,

     

    From your description, my understanding is that you want to know if SQL Server will auto-use the new cert for connection encryption. If anything is misunderstand, please tell me.

     

    As yet, SQL Server couldn't use the new cert automatically. If you actually need to use the new cert, you could set it manually in SSCM and then grant the read permission. By the way, we need to pay attention that after the cert is configured successfully, the cert will work when restarting the SQL Server. As you described that you need cert to be set to auto-renew every four hours, even though you could configure the cert manually every four hours, in production environment restarting SQL Server every four hours is not a good idea.

     

    Best Regards,

    Emily


    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

    Thursday, July 26, 2018 6:22 AM
  • Emily,

    Thank you for the reply. Thank you for confirming that the renewed cert requires configuration to be used.

    The bigger question I have though, is whether it is expected behavior for SQL Server to successfully continue using the expired key? I would not want to count on SQL using the expired key indefinitely if there is some sort of "check" the SQL does and eventually will simply not start due to the expiration/renewal. Does that make sense?

    The 4 hour expiration was just for testing, so we could see what happens at expiration/renewal without having to wait long periods of time. We would certainly use an extended period for production, and plan on maintenance windows which allow for the restart of the SQL service, in which the switch to renewed certs could be made. Just want to be sure that we don't have any issues if there is some period of time in between expiratin/renewal and that manual process

    Thanks!

    Thursday, July 26, 2018 12:20 PM
  • Hi Ebond,

     

    SQL Server will search for the thumbprint value in the registry path "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Certificate" to confirm the certificate which is used in the connection. If SQL Server can't locate the certificate in the certificate store via the thumbprint value, it will fail to start. Theoretically, SQL Server is not able to use the expired key in the connection successfully.

     

    Best Regards,

    Emily


    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, July 27, 2018 5:59 PM
  • Emily,

    Thank you again for the response. If I understand you correctly: once the certificate is expired, SQL Server should no longer be able to use it to encrypt traffic. This is despite the fact that it does in fact use an expired certificate, and even survives a restart of the service and continues to do so?

    Something else I've discovered is that the expired certs can be seen in the MMC -> Certificates node if you check the "Archived certificates" View Option. When I do so, I see the expired cert that SQL continues to use successfully to encrypt traffic.

    Is there any Microsoft SQL documentation that addresses how SQL Server, specifically when set to "Force Encryption", handles a cert expiring yet the expired cert still being available? Or guidance on how to handle expiration and/or renewal of certs? Certainly entities with large numbers of SQL instances using encryption are not manually handling cert renewals?

    Thanks!

    Monday, July 30, 2018 1:22 PM
  • Hi Ebond,

     

    SQL Server can't change the cert automatically. All setting about certs stays until you change them manually. Since the expired certs were still existing in your machine. And you didn't change the setting of cert manually in SSCM. So SQL Server could still locate the cert and use it. As for some more detailed information about expiration/renewal of certs, I would suggest you asking Windows specialist for more professional help.

     

    Best Regards,

    Emily


    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

    Thursday, August 2, 2018 4:07 PM