January 26, 2011
I was recently prompted by the following error message while attempting to connect to a MS SQL Server instance:
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)
It had been working previously, what could have changed to cause the problem?
I did a quick google but the suggested fixes had mixed results, and none solved my problem.
I stumbled on this article: SQL SERVER – FIX : ERROR...
It wasn't the answer to my problem, but it led me down the right path.
I was connecting to a MS SQL Server instance on a Virtual Machine I had running locally. Recently, I installed tethering software for my mobile phone which had corrupted my windows network configuration. Not saying that this you did the same, or were using a VM, but it helps explain the cause a bit. I had to re-install my VM software, and the VM networking components were re-configured in doing so.
The IP address of my VM had changed.
That was the culprit.
Go to the SQL Configuration Manager. (You can refer to the link above for a walk through with screen shots to get there)
Expand the SQL Network Configuration and click on the PROTOCOLS node
Right click on TCP/IP and open up the PROPERTIES panel
Select the IP ADDRESS tab
Make sure the values for the IP ADDRESS fields are correct and match the system it is running on.
Restart the service, and you should be back in business, I was.
On a side note, this is also the same place where lots of people get hung up when setting up remote access to a SQL Server instance.
Make sure you fill in the TCP PORT, even if you are using the default 1433.