다음을 통해 공유

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

  • 아티클

Problem Description :

v Unable to start SQL Server 2005 default instance on a standalone server.

v We notice below Error message in the SQL Server Error Log

           

                                2009-07-15 19:03:25.74 spid5s Error: 15466, Severity: 16, State: 1.

                                2009-07-15 19:03:25.74 spid5s An error occurred during decryption.

                                2009-07-15 19:03:25.79 spid5s Server name is 'Server_Name'. This is an informational message only. No user action is required.

                                2009-07-15 19:03:25.79 spid9s Starting up database 'model'.

                                2009-07-15 19:03:25.85 Server Error: 17182, Severity: 16, State: 1.

                                2009-07-15 19:03:25.85 Server TDSSNIClient initialization failed with error 0x2, status code 0x38.

                                2009-07-15 19:03:25.86 Server Error: 17182, Severity: 16, State: 1.

                                2009-07-15 19:03:25.86 Server TDSSNIClient initialization failed with error 0x2, status code 0x1.

                                2009-07-15 19:03:25.86 Server Error: 17826, Severity: 18, State: 3.

2009-07-15 19:03:25.86 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.

                                2009-07-15 19:03:25.86 Server Error: 17120, Severity: 16, State: 1.

        2009-07-15 19:03:25.86 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.

Environment:

PRODUCT VERSION AND SERVICE RELEASES

Windows 2003

Service Pack 2

SQL Version: SQL 2005 Standard Edition

SQL Service Pack: 3

SQL Build: 9.00.4035

Server name is 'RSCSQL'.

SQL Server Startup Account – RSC\sqladmin.

Some Information Understanding server-side protocol initialization error codes

From https://blogs.msdn.com/sql_protocols/archive/2006/01/10/511330.aspx

Troubleshooting Steps:

v Tried starting SQL Server (MSSQLServer) using LOCAL SYSTEM in SCM.

v We created a ‘Certificate’ of type - REG_STRING value in registry - HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\

v Started SQL Server (MSSQLServer), it started Successfully.

v We then reverted to domain\admin account – RSC\sqladmin got WMI PROVIDER ERROR - The system cannot find the file specified (0x80070002) and it failed with message as shown below.

From Application Event Logs

======================

Event Type: Error

Event Source: MSSQLSERVER

Event Category: (2)

Event ID: 17182

Date: 7/15/2009

Time: 9:47:42 PM

User: N/A

Computer: RSCSQL

Description:

TDSSNIClient initialization failed with error 0x80092004, status code 0x80.

Event Type: Information

Event Source: MSSQLSERVER

Event Category: (2)

Event ID: 26017

Date: 7/15/2009

Time: 9:47:42 PM

User: N/A

Computer: RSCSQL

Description:

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

v It did not help in starting SQL server. But whenever we change the account to LOCAL SYSTEM in SCM we are able to start SQL Server.

v To determine the provider of a certificate, we ran the following (Reference - KB 928779) command at a command prompt: certutil -v -store my

                       Output: CertUtil: -store command completed successfully.

          

## Did a small Repro at my end ##

v We wanted to test out if we are able to encrypt a small text file. So for that we created a Test.txt in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ folder (just for temporary). Then tried to encrypt the file by right clicking on the file > properties > advanced button – chose Encrypt button – We got message that “we cannot encrypt as user profiler does not have that option or access to encrypt the file”

v This indicated us that there is some problem with the Profiler SID in the registry key location - HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList\

v We then checked the registry key to check if we have proper SID for the RSC\sqladmin account or not. -

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList\S-1-5-21-1243468345-3007771793-2118396552-1329.bak for sqladmin account

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList\S-1-5-21-1243468345-3007771793-2118396552-1329 for sqladmin.RSC account

v Checked in my Test machine, noticed there should not be any SID having .bak extension. Looks like someone has manually changed or took backup of the SID and gave the naming as .bak.

v We checked for one more account and it too had the similar setting. So this leads to one of the cause of the issue as improper SID in the registry causing SQL not to start and give error –

TDSSNIClient initialization failed with error 0x2, status code 0x38.

v So we decided to change the above SID values to as shown below

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList\S-1-5-21-1243468345-3007771793-2118396552-1329 for sqladmin account

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList\S-1-5-21-1243468345-3007771793-2118396552-1329.bak2 for sqladmin.RSC account

v We then tested out Test.txt file whether it can be encrypted or not? Yes, we were able to encrypt it successfully.

v Tried starting SQL Server and it errored out with the below message.

2009-07-16 01:15:17.41 spid4s Error: 15466, Severity: 16, State: 1.

2009-07-16 01:15:17.41 spid4s An error occurred during decryption.

2009-07-16 01:15:17.47 spid9s Starting up database 'model'.

2009-07-16 01:15:17.47 spid4s Server name is 'RSCSQL'. This is an informational message only. No user action is required.

2009-07-16 01:15:17.62 Server Error: 17190, Severity: 16, State: 1.

2009-07-16 01:15:17.62 Server FallBack certificate initialization failed with error code: 1.

2009-07-16 01:15:17.62 Server Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.

2009-07-16 01:15:17.63 Server Error: 17182, Severity: 16, State: 1.

2009-07-16 01:15:17.63 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x80.

2009-07-16 01:15:17.63 Server Error: 17182, Severity: 16, State: 1.

2009-07-16 01:15:17.63 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x1.

2009-07-16 01:15:17.63 Server Error: 17826, Severity: 18, State: 3.

2009-07-16 01:15:17.63 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.

2009-07-16 01:15:17.64 Server Error: 17120, Severity: 16, State: 1.

2009-07-16 1:15:17.64 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.

v Then Deleted the Certificate key in regedit location (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\). It did not help in starting SQL but it gave same error message in SQL Server Error Log.

2009-07-16 01:19:19.22 spid9s Starting up database 'model'.

2009-07-16 01:19:19.29 Server Error: 17182, Severity: 16, State: 1.

2009-07-16 01:19:19.29 Server TDSSNIClient initialization failed with error 0x2, status code 0x38.

2009-07-16 01:19:19.29 Server Error: 17182, Severity: 16, State: 1.

2009-07-16 01:19:19.29 Server TDSSNIClient initialization failed with error 0x2, status code 0x1.

2009-07-16 01:19:19.29 Server Error: 17826, Severity: 18, State: 3.

2009-07-16 01:19:19.29 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.

2009-07-16 01:19:19.29 Server Error: 17120, Severity: 16, State: 1.

2009-07-16 01:19:19.29 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.

           

v We then went to C:\Documents and Settings\ Folder, Noticed “sqladmin” folder and “sqladmin.RSC” folder. So we have different profiles for sqladmin account.

v This indicated and confirmed us that the Profile – “sqladmin” has been corrupted. How it got corrupted? There is no clue or logs or avenues to explore which could indicate or say it’s difficult to explore in finding how it (Profile – “sqladmin”) got corrupted. (This issue is commom in Sysprep systems or After restoring systemlevel backups) But the cause of the issue would be User Profile – “” was corrupted and landed us in to this issue (SQL not starting).

v How to resolve this issue now?

Recreate the user Profile for startup account of SQLServer- by following below steps.

1.Right click on computer, and select propertie
2.Right click on computer, and select properties
3.Click on System Advanced Configuration
4.Select Advanced options Tab
5.On user profiles frames click on configuration
6.Select the user from the list and then delete it
7.Now the profile will be respawned the next time the user logs on

Related post:

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

 

 

By

Gagan Hiremath

Reviewed by

     Karthick P.K

     Technical Lead, Microsoft SQL Server PSS

Comments

  • Anonymous
    October 06, 2011
    Nice !

  • Anonymous
    April 14, 2012
    Helpful post.

  • Anonymous
    May 06, 2013
    Had the exact same thing happen on a SQL 2008 R2 clustered instance, your solution was spot on. Great post!

  • Anonymous
    August 17, 2013
    your solution is perfect... Thanks a lot