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
Best Answer
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.