1

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.

Finally , How I can Listen to all IPs?

Thanks

Improve this question
4
  • 2
    Can you please add a screenshot to what your TCP/IP properties look like? I uploaded a screenshot of how it's supposed to look at: imgur.com/drXIl5U Note that ALL TCP Dynamic Ports have the 0 deleted and left blank. Only under IPAll-TCP Port do we have a port # listed. Can you verify you did the same thing?
    – Ali Razeghi - AWS
    Commented Jul 1, 2016 at 19:19
  • I'm assuming that the interfaces/IP addresses for this server were changed AFTER SQL Server was installed. If that's the case the addresses in SQL Server Configuration Manager won't be synchronized with what is currently on the system and that may lead to the issue you're describing. Please post a comment or edit the question with this information. A screenshot of SSCM for IPs and a list of the current interfaces and IPs for windows.
    – Sean Gallardy
    Commented Jul 1, 2016 at 20:26
  • dear @Ali razeghi your link doesn't work for me. but I captured screenshot from tcp/ip properties of my machine . drive.google.com/open?id=0B3zq7J4BJZgMNEdCZEJIeml1RHc
    – hamed
    Commented Jul 2, 2016 at 17:55
  • dear @Sean Gallardy because this machine receive IP from DHCP my IP is changed . but I modify SQL IP from IP Addresses tab and synchronize with present IP and finally SQL service doesn't start yet.
    – hamed
    Commented Jul 2, 2016 at 17:55

1 Answer 1

Reset to default
1

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.

Improve this answer

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.