Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've a 64-bit installation of SQL 2008 R2 SP2 on Server 2008 R2, with five instances of SQL.

I can get the aliases set up to work fine on the server (i.e. if I remote onto the server, then it works without problems, but from the client machine, there is no joy).

Errors:

SQL Pipes Error 40, Error 53.

Or, if specify port 1433, TCP Error 0, 11004.

If I specify port 63769 (which is mentioned as a port that SQL is listening on), TCP Error 0, 11004.

If I connect using the servername\instancename I have no problems.

I've set up the alias in 32-bit and 64-bit on the server. I've left the ports as dynamic in the TCP settings, because if I define the port as being 1433 for each instance, none of them will start. This is weird, as it's an approach I've used successfully on other servers.

There is no internal firewall, but some ports may, or may not, be shut off...

Any ideas about what to try next?

Thanks!

share|improve this question
    
Is tcp/ip enabled on client machines and is sql browser service running on sql servers ? –  Kin Jul 23 '13 at 14:52
    
Yes, tcp/ip is enabled on the client and browser service running on the server. –  user26260 Jul 25 '13 at 15:49

1 Answer 1

If you want aliases to work from the client then you have to create them on the client. Install the SQL Server client (from memory the feature is called Client Tools) and use Configuration Manager to create the aliases (32-bit or 64-bit to match the client application).

share|improve this answer
    
Why do I need to do this when every other alias I've created on other servers works perfectly when I connect from SSMS, please? Without having to create anything other than on the server? –  user26260 Jul 24 '13 at 10:25
    
Well, I've tried using configuration manager on my PC to deal with this, and got the following:------- Cannot connect to Aliasname. ------------------------------ ADDITIONAL INFORMATION: 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) (Microsoft SQL Server, Error: -1) OK ------------------------------ –  user26260 Jul 25 '13 at 15:47
    
I'm not sure I understand the issue you are having. From what I am reading, you can connect to the database server from the client machine using SQLSERVER\ALIAS but you cannot connect using an alias. Is that correct? If so, double-check you created the alias for 32-bit or 64-bit client. Also, if you created the alias using named pipes, create an alias using IP Sockets, or vice versa. To check the IP Sockets, try connecting to the server from SQL Server Management Studio using the connection of SERVER,1234 (that is the server name, a comma, then the port number it is listening on). –  Greenstone Walker Jul 25 '13 at 20:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.