SQL SERVER – Unable to Start SQL Server – TDSSNIClient Initialization Failed with error 0xd

As a part of my passion, I do visit the forums and try to help. I contacted him personally via email and found an interesting situation. So, sharing it via the blog. I saw below post in a public forum about TDSSNIClient Initialization Failed.

Hi Experts,
I am not able to start the SQL Server service. Getting below error while starting SQL via configuration manager.

The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.

Any ideas?

Thanks!
As the error message says, I asked for ERRORLOG and here is the place of failure.

2016-02-24 00:16:36.95 spid13s     Error: 17182, Severity: 16, State: 1.
2016-02-24 00:16:36.95 spid13s     TDSSNIClient initialization failed with error 0xd, status code 0x10. Reason: Unable to retrieve registry settings from TCP/IP protocol’s ‘IPAll’ configuration key. The data is invalid.
2016-02-24 00:16:36.95 spid13s     Error: 17182, Severity: 16, State: 1.
2016-02-24 00:16:36.95 spid13s     TDSSNIClient initialization failed with error 0xd, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The data is invalid.
2016-02-24 00:16:36.95 spid13s     Error: 17826, Severity: 18, State: 3.
2016-02-24 00:16:36.95 spid13s     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-02-24 00:16:36.95 spid13s     Error: 17120, Severity: 16, State: 1.
2016-02-24 00:16:36.95 spid13s     SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

If we look into the first message it says

Unable to retrieve registry settings from TCP/IP protocol’s ‘IPAll’ configuration key

and then it says

The data is invalid.

I asked to capture, process monitor to figure out where is IPAll key and what is the value.

Opal is basically a registry key which is set by the configuration manager.

Under properties, we need to go to the IP Address tab and scroll all the way down till IPAll.

He also confirmed that if a port is made dynamic, it works.

My next question was what was changed and he said that they want to use non-default port numbers. They had a very interesting logic. Based on IP address of the machine, they provide port so that they can remember it easily. If IP is ending with 2, they would use 2433. This machine has IP ends with 66 so they used 66433.

To find if port is valid or not, I searched and found https://msdn.microsoft.com/en-us/library/ms177440.aspx which points to http://support.microsoft.com/kb/929851 which says default end port is 65535. Means valid port is 0 to (2^16)-1. Now I understand why I saw “data is invalid” message in ERRORLOG.

Once we changed the port to a value within the range, SQL started fine.

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