Create ConfigMgr SQL Server Identification Certificate

1142

Few days back while working on implementing PKI for SCCM, I encountered an issue related to SQL server certificate. To identify the root cause of this issue, it took some time but at the end it was identified. We encountered this issue in the production environment. While cleaning up the unwanted certs from the SCCM server, one of my associate accidentally deleted ConfigMgr SQL Server Identification Certificate. After sometime we noticed that the ConfigMgr console was unable to connect to the site database. The SQL Server, SQL Server reporting services and SQL server agent services were stopped automatically. After checking the event viewer logs I checked certificate store on the machine and noticed that the SQL Server identification certificate was missing. In this post I will show you how create ConfigMgr SQL Server Identification Certificate. I have simulated the same issue in my lab setup and I am going to show you how to fix that.

Upon checking the event viewer logs I found Report Server (MSSQLSERVER) cannot connect to the report server database.

2016-06-06 21:41:30.51 spid10s     Unable to load user-specified certificate [Cert Hash(sha1) “BCDDA9DF02E4091B6865B15394DF5FA0F1D9ABF3”]. 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.
2016-06-06 21:41:30.51 spid10s     Error: 17182, Severity: 16, State: 1.
2016-06-06 21:41:30.51 spid10s     TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
2016-06-06 21:41:30.52 spid10s     Error: 17182, Severity: 16, State: 1.
2016-06-06 21:41:30.52 spid10s     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.
2016-06-06 21:41:30.52 spid10s     Error: 17826, Severity: 18, State: 3.
2016-06-06 21:41:30.52 spid10s     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.
2016-06-06 21:41:30.52 spid10s     Error: 17120, Severity: 16, State: 1.
2016-06-06 21:41:30.52 spid10s     SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

What gave me a hint of missing certificate was this error line “The server will not accept a connection. You should verify that the certificate is correctly installed”.

Create ConfigMgr SQL Server Identification Certificate

When I tried starting the SQL server & SQL server agent service, I got the below error.

Create ConfigMgr SQL Server Identification Certificate

Create ConfigMgr SQL Server Identification Certificate

To create ConfigMgr SQL Server Identification Certificate, open the IIS management console. Select the server then double click Server Certificates.

Create ConfigMgr SQL Server Identification Certificate

Right click on the right pane and click Create Self-Signed Certificate.

Create ConfigMgr SQL Server Identification Certificate

Provide a friendly name to this cert. Choose the certificate to be stored in Personal store. Click OK.

Create ConfigMgr SQL Server Identification Certificate

You can now see that the new certificate is created.

Create ConfigMgr SQL Server Identification Certificate

Launch the SQL Server Configuration Manager, expand SQL Server Network Configuration, right click Protocols for MSSQLSERVER and click Properties.

Create ConfigMgr SQL Server Identification Certificate

Select the Certificate tab and use the drop down to select the self-signed certificate you created. You can double check this by making sure the Issued By field matches the server name. Click OK.

Create ConfigMgr SQL Server Identification Certificate

Right click on SQL server services and start them.

Create ConfigMgr SQL Server Identification Certificate

Congratulations you have fixed this issue very neatly 🙂

  • Santhosh

    Hello Prajwal,

    we migrated the SCCM SQL DB to another server and when i tried to do a site maintenance i got error as below

    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: The certificate chain was issued by an authority that is not trusted.
    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection
    *** Failed to connect to the SQL Server, connection type: SMS ACCESS.
    > INFO: SQL Connection failed. Connection: SMS ACCESS, Type: Secure
    > ERROR: SQL Connection failed. Connection: SMS ACCESS, Type: Secure
    > ERROR: Failed to get SQL Server connection.

    Can i perform the above steps mentioned by you?

    Regards,
    Santhosh B S

  • Santhosh

    Hello Prajwal,

    I migrated the SCCM SQL DB to another server and when i do site maintenance i get below errors. Can i perform the above steps mentioned by you?

    I have standalone primary server and IIS 6.1 and server 2008 R2.
    Destination SQL is 2012

    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: The certificate chain was issued by an authority that is not trusted.
    *** Failed to connect to the SQL Server, connection type: SMS ACCESS.

    > INFO: SQL Connection failed. Connection: SMS ACCESS, Type: Secure

    > ERROR: SQL Connection failed. Connection: SMS ACCESS, Type: Secure

    > ERROR: Failed to get SQL Server connection.

    Regards,
    Santhosh B S