Tuesday, October 28, 2008

SQL 2005 "Listen All" TCP/IP Networking Problem Fixed

I'm one of those DBAs that likes to lock down my SQL servers. This means ensuring that SQL is only listening on the TCP/IP port I want it on.
This is easy enough to configure with the SQL Server Configuration app or by editing the registry... The GUI is "cleaner". Anyways, you'll want to set a static port for the IP enable that IP, disable the others, and then disable "listen all". However, when doing this you may occasionally run into a problem where you get error messages like this when you try to start the SQL service:

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.
AND/OR
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

If you know the port is available (netstat is your friend) and the IP address is correct then here's what you need to check:
Is there by any chance more than one 127.0.0.1 entry? Yes? Regedit time!
Go into the registry and navigate to HKLM\Software\Microsoft\Microsoft SQL Server\*INSTANCE NUMBER*\MSSQLServer\SuperSocketNetLib\Tcp\

Look for one of the IPx keys that has 127.0.0.1 in the IpAddress entry and delete it. After that you should be able to start the SQL service with no troubles.

Cheers!

No comments: