CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008alias
Saam
Saam

Reputation: 385

alias not working on sql server 2008 r2

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?

Upvotes: 15

Views: 30246

Answers (8)

Jorge Cribb
Jorge Cribb

Reputation: 406

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

Upvotes: 1

Alan Macdonald
Alan Macdonald

Reputation: 1900

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.

Upvotes: 2

datawrangler
datawrangler

Reputation: 61

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.

Upvotes: 6

Scott Brickey
Scott Brickey

Reputation: 1227

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?)

Upvotes: 0

m1k4
m1k4

Reputation: 829

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

Upvotes: 22

IV V
IV V

Reputation: 71

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

Upvotes: 1

Tom Brown
Tom Brown

Reputation: 793

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.

Upvotes: 8

SPE109
SPE109

Reputation: 2951

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

Upvotes: 2

Related Questions