Problem connecting to SS2K5 named instance using client tools

Hi,

The problem I have is this:

I have installed SQL Server 2005 Enterprise Edition on a W2K3 server that already has a SQL Server 2000 default instance installed. The installation appears to be a success. The problem is that having installed the client tools, and trying to connect, I get the following error message in a dialog:

"Cannot connect server SERVERNAME\INSTANCENAME
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. (provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)(Microsoft SQL Server)"

The funny thing is that I can connect to the SQL Server 2000 default instance that is on that server.
So it's obviously not a connection problem per se at my end i.e. bad dns etc which might stop me resolving the server name.

Googling for this error text gives the following advice on various web sites:

1. Check that the remote server allows connections.

It does, as other colleagues in my team have successfully installed the tools and can connect with no problems.

2. Check that the SQL Browser service is running.

It is, as without this running, my colleagues would not be able to connect either.

3. Open SQL Server Configuration Manager. Select "SQL Server 2005 Network Configuration | Protocols for MSSQLSERVER" then enable the protocols you need.

The required protocol (TCP/IP) is enabled. Named pipes is also enabled, although I don't think that we're using that. And shared memory is also enabled by default.

4. Open the firewall and add exceptions for sqlbrowser.exe or UDP port
1434

There is no firewall running at either end of the connection and as mentioned I can connect to the SS2K default instance.

5. Make sure that the server machine is reachable.

I can connect to the SS2K default instance so it must be reachable. I can't see that I would need to change any configuration settings as my colleagues are using exactly the same default settings for their connections and they work fine.

The only thing that I have done differently is that I originally installed the client tools using someone else's privileged account as I didn't have admin rights on my box. The client tools didn't work, so I got admin rights to my box, uninstalled the client tools and then reinstalled them using my account. Still no luck. Not sure if something is still hanging around after the uninstallation that would cause me a problem.

To help, I've also filled out the following questionnaire:

[1] Client side:

> What is the connection string in you app or DSN?

There isn't one. I'm trying to connect from the SQL Server Management Studio | 'Connect to Server' dialog.

> If client fails to connect, what is the client error messages?

"Cannot connect server Y\Z
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. (provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)(Microsoft SQL Server)"

Is the client remote or local to the SQL server machine? Remote

Can you ping your server? YES

Can you telnet to your SQL Server? YES

What is your client database provider? [SNAC | MDAC | ADO.NET1.0 | ADO.NET2.0| other (please specify] N/A
What is your client application? SQL Management Studio
Is your client computer in the same domain as the Server computer? Different domains
What protocol the client enabled? [Shared Memory | TCPIP | Named Pipes]. All 3 protocols are enabled
Do you have aliases configured that match the server name portion of your connection string? No
Do you select force encryption on server and/or client? No

[2] Server side:

What is the MS SQL version? SQL Server 2005
What is the SKU of MS SQL? Enterprise
What is the SQL Server Protocol enabled? [Shared Memory | TCPIP | Named Pipes ]. All 3
Does the server start successfully? YES. Other people can connect to the same instance.
If SQL Server is a named instance, is the SQL browser enabled? YES
What is the account that the SQL Server is running under? Domain Account
Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? N/A. Firewall not running.
Do you make firewall exception for SQL Browser UDP port 1434? N/A. Firewall not running.
What tool or Application are you using to connect to SQL Server SQL Server Management Studio

[3] Platform:

What is the OS version? Windows 2003 (Server side), Windows XP Pro Version 2002 SP 2
Do you have third party antivirus, anti-spareware software installed? Symantec AntiVirus

[4] Misc:

If you have certificate configuration issue: N/A

Has anybody else come across this problem, as I've run out of ideas.

Many thanks,

Hi,

I've fixed the problem.
One of my colleagues who can log onto the SS2005 named instance from his workplace came to my workplace today and found that he also couldn't connect from there. That ruled out my installation. So we played with SQLCMD trying to connect and found that we could if we specified the TCP port (1776). So then it was just a question of working out
what the syntax was to specify the port in the Connection dialog. Turns out that server-name\,1776 works and allows us to connect.

Thanks.