Can't connect to SQL Server using an Alias

Question

I have a SQL Server 2000 install on a server that I am having weird connection issues with.

Using SSMS I can't connect to it using an Alias I have setup on Configuration Manager. The Alias is set to use TCPIP which is the first protocol in the order below shared memory. If I use the exact same server name I put into the Alias then I can connect just fine.

I see the exact same behavior in my ASP.NET application, using the alias I get a connection failure, using the name it connects just fine. The ASP.NET connection string is not specifying a protocol.

Answer 1

Does the alias specify a protocol? Is that protocol accepted by the server? Is that the protocol being used when you are connecting by name? (i.e. what is the order of client protocols)

Answer 2

Using the server name will use the default connection protocol for the client. This may be named pipes or tcp/ip (MDAC version, upgrade vs install etc).

The Alias is set to use TCPIP which is the first protocol in the order below shared memory

I assume that if the client alias is set to use tcp, then the server is only configured to listen on named pipes so it only works with server name. The client will eventually find named pipes in it's list of protocols to use.

To test, replace servername with np:servername and tcp:servername to change the protocol without using the alias.

Or check what the server is listening on and fix that...

The other option is a non-standard TCP port (server, not 1433) or firewall preventing access to port 1433/1434.

SQL 2000 Books