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 0x35, 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.
One response to “Fixing remote connection problem for Microsoft SQL Server 2005”
For work with sql files advise use-repairing sql server,tool helped me not once and as far as i know it is free,program can help with this problem and retrieve the data, that was considered to be lost,mwill extract housekeeping data from the source database and preview the data, that can be recovered,this tool is a good solution to recover data from corrupted databases in MS SQL Server format,restore databases represent files, like any other documents, they can be easily corrupted by viruses, all sorts of malware, hard drive failures, file system errors, incorrect user actions, etc,supports both data extraction to your hard drive as scripts in SQL format and data export directly to a database in MS SQL Server format.