none
SQL Server 2005 Unable to load user-specified certificate

    Question

  • Hi,

    I'm troubleshooting a SQL Server 2005 in Windows Server 2008R2 which cannot be started after a reboot and the need to reboot is due to applying MS security patches.

    I traced back that 2 weeks ago, the cert on the machine was changed but there's no reboot.

    The SSCM showed that Force Encryption was ON but the Certificate tab showed nothing. I set it to OFF and reboot. This should generate a fresh self-signed cert but it still showed the below errors :

    17:00:27.83 Server      Error: 26014, Severity: 16, State: 1.
    17:00:27.83 Server      Unable to load user-specified certificate. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.
    17:00:28.00 Server      Error: 17182, Severity: 16, State: 1.
    17:00:28.00 Server      TDSSNIClient initialization failed with error 0x80092004, status code 0x80.
    17:00:28.00 Server      Error: 17182, Severity: 16, State: 1.
    17:00:28.00 Server      TDSSNIClient initialization failed with error 0x80092004, status code 0x1.
    17:00:28.00 Server      Error: 17826, Severity: 18, State: 3.
    17:00:28.00 Server      Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
    17:00:28.00 Server      Error: 17120, Severity: 16, State: 1.
    17:00:28.00 Server      SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

    From its last good log, "The certificate was successfully loaded for encryption." was there. Is this is self-signed cert loaded or SSL cert from the system ?

    The services are running using LocalSystem. I had tried to change to a local windows account but it'll prompt "WMI Provider Error"  - Cannot find object or property. [0x80092004] " However, SQL still couldn't start up.

    Also checked that the registry has SSL3. No TLS though.

    I have also checked the registry for curly braces but none.

    HKLM\SOFTWARE\Microsoft\Cryptography\MachineGuid This key should ideally have the GUID of the machine without curly braces, so {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx} becomes xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

    This machine is not joined to domain. It's actually an appliance.

    Below are the patches applied :

    KB890830
    KB4055532
    KB4055269
    KB4056897
    KB4056894
    KB4056568

    Not sure if it's worth removing them. But there's another appliance which has the same setup for DR purpose also had the same set of patches applied. Except its cert is not touched.

    Can anyone share some light on this please ? I believe the last option will be to restore the full system to the time before it reboot or to re-install the SQL Server.

    TIA !

    Wednesday, February 07, 2018 3:44 AM

Answers

  • Hi limssd,

    >> Although the cert appears at the MMC, it's not shown at SSCM. However, "The certificate was successfully loaded for encryption." can be seen from the log. Is there any parameter or utility that directs SQL Server to load the certificate from a known path ?

    The certificate not shown in the SSCM can be caused by that the certificate cannot meet all requirements of SQL Server, but this does not mean that it cannot be used, please refer to this: https://stackoverflow.com/questions/36817627/ssl-certificate-missing-from-dropdown-in-sql-server-configuration-manager

    >>Is SHA256 SSL certificate supported for SQL Server 2005 ? From the BOL, I can only see "The level of encryption used by SSL, 40-bit or 128-bit, depends on the version of the Microsoft Windows operating system that is running on the application and database computers." Does that mean only SHA1 is supported ?

    We can understand it as only SHA1 is supported. Because SQL Server 2005 is out of support, it looks like that we cannot get a document which discussed about this.

    Best Regards,

    Teige


    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 limssd Wednesday, February 14, 2018 3:15 AM
    Tuesday, February 13, 2018 8:11 AM
    Moderator

All replies

  • Refer following link https://blog.sqlauthority.com/2016/12/09/sql-server-error-26014-unable-load-user-specified-certificate/

    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Wednesday, February 07, 2018 6:50 AM
  • The SSCM, Certificate tab showed nothing.

    From its last good log, "The certificate was successfully loaded for encryption." was there.

    Is this a self-signed cert loaded or SSL cert from the system ?

    Wednesday, February 07, 2018 7:29 AM
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL<Version>.<InstanceID>\MSSQLServer\SuperSocketNetLib

    The above only showed tcp & np. That's all !

    Wednesday, February 07, 2018 7:30 AM
  • Hi limssd,

    This certificate refers to a self-generated certificate used as a “best-effort” mechanism to protect the SQL login information (including password) when using SQL authentication. 

    Please refer to this article to troubleshooting it: https://thesqldude.com/2011/08/03/sql-server-service-does-not-start-after-enabling-ssl-encryption/

    Besides, because SQL Server 2005 has been out of support, I suggest you using later version of SQL Server and migrate date to it.

    Best Regards,

    Teige


    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, February 09, 2018 9:00 AM
    Moderator
  • I had tried all the steps from SQLdude but they didn't work for my issue.

    Anyway, the application support team replaced a configuration folder from the DR server and regenerated the cert. Wala ! SQL Service started !

    I've a few questions:

    1. Although the cert appears at the MMC, it's not shown at SSCM. However, "The certificate was successfully loaded for encryption." can be seen from the log. Is there any parameter or utility that directs SQL Server to load the certificate from a known path ?
    2. Is SHA256 SSL certificate supported for SQL Server 2005 ? From the BOL, I can only see "The level of encryption used by SSL, 40-bit or 128-bit, depends on the version of the Microsoft Windows operating system that is running on the application and database computers." Does that mean only SHA1 is supported ?

    Monday, February 12, 2018 6:57 AM
  • Hi limssd,

    >> Although the cert appears at the MMC, it's not shown at SSCM. However, "The certificate was successfully loaded for encryption." can be seen from the log. Is there any parameter or utility that directs SQL Server to load the certificate from a known path ?

    The certificate not shown in the SSCM can be caused by that the certificate cannot meet all requirements of SQL Server, but this does not mean that it cannot be used, please refer to this: https://stackoverflow.com/questions/36817627/ssl-certificate-missing-from-dropdown-in-sql-server-configuration-manager

    >>Is SHA256 SSL certificate supported for SQL Server 2005 ? From the BOL, I can only see "The level of encryption used by SSL, 40-bit or 128-bit, depends on the version of the Microsoft Windows operating system that is running on the application and database computers." Does that mean only SHA1 is supported ?

    We can understand it as only SHA1 is supported. Because SQL Server 2005 is out of support, it looks like that we cannot get a document which discussed about this.

    Best Regards,

    Teige


    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 limssd Wednesday, February 14, 2018 3:15 AM
    Tuesday, February 13, 2018 8:11 AM
    Moderator