none
Forced Encryption with SHA256

    Question

  • Hello,

    We currently use SHA1 type certs to force encryption and would like to start using SHA256 certs instead. From SQL Server Configuration Manager, SQL Server Network Configuration, we usually right click on the Protocol for the instance and select properties and change Force Encryption to Yes and followed by select the certificate we would like to use.

    Issue is, the new SHA256 certificate we have issued is not visible for selection, even though this is in certificates console.

    We are Running SQL Server 2014 on the server (version 12.0.4457.0).

    Is there a guide or a process which is different to SHA1 I need to follow to make the change successfully?

    Wednesday, February 8, 2017 2:53 PM

Answers

  • Hi Latif Yahya,

    >>We are Running SQL Server 2014 on the server (version 12.0.4457.0).

    Based on my test,even though SSCM doesn’t recognize SHA256 certificate, you could still manage to let SQL Server instance use it by replace cert thumb print in registry. Here’s the registry path:
    ##Force encryption setting set to 1
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.INSTANCENAME\MSSQLServer\SuperSocketNetLib\ ForceEncryption
    ##Replace the value with SHA256 cert thumbprint
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.INSTANCENAME\MSSQLServer\SuperSocketNetLib\ Certificate

    If you have any other questions, please let me know.

    Regards,
    Lin

    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, February 9, 2017 3:28 AM
    Moderator
  • Hi Latif Yahya,

    >>We are Running SQL Server 2014 on the server (version 12.0.4457.0).

    Based on my test,even though SSCM doesn’t recognize SHA256 certificate, you could still manage to let SQL Server instance use it by replace cert thumb print in registry. Here’s the registry path:
    ##Force encryption setting set to 1
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.INSTANCENAME\MSSQLServer\SuperSocketNetLib\ ForceEncryption
    ##Replace the value with SHA256 cert thumbprint
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.INSTANCENAME\MSSQLServer\SuperSocketNetLib\ Certificate

    If you have any other questions, please let me know.

    Regards,
    Lin

    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.

    Thank you Lin.

    I will test this and let you know.


    • Marked as answer by Latif Yahya Friday, March 17, 2017 12:56 PM
    Thursday, February 9, 2017 4:04 PM

All replies

  • Did you add the certificate to your local store?

    Wednesday, February 8, 2017 3:15 PM
  • I have indeed and can clearly see the cert in the local store.

    Intended Purposes, Friendly Name are same as the SHA1 cert as I went thought the troubleshooting guide at the bottom of this link.

    Wednesday, February 8, 2017 4:08 PM
  •  I have been trying myself. I can only get Sha1 working as well.
    Wednesday, February 8, 2017 4:09 PM
  • Hi Latif Yahya,

    >>We are Running SQL Server 2014 on the server (version 12.0.4457.0).

    Based on my test,even though SSCM doesn’t recognize SHA256 certificate, you could still manage to let SQL Server instance use it by replace cert thumb print in registry. Here’s the registry path:
    ##Force encryption setting set to 1
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.INSTANCENAME\MSSQLServer\SuperSocketNetLib\ ForceEncryption
    ##Replace the value with SHA256 cert thumbprint
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.INSTANCENAME\MSSQLServer\SuperSocketNetLib\ Certificate

    If you have any other questions, please let me know.

    Regards,
    Lin

    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, February 9, 2017 3:28 AM
    Moderator
  • Hi Latif Yahya,

    >>We are Running SQL Server 2014 on the server (version 12.0.4457.0).

    Based on my test,even though SSCM doesn’t recognize SHA256 certificate, you could still manage to let SQL Server instance use it by replace cert thumb print in registry. Here’s the registry path:
    ##Force encryption setting set to 1
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.INSTANCENAME\MSSQLServer\SuperSocketNetLib\ ForceEncryption
    ##Replace the value with SHA256 cert thumbprint
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.INSTANCENAME\MSSQLServer\SuperSocketNetLib\ Certificate

    If you have any other questions, please let me know.

    Regards,
    Lin

    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.

    Thank you Lin.

    I will test this and let you know.


    • Marked as answer by Latif Yahya Friday, March 17, 2017 12:56 PM
    Thursday, February 9, 2017 4:04 PM
  • Hi Latif Yahya,

    I’m writing to follow up with you on this post. Was the issue resolved? If the issue has been resolved, please mark the corresponding replies as answer as it would benefit others when they are reading this thread. If not, could you please provide more information so we can have a better understanding about the issue?

    If you have any other questions, please let me know.

    Regards,
    Lin

    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, February 20, 2017 9:17 AM
    Moderator
  • Hello, Our DBA's have tested this and confirmed as working!

    Below is the query they used for testing.

    USE master

    GO

    SELECT encrypt_option, * FROM sys.dm_exec_connections

    GO


    • Edited by Latif Yahya Friday, March 17, 2017 12:58 PM confirmation that this is working
    Friday, March 17, 2017 12:40 PM