Very strange SQL Server 2005 Remote Connection Issue
Prasad Behere
I’m facing very strange SQL Server connection issue where I’m able to
connect to default SQL 2000 instance remotely but not able to connect to SQL
Server 2005 named instance remotely. I’m getting following error
Error Text:
“A network-related or instance-specific error occurred while establishing a
connection to SQL Server. The server was not found or was not accessible.
Verify that the instance name is correct and that SQL Server is configured to
allow remote connections.
(provider: SQL Network Interfaces, error: 26 – Error Locating
Server/Instance Specified) ”
Please find below mentioned information regarding the environment.
Server environment information:
• Server is Microsoft Windows Server 2003 (SP2).
• Machine has SQL Server 2000 (Enterprise Edition) and SQL Server 2005
(Enterprise Edition) with SP2 installed.
• SQL Server 2000 is default instance and SQL Server 2005 is named instance.
• Named Piped and TCP/IP protocols are enabled on both client and server.
• SQL Server 2000 can be connected remotely through Named Piped protocol
(but not through TCP/IP) and SQL Server 2005 can not be connected either
through named pipe or TCP/IP.
• SQL 2000 uses default port 1433 and SQL 2005 uses dynamic port.
• Firewall is disabled on server machine.
Problem statement:
SQL Server 2005 named instance is not being able to connect remotely.
Things checked to resolve the issue:
• SQL Server 2005 Surface area configuration for Remote connections. Server
allows remote connections.
• SQL Server 2005 browser services are running and are active under SQL
Server configuration manager
• Added exception in firewall for SQL Server Browser service and added UDP
port 1434. However as firewall is disabled it would not make any difference.
Some more information:
• I was able to connect it after the installation and everything was working
fine. The problem came soon after a disk, where MDF and LDF are, from
machine(there are two separate disks) got disconnected somehow, and
reconnected.
• Tried to connect through SSMS with Port in Server name like <server
name>\<instance name>,<port number>.
• Tried to check if TCP/IP port is LISTENING or FILTERED with PortQry
utility(version 2.2).
• The server in question is able to connect locally and can connect to other
servers.
Please provide your feedback and suggestions. Thanks in Advance.
Prasad Behere
Erland Sommarskog
> � Tried to connect through SSMS with Port in Server name like <server
When you specify port number, you should not specify the instance name.
Just
server,xxx
But since you cannot connect to the default instance with TCP/IP, I would
not expect this to work either. If you instance specify the named pipe
and force Named Pipes with
np:server,xxxx
I think it mayt work. Except that I don't know exactly how to specify
the named pipe.
Are you able to ping the machine? It seems to me that the root issue is
that you cannot connect with TCP/IP at all.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx