SQL SERVER – Startup Error: 17190, Severity: 16 – Initializing the FallBack Certificate Failed with Error Code: 1, State: 20

I must confess that consulting has taught me much more than what I have learned in my regular job. Freelancing comes with advantages and disadvantages. For me, I am looking at advantages and I am happy with my decision. In this blog post we will learn about error related to FallBack Certificate.

One of my clients contacted me for On-Demand consulting and we quickly jumped on the call to find the issue and fix it. As you might know, every single day I keep two slots available for On Demand (50 minutes). When I joined the call, they informed me that they are having trouble starting a SQL Service and their critical business application is down. The configuration manager showed below.

The first thing I always check is ERRORLOG and here is what I found there.

2017-10-13 11:53:31.36 spid18s Error: 17190, Severity: 16, State: 1.
2017-10-13 11:53:31.36 spid18s Initializing the FallBack certificate failed with error code: 1, state: 20, error number: 0.
2017-10-13 11:53:31.36 spid18s Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
2017-10-13 11:53:31.36 spid18s Error: 17182, Severity: 16, State: 1.
2017-10-13 11:53:31.36 spid18s TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
2017-10-13 11:53:31.36 spid18s Error: 17182, Severity: 16, State: 1.
2017-10-13 11:53:31.36 spid18s 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.
2017-10-13 11:53:31.36 spid18s Error: 17826, Severity: 18, State: 3.
2017-10-13 11:53:31.36 spid18s 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.
2017-10-13 11:53:31.36 spid18s Error: 17120, Severity: 16, State: 1.
2017-10-13 11:53:31.36 spid18s SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

I search on the internet and found many blogs having a wealth of information based on the error message. It looks like SQL Server generates a self-signed certificate by default for encryption. When I searched for 0x80092004 I could find below on Microsoft site.

The value 0x80092004 is an SSPI error code that translates to CRYPT_E_NOT_FOUND. This error was generated by SSL because it could not locate the certificate. Here are the possible causes

• SQL Server Startup account (service account) does not have permission or does not have a local profile in the system.
• The SQL server startup account cannot access a pre-existing key container needed to create the self-signed certificate.

Here are various options which can be tried.

1. Checked if SQL Startup account has FULL CONTROL to the folder %userprofile%\AppData\Roaming\Microsoft\Crypto\RSA\ S-1-5-21-xxxxxx-xxxxx-xxxx-xxxxx (last part of SID of the service account and user is service account)
2. Try different service account to see if the issue relates to domain account?
3. Are we getting TEMP profiles when we are logged in to the server? If that is still the case, then that might be the cause of the issue because certificates are not supported in TEMP profiles.  To ensure that these accounts are logged on with a normal profile, there is some information that would need to be cleaned up.

(A temporary profile is loaded after you log on to a Windows Vista-based system)

SOLUTION/WORKAROUND

We found that cleaning up the profile information has resolved the issue on this server for my client.

Have you ever encountered such issues? Can you please share the solution with others via comment?

Reference: Pinal Dave (https://blog.sqlauthority.com)