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?

Aliases or instance names? An alias is an object. Make sure you have the SQL Browser service running. — ta.speot.is
– ta.speot.is, Commented Jun 20, 2011 at 7:41
my sql browser is running. I can connect with instance name, or IP. but when I create an alias, I'm stuck — Saam
– Saam, Commented Jun 20, 2011 at 8:13
I don't understand, an alias is something you use within the instance. I'm not aware of any ability to give a named instance an alias. — ta.speot.is
– ta.speot.is, Commented Jun 20, 2011 at 8:16
I'm sorry, are we talking about the same thing? :) I'm talking about aliases you create on client to connect to an instance. As far as I know, it has no limitations about instance type(default or named) — Saam
– Saam, Commented Jun 20, 2011 at 8:23
msdn.microsoft.com/en-us/library/ms190445.aspx explained it to me. There's a warning in the comments about using the right configuration tool for the architecture your client application will you. Perhaps that's it? — ta.speot.is
– ta.speot.is, Commented Jun 20, 2011 at 9:32

m1k4 · Accepted Answer · 2012-03-24 11:38:12Z

22

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

answered Mar 24, 2012 at 11:38

m1k4

8292 gold badges12 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robert Brooker Over a year ago

Thanks for mentioning 32bit. In my case it wasn't until I set up the 32bit Aliases before I got it working

Tom Brown · Accepted Answer · 2011-10-24 15:25:42Z

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.

run CLICONFG - create your TCP alias (will default to the x64 version if you're on a 64-bit O/S)
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.

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

datawrangler · Accepted Answer · 2013-12-20 16:24:36Z

6

For me, the answer was to use the 32-bit CLICONFG. Both Management Studio and the application I was trying to install were 32-bit applications on a 64-bit server. Moral of the story is to create both 64-bit and 32-bit aliases.

answered Dec 20, 2013 at 16:24

datawrangler

611 silver badge1 bronze badge

Comments

SPE109 · Accepted Answer · 2011-06-20 09:10:37Z

2

Are you using named instances? And if so have you checked that the port is statically assigned?

If you are using default instances are you running on a x64 platform? There are aliases for 32bit and 64bit so SSMS on the same box as the database engine would use the alias under the 32 bit section even though the box is 64bit

edited Jun 20, 2011 at 9:10

answered Jun 20, 2011 at 7:22

SPE109

2,9511 gold badge19 silver badges16 bronze badges

2 Comments

Saam Over a year ago

nope, they are all default instances, and I use static ports. anyway, I can connect to my instances via TCP/IP and instance name/IP, so there isn't a problem with my protocol configuration

Saam Over a year ago

Well I only have the "SQL Native Client Configuration" node(not 32-bit mentioned anywhere) so I'm assuming the alias is under 64-bit version of native client, and the 32-bit version isn't installed.

Alan Macdonald · Accepted Answer · 2016-01-08 14:48:57Z

2

For me this was caused by me creating the alias on a 64 bit machine but the software running as a 32 bit application.

When in Sql Server Configuration Manager ensure the alias is set under both the following sections:

SQL Native Client {VersionNo} Configuration
SQL Native Client {versionNo} Configuration (32bit)

That way it will be available regardless of processor platform. Of course if you only want the alias available for one platform for some reason, set the appropriate one and not the other.

answered Jan 8, 2016 at 14:48

Alan Macdonald

1,91021 silver badges36 bronze badges

Comments

IV V · Accepted Answer · 2011-10-26 18:54:18Z

1

Try it with the IP address, like 127.0.0.1 instead of your machine name, localhost or .(dot).

answered Oct 26, 2011 at 18:54

IV V

713 bronze badges

Comments

Zac · Accepted Answer · 2017-09-01 16:12:25Z

1

In my case it only worked when I placed the port on the connection [server]\[instance],[port]. Example: DBSERVER\OPERATIONS,1433

edited Sep 1, 2017 at 16:12

Zac

4,7553 gold badges39 silver badges46 bronze badges

answered Sep 1, 2017 at 15:50

Jorge Cribb

4164 silver badges4 bronze badges

Comments

Scott Brickey · Accepted Answer · 2012-05-01 14:16:07Z

0

Also, check that your alias uses listeners that are enabled (is your alias configured to use TCP while your server is only listening via Shared Memory?)

answered May 1, 2012 at 14:16

Scott Brickey

1,21912 silver badges22 bronze badges

Collectives™ on Stack Overflow

alias not working on sql server 2008 r2

8 Answers 8

1 Comment

Comments

Comments

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

1 Comment

Comments

Comments

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related