Fixing remote connection problem for Microsoft SQL Server 2005
Today I played with a server setup a few months ago but has since been neglected. It has Microsoft SQL Server 2005 Standard Edition on it.
I got the following error when trying to connect to it using sqlcmd integrated authentication from my workstation:
C:\>sqlcmd -S MyServer
HResult 0×35, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [53].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
So I googled around a little bit. Some suggested to enable and start Sql Server Browser Service. I didn’t think that was the issue, because I had several SQL Server 2005 servers with Sql Server Browser Service disabled and I still could connect to them remotely. Subsequent testing confirmed I was right.
I then played around with Sql Server Surface Area Configuration (Programs -> Microsoft Sql Server 2005 -> Configuration Tools). Click on Service Area Configuration for Services and Connections. For Remote Connections, I adjusted the setting to Using TCP/IP only, and it worked. The other 2 choices, Using named pipes only and Using both TCP/IP and named pipes, did not work.