2

I have problem with access to SQL server with TCP/IP.

I would like to SQL instance accept client from all network adapter that machine has.

I go to configuration manager console and enable TCP/IP, set TCP port to 1433 (or any other port) , set “TCP Dynamic Port” to Blank and set “Listen All” to true. After these changes, I restart SQL server service and starting SQL service will fail. For starting SQL service I have to change “Listen All” to false and then service will start successfully.

But after disabling “Listen ALL” you have to set Adapter IP in “IP Addresses” tab and SQL will accept request only from that IP and if you don’t specify any IP, SQL doesn’t accept any TCP/IP request.

Now I need to know why enabling “Listen ALL” will lead to service failure. And how can I resolve this problem?

I ran “netstate –a” and I saw no application has port 1433 on TCP.

When “Listen ALL” was disable and I don’t added any specific IP Yet, I check SQL browser service(UDP 1434) with “portquery.exe” and SQL browser response doesn’t include any TCP port.

After I added 1 specific IP SQL browser service include TCP 1433.

And as I mentioned if I enable “Listen ALL” , SQL service will failure and after restart it can’t start.

After service restart failure I checked windows event viwer and I saw 4 Error as Following :

1.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.

2.TDSSNIClient initialization failed with error 0xd, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The data is invalid.

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.

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

Some experts tell me may there is a tiny whitespace instead blank in IPALL/TCP Dynamic Ports but When I Checked out that I saw just Blank is there and it is OK. this screen shot of my TCP/IP properties for more detail : https://drive.google.com/open?id=0B3zq7J4BJZgMNEdCZEJIeml1RHc

also as you see from above image, All Ips that exist in IP Addresses tab exist and network adapters IPs doesn't changed.I mean I know if SQL listen to non exist Ip it will failed.
Finally , How can I Listen to all IPs?

Thanks

Improve this question
10
  • So I assume you read blogs.msdn.microsoft.com/sqljourney/2012/06/15/… and you checked to remove the whitespace characters in the registry HKEY_LOCAL_MACHINESoftwareMicrosoftMicrosoft SQL Server<InstanceName>MSSQLServerSuperSocketNetLib as this talks about. Also see Whitespace Characters
    – IT Thug Ninja
    Commented Jul 7, 2016 at 14:33
  • Heya Hamed, did PIMP_JUICE_IT's solution work for you?
    – Ali Razeghi - AWS
    Commented Jul 11, 2016 at 17:46
  • Dear @AliRazeghi my deduction from PIMP_JUICE_IT solution is : "white space has several code and may another whitespace code place into TCP Dynamic Ports field instead of common code". but I check binary value of that code and I saw it has exactly binary 0 value. Did I understand his solution correctly?
    – hamed
    Commented Jul 12, 2016 at 16:57
  • Dear @PIMP_JUICE_IT Did I understand your solution correctly? my understanding : "white space has several code and may another whitespace code place into TCP Dynamic Ports field instead of common code"
    – hamed
    Commented Jul 12, 2016 at 16:59
  • @hamed Per that article I posted for your reference: Under the IPAll subkey, you will find the same two “TCP Dynamic Ports” and “TCP Port” keys. Check the value for the TCP Dynamic Ports key. Do you see a whitespace there? If so, then most likely that is the reason for the service startup failure. Removing the whitespace should fix the issue, and the service should come online just fine. . Check the value for the reg key as indicated by the article and remove any whitespace/spaces and then retry, just delete the value from it and put in the new value manually so you know there is no space.
    – IT Thug Ninja
    Commented Jul 12, 2016 at 20:08

1 Answer 1

Reset to default
1

Finally

I found the reason of problem.

I don't know for what SQL or other services had changed the value type of TcpPort fields in registry (HKEY_LOCAL_Machine\Software\Microsoft\Microsoft SQL Server\Instance ID\MSSQLServer\SuperSocketNetLib\TCP\IPALL\TCPPort ).

It's converted to Binary or DWORD type.

but the solution : I removed TcpPort value and then create a new string value and named it TcpPort and set it to 1433 and then service started successfully.

there is no need to say that I checked that field over and over and it doesn't has any whitespaces.

Thanks to all experts whom participate in this discussion and helped me.

Improve this answer
2
  • now anybody can answer me why the type of tcp port had been changed?
    – hamed
    Commented Jul 17, 2016 at 17:33
  • I found for what it is converted to DWORD. reproachfully I write an application for set registry value and I did myKey.SetValue("TcpPort", "1433", RegistryValueKind.DWord); this line change value type to DWORD
    – hamed
    Commented Jul 17, 2016 at 18:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.