SQL SERVER – Unable to Start SQL Service Error: 17172 – SNIInitialize() Failed with Error 0x2

SQL
No Comments

While preparing a lab for my upcoming training session, I was trying to play with the cluster. While doing that I was in a situation where I was unable to start SQL Service.

I looked into SQL Server ERRORLOG to see the cause of SQL Startup failure. Here were the last messages logged in the ERRORLOG. If you are new to SQL Server then refer below blog to find the location of the ERRORLOG file. SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location

2018-01-28 15:29:54.27 Server SNIInitialize() failed with error 0x2.
2018-01-28 15:29:54.27 Server SQL Server shutdown has been initiated

The error code mentioned is 0x2 which is 2 in decimal. Error Number 2 in windows means “The system cannot find the file specified.” Interesting, same error appears when I try to start SQL Service using services.msc.

SQL SERVER - Unable to Start SQL Service Error: 17172 - SNIInitialize() Failed with Error 0x2 sni-err02-01

Now, to find what is missing, I captured registry access while starting SQL Service using Process Monitor tool.

SQL SERVER - Unable to Start SQL Service Error: 17172 - SNIInitialize() Failed with Error 0x2 sni-err02-03

As we can see above, SQL Server was looking for a key called “ClusterName” in the registry and that is missing. Once we see “NAME NOT FOUND” then we see two “WriteFile” in SQL Server ERRORLOG followed by a “CloseFile” for ERRORLOG. This confirms that this is the right reason for SQL Startup failure. In my case, there was no SQL cluster and I was just trying to fool SQL Server by showing it as cluster but looks like there are more keys needed.

WORKAROUND/SOLUTION

Below is the registry key which should not be there if SQL is standalone. So we should take a backup and delete the “Cluster” key. Here is the complete path for my instance in case the image is not clear.

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\Cluster

SQL SERVER - Unable to Start SQL Service Error: 17172 - SNIInitialize() Failed with Error 0x2 sni-err02-02

The path would change based on SQL Server version and instance name. Here is the list of SQL versions (relevant as of today) and the respective keys in the registry.

SQL Server 2008MSSQL10
SQL Server 2008 R2MSSQL10_50
SQL Server 2012MSSQL11
SQL Server 2014MSSQL12
SQL Server 2016MSSQL13
SQL Server 2017MSSQL14

We need to append Instance Name (MSSQLSERVER for default instance) to make the complete registry key. By going with about logic, for my server SQL 2017, default instance it is MSSQL14.MSSQLSERVER.

Hope this blog helps someone who faces the similar issue.

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

, , ,
Previous Post
SQL SERVER – SQL Service Not Starting in Cluster- The System Cannot Find the File Specified
Next Post
SQL SERVER Data Tools – SSDT for Visual Studio 2017 Installation Error 0x80070002: Error 0x80070002: Failed to Acquire Payload

Related Posts

Leave a Reply

Menu