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.

SQL SERVER - Unable to Start SQL Server - TDSSNIClient Initialization Failed with error 0xd start-error-01

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.

SQL SERVER - Unable to Start SQL Server - TDSSNIClient Initialization Failed with error 0xd start-error-02

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

SQL SERVER - Unable to Start SQL Server - TDSSNIClient Initialization Failed with error 0xd start-error-03

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

SQL SERVER - Unable to Start SQL Server - TDSSNIClient Initialization Failed with error 0xd start-error-04

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)

,
Previous Post
SQL SERVER – Error Msg 5042, Level 16 – The File ‘tempdev02’ Cannot be Removed Because it is Not Empty
Next Post
SQL SERVER – Data Lineage Tracking in ETL Processes – Notes from the Field #124

Related Posts

9 Comments. Leave new

  • Tribhuwan Mishra
    May 11, 2016 5:08 pm

    sorry i have no any Idea

    Reply
  • Jose Delcour
    May 13, 2016 4:36 pm

    Very interesting article. I once had a similar issue and could not find a way to solve it. Thanks!

    Reply
  • Chandra Kothareddy
    February 2, 2017 1:44 am

    Dave this is an excellent article. I had similar issue where my port was in 67K’s and sql server services were not trying. I pulled out my hair looking all around with no clue. This helps to understand the limit on the port no’s available to use.

    Thanks,
    Chandra.

    Reply
    • Thanks for the comment Chandra. I want that no one should go bald due to SQL issue and find solutions on my blogs :)

      Reply
  • Hello Mr Dave,

    I am also getting this issue, but the error message differs a little bit.
    Error: 17182, Severity:16, State: 1
    TDSSNIClient initialization failed with error 0x103, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. No mare data available.
    I am also getting few more errors along with it.
    I deleted the server name in the failover cluster and again added the other server in configure application and services wizard, added the SQL server and SQL server agent to the virtual server instance as other resource.
    But now my SQL services are not starting, I am struggling in the forums and blogs since last three days but nothing seems to help.
    Can you please help??

    Reply
    • “No mare data available” is the real error. Look at ProcMon and see if you find something wrong with IP.

      Reply
  • Thanks for the reply, But resolved it, forgot to update in the forum.
    There was mismatch in registry settings.
    I got the hint from Microsoft document https://support.microsoft.com/en-in/help/883732/a-sql-server-cluster-resource-goes-to-a-failed-state-when-you-try-to-b

    I added the Instance name and virtual server name in registry path HKEY_LOCAL_MACHINE\Cluster\Resources\\Parameters

    Reply

Leave a Reply

Menu