none
SQL 2008 Error - TDSSNIClient initialization failed with error 0x80092004 RRS feed

  • Question

  • I'm getting this error in our SQLDEV

    SQL 2008 enterrpise R2

    2013-06-15 10:33:16.11 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    2013-06-15 10:33:16.11 Server      Detected 2 CPUs. This is an informational message; no user action is required.
    2013-06-15 10:33:16.16 Server      Using locked pages for buffer pool.
    2013-06-15 10:33:16.18 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
    2013-06-15 10:33:16.20 Server      Node configuration: node 0: CPU mask: 0x0000000000000003:0 Active CPU mask: 0x0000000000000003:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
    2013-06-15 10:33:16.23 spid7s      Starting up database 'master'.
    2013-06-15 10:33:16.88 spid7s      Resource governor reconfiguration succeeded.
    2013-06-15 10:33:16.88 spid7s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.
    2013-06-15 10:33:16.88 spid7s      SQL Server Audit has started the audits. This is an informational message. No user action is required.
    2013-06-15 10:33:16.88 spid7s      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
    2013-06-15 10:33:16.90 spid7s      SQL Trace ID 1 was started by login "sa".
    2013-06-15 10:33:16.90 spid7s      Starting up database 'mssqlsystemresource'.
    2013-06-15 10:33:16.94 spid7s      The resource database build version is 10.50.4000. This is an informational message only. No user action is required.
    2013-06-15 10:33:17.23 Server      The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030e. Check certificates to make sure they are valid.
    2013-06-15 10:33:17.23 spid10s     Starting up database 'model'.
    2013-06-15 10:33:17.23 spid7s      Server name is 'OPMGR'. This is an informational message only. No user action is required.
    2013-06-15 10:33:17.23 Server      Error: 26014, Severity: 16, State: 1.
    2013-06-15 10:33:17.23 Server      Unable to load user-specified certificate [Cert Hash(sha1) "8A77D152B5410F364E43F5D27F15A13E50B087C6"]. 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.
    2013-06-15 10:33:17.23 Server      Error: 17182, Severity: 16, State: 1.
    2013-06-15 10:33:17.23 Server      TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.

    2013-06-15 10:33:17.23 Server      Error: 17182, Severity: 16, State: 1.
    2013-06-15 10:33:17.23 Server      TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.

    2013-06-15 10:33:17.23 Server      Error: 17826, Severity: 18, State: 3.
    2013-06-15 10:33:17.23 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.
    2013-06-15 10:33:17.23 Server      Error: 17120, Severity: 16, State: 1.
    2013-06-15 10:33:17.23 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.

    Saturday, June 15, 2013 11:04 AM

All replies

  • Hello,

    The error is due to certificate which is loaded in SQL Server.

    Copy the thumbprint value of the certificate in registry at:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.50.instancename\MSSQLServer\SuperSocketNetLib\certificate

    To get the thumbprint of the certificate , load the certificate through mmc console.

    make sure that when you load the thumbprint in registry, remove all the spaces and make sure that its valid.

    Wednesday, October 2, 2013 4:46 PM
  • The information you provide may be correct but it is difficult to decipher.  The failure occurs when the certificate is added for TDE.   After the machine reboots, the local account (nt service\mssql$instancename) causes the error.  Removing the certificate from the protocol and then adding it back in, re-enables the service to start up.

    Your instruction says to copy the thumbprint from the mmc.   As it is listed in UNICODE, the instructions to remove all the spaces made sense however, the code there, matches the code that is already in the registry.

    Is it possible you left out a step?   In general, there seems to be a good deal of to'ing and fro'ing regarding certificates and their export to DER encoding or to BASE64 encoding - is there something that has to happen here as well?  Does the certificate require export and reimport to another store in the certificate store?

    The certificate was created as an FDQN to allow it to be imported through the protocol.   As this worked, it comes as unexpected that now the certificate is preventing the service from starting.

    Don, to restate this question (with your permission "help.me"), is there a step missing in the instruction above?

    I found this for an older version of SQL (2008 R2)

    https://msdn.microsoft.com/en-us/library/ms186362(v=sql.105).aspx


    R, J

    Tuesday, August 23, 2016 1:59 PM
  • Did you tried to follow the link that you found?

    It's seems to me in first glance like the relevant instructions to your issue

    * I actually intended to post this link as an option to solution, but then I notice that you already found it :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Tuesday, October 4, 2016 4:43 PM
    Moderator
  • The instructions here do work to some extent.   To meet the requirement that the symmetric key is protected by a "official" certificate, if I understand the context correctly (which is part of the problem), what has to happen is that the "official" certificate must be approved for a "purpose" that allows the private key to be created (.pvk) from the .cer key.   The .cer key must be exported to a DER with the private key so that the makecert can run.  Afterward, the pvk2pfx can create the .cer, .pfx, and .pfx (generally used for code signing) but adequate to be registered using SQL commands in the SSMS.

      
    • Export the "official" certificate (if purposed for this) to a DER Encoded Password protected CA (.cer)
    • Use the makecert utility to create a new .cer, .pvk based on the "official" certificate (Makecert is a VS tool)
    • Download the PVKConverter for SQL Server and convert the .pvk to a .pfx (https://www.microsoft.com/en-us/download/details.aspx?id=40812)
    • In SSMS, if not already done, create the Service Master Key (SMK)
    • Create the Asymmetric Key from a .cer file using a .pvk (your makecert and pvkconverter tools create these)
    • Backup the Certificate - if you skip this step you'll get warnings - you should heed the warnings and backup the certificate.
    • Switch from the master to the database you need a symmetric key to be used for encryption and create the Database Encryption Key with the Asymmetric Certificate as created above
    • Set Encryption for that database "ON"

    If there is an error "Cannot write into file...", it means that your service account, not you as the creator, cannot "write into" the backup file.   It can be confusing if your service account or local virtual service (nt service\mssql$instancename) is not permissioned as an administrator (and it shouldn't be).

    This process worked on one domain and there were issues in another.  It may be the issue had to do with the way the "official" certificate was purposed.  The one "official" (meaning signed by an authority) was purposed only as an identity linked to a login and this complexity confuses me.   Fortunately I have more options on that domain than on the one that was successful.

    Hope this helps someone.

    


    R, J

    • Proposed as answer by Crakdkorn Tuesday, October 18, 2016 12:51 PM
    Wednesday, October 5, 2016 12:59 PM