15

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Saam
  • 385
  • 1
  • 3
  • 12
  • Aliases or instance names? An alias is an object. Make sure you have the SQL Browser service running. – ta.speot.is Jun 20 '11 at 07:41
  • my sql browser is running. I can connect with instance name, or IP. but when I create an alias, I'm stuck – Saam Jun 20 '11 at 08: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 Jun 20 '11 at 08: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 Jun 20 '11 at 08:23
  • http://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 Jun 20 '11 at 09:32
  • I've visited that page, the comment is about clients, where you usually don't have the SQL Server configuration manager, so you have to either install client connectivity components from SQL Server installation media, or use the solution recommended in the comment. But on my server, where I have a SQL Server instance installed, I have configuration manager, which configures native client as well, which is the right tool – Saam Jun 21 '11 at 02:23
  • The article says that the native client configuration is local to that server. You can't use that alias on another machine. Oh well, I feel I'm not helping. – ta.speot.is Jun 21 '11 at 02:53
  • Make sure that you have the 32 bit alias as well as the 64 bit alias. That turned out to be my problem. – Chad Mar 28 '13 at 16:07
  • I really hope there's a solution to this... I have the same or similar problem, but specifically with using an alias that's also the name of another active server in the domain. See here: http://dba.stackexchange.com/questions/132313/sql-server-2008r2-alias-not-working -- using a different alias works and using reused aliases with my 2014 SQL boxes works... – NateJ Mar 17 '16 at 01:48

8 Answers8

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

m1k4
  • 829
  • 2
  • 12
  • 26
8

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.

  1. %SystemRoot%/SysWow64/CliConfg.exe (32-bit version on 64-bit O/S)
  2. "SQL Native Client 10.0 Configuration (32bit)" under the configuration manager tool.
Tom Brown
  • 558
  • 1
  • 8
  • 20
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.

datawrangler
  • 61
  • 1
  • 1
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:

  1. SQL Native Client {VersionNo} Configuration
  2. 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.

Alan Macdonald
  • 1,872
  • 20
  • 36
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

SPE109
  • 2,911
  • 1
  • 19
  • 16
  • 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 Jun 20 '11 at 08:17
  • 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. – Saam Jun 21 '11 at 02:30
1

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

Zac
  • 4,510
  • 3
  • 36
  • 44
Jorge Cribb
  • 396
  • 2
  • 4
1

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

IV V
  • 71
  • 3
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?)

Scott Brickey
  • 1,207
  • 12
  • 22