SQL SERVER 2008 R2 not able to come online – Initializing the FallBack certificate failed

While applying a service pack on SQL Server 2008 R2, after server reboot, SQL service were unable to start and following error noted in SQL Error log,

———–

Error: 17190, Severity: 16, State: 1.

Initializing the FallBack certificate failed with error code: 1, state: 1, error number: -2146893788.

Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.

 

Error: 17182, Severity: 16, State: 1.

TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.

 

Error: 17182, Severity: 16, State: 1.

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.

 

Error: 17826, Severity: 18, State: 3.

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.

 

Error: 17120, Severity: 16, State: 1.

SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

————–

 

The issue was not linked with service pack, but it looked more like certificate error, on further investigation found that this happens because of user profile corruption of SQL Service domain account.

 

After initial investigation found a useful link on msdn blogs, http://blogs.msdn.com/b/sqljourney/archive/2012/10/09/10357697.aspx and issue found to be same i.e. SQL service domain account profile corruption; Do not use the solution provided on above mentioned msdn blog to fix profile corruption on a server, use following MS KB article which has three methods to fix profile corruption issue, http://support.microsoft.com/kb/947215 , you may have to reboot server to bring the changes into effect.

 

Please find below the steps I used to fix my issue,

 

SQL was unable to come online and to ensure it is a profile corruption issue, go to Windows event logs and filter for Event ID: 1511, in my case there was entry of it just after my activity got completed,

—————-

Log Name: Application

Source: Microsoft-Windows-User Profiles Service

Date: 8/4/2013 12:38:17 AM

Event ID: 1511

Task Category: None

Level: Error

Keywords:

User: <domain>/<accountname>

Computer: <servername>.<domain>.com

Description:

Windows cannot find the local profile and is logging you on with a temporary profile. Changes you make to this profile will be lost when you log off.

—————-

 

Now to fix it, I followed METHOD 1 described in following MS KB article
http://support.microsoft.com/kb/947215

 

First login with any other administrative account other than for which profile got corrupt and follow the steps below,

 

Note: Before doing any changes to registries, please backup all registries using registry export functionality and remember if something wrongly done then it may crash your installation too which require you to reinstall SQL Server, so change options wisely and only if similar situation occurs to you, refer KB article link shared above for further details.

 

To fix the user account profile, follow these steps:

(http://support.microsoft.com/kb/322756/ )

• Click Start, type regedit in the Search box, and then press ENTER.
  
• In Registry Editor, locate and then click the following registry subkey:
  

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList
  

• In the navigation pane, locate the folder that begins with S-1-5 (SID key) followed by a long number.
  
• Click each S-1-5 folder, locate the ProfileImagePath entry in the details pane, and then double-click to make sure that this is the user account profile that has the error.
  

  Collapse this imageExpand this image
  

  
  

   

  • If you have two folders starting with S-1-5 followed by some long numbers and one of them ended with .bak, you have to rename the .bak folder. To do this, follow these steps:
    
    • Right-click the folder without .bak, and then click Rename. Type .ba, and then press ENTER.
      

      Collapse this imageExpand this image
      

      
      

    • Right-click the folder that is named .bak, and then click Rename. Remove .bak at the end of the folder name, and then press ENTER.
      

      Collapse this imageExpand this image
      

      
      

    • Right-click the folder that is named .ba, and then click Rename. Change the .ba to .bak at the end of the folder name, and then press ENTER.
      

      Collapse this imageExpand this image
      

      
      

  • If you have only one folder starting with S-1-5 that is followed by long numbers and ends with .bak. Right-click the folder, and then click Rename. Remove .bak at the end of the folder name, and then press ENTER.
    

     

• Double-click the folder without .bak in the details pane, double-click RefCount, type 0, and then click OK.
  

  Collapse this imageExpand this image
  

  
  

• Click the folder without .bak, in the details pane, double-click State, type 0, and then click OK.
  

  Collapse this imageExpand this image
  

  
  

• Close Registry Editor.
  
• Restart the computer.
  
• Log on again with your account.
  

 

 

Once registry keys are updated, we were able to bring the sql server online. Hope it helps.

 

If above method doesn’t fix the issue then there are two more methods provided by Microsoft in same KB article (http://support.microsoft.com/kb/947215) please follow them.

 

Other useful references:

1. If your issue is more complicated then there is slight different approach to fix on Karthick blog: http://mssqlwiki.com/2012/04/19/sqlserver-initializing-the-fallback-certificate-failed-with-error-code-1-state-1-error-number-2146893802/

 

2. The issue might also be linked with protocols as suggested at this link: http://blogs.msdn.com/b/sql_protocols/archive/2005/10/31/487090.aspx?PageIndex=4

3. Reference to profile corruption issue: http://social.technet.microsoft.com/Forums/en-US/itproxpsp/thread/f7231510-d4f2-435f-9fbc-37ff68f5eb39