alias not working on sql server 2008 r2

Question

I have several servers with SQL Server 2008 R2 instances on them, and alias doesn't work on any of them.

Clients connect to these servers using TCP/IP without any problem, telnet works on IP/Port I use for my alias, the firewall exceptions are created, basically everything works fine, except when I create an alias, I can not connect through it to my server using either TCP/IP or named pipes (local or one of other servers).

I've installed latest cumulative updates, which updates native client too (which I think is the source of problem) and I still have the problem. The stranger part is, if I create an alias on a server with sql server 2005 (native client 9), I can connect to my 2008 r2 instances. Any suggestions?

Answer 1

After you are sure, that it's not firewall problem, TCP/IP problem, and you can connect to server regularly without using alias and only have a problem to connect with alias, I have this problem on Vista and Windows 7.

Solution is to set up proper port inside of "SQL Server Network Configuration" inside "SQL Server Configuration Manager".

Here are the steps:

Go to Computer Management -> Service and Application -> SQL Server Configuration Manager -> SQL Server Network Configuration

Notice that you can also open directly SQL Server Configuration Manager (not from Computer management)

Then in SQL Server Network Configuration, if it is not already enabled, enable TCP/IP protocol.

Right click to open properties of TCP/IP protocol

Then on IP Adresses Tab you will have couple of records.

For every one put TCP Port = 1433

If you use x64 operating system, you have two "SQL Server Network Configuration" nodes, one for 32bit and the other for 64bit. Be sure that you have checked those port on both of them.

Good luck

Answer 2

For me it was the sequence of creating the aliases that was important. See this link: W2K8 R2 SQL Alias will not connect I started by deleting everything, CliConfig aliases and Configuration Manager Native Client config aliases. Then re-create, adding the CLICONFG version first.

1. run CLICONFG - create your TCP alias (will default to the x64 version if you're on a 64-bit O/S)

2. From SQL Server Configuration Manager create the alias under "SQL Native Client 10.0 configuration"

Try to connect using SSMS - it worked for me. If it doesn't you could go on to try the 32-bit set-up. I did this anyway as the application I'm developing which uses the alias is x86.

3. %SystemRoot%/SysWow64/CliConfg.exe (32-bit version on 64-bit O/S)
4. "SQL Native Client 10.0 Configuration (32bit)" under the configuration manager tool.