Alias definintion, what am I doing wrong?


I tried to define an alias on a server (as part of a database consolidation), but it doesn't seem to work. These are the steps I did:

  1. started SQL configuration manager on the server

  2. created alias under "SQL Native Client 10.0 configuration (32bit)" with name NWGALIAS

  3. protocol TCP/IP, no port (Default)

  4. SQL browser service is running

Started SQLcmd.exe from a client as "sqlcmd -Snwgalias -E", but it's returning "could not open a connection to SQL server" Server is 64bit and so is client, so I created a 2nd alias (NWGALIAS2) under the "SQL Native Client 10.0 configuration" (I assume the is 64bit), but with no result.

What am I doing wrong?

more ▼

asked May 21, 2012 at 02:37 PM in Default

avatar image

Wilfred van Dijk
3.6k 28 39 49

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

Is TCP an enabled protocol for the instance?

Is the SQL Server Browser service running for the server?

.. .. .. I have just created an alias on my local SQL 2008 instance via SQL Server Configuration Manager, SQL Native Client 11.0 Configuration (32bit), Aliases, New Alias... and everything seemed to work as normal.

Now, this is local, would there be a need for DNS to have an entry that points client PCs to my IP address so they can get picked up by SQL Browser service and passed(via the alias) to my instance?

more ▼

answered May 22, 2012 at 12:44 PM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(comments are locked)
10|1200 characters needed characters left

Create the alias on the client

more ▼

answered May 21, 2012 at 06:19 PM

avatar image

25k 3 13 20

The problem is that the application which is using the database is installed on several laptops (being used by field engineers). If creating an alias locally is the option, I could also alter the connection definition locally.

According to this link link text you could create an alias on the server also (first option)? It sounds stupid, but the article is not mentioning WHERE to define the alias.

May 22, 2012 at 07:53 AM Wilfred van Dijk

The client machine in the article has the Configuration Manager installed, which doesn't appear to match your environment. You mentioned that cliconfig works, but is not an option.

If you are not allowed to change anything on the client machine, you can create a DNS entry for NWGALIAS that points back to the "real" server.

If you are allowed to change something on the client, you can do the alias, change the application config file, or edit your hosts file with an entry for NWGALIAS that points to the "real" server to shortcut DNS.

May 22, 2012 at 02:50 PM KenJ
(comments are locked)
10|1200 characters needed characters left

@Fatherjack ♦♦ 's post had help me to solve this problem, because tcp protocol was disabled in SQL Server configuration manager for my instance and nearly no guide mentions that.

alt text

protocol.png (106.9 kB)
more ▼

answered Apr 10, 2015 at 07:53 AM

avatar image

11 2

(comments are locked)
10|1200 characters needed characters left

As per the MSDN / SQL Server documentation on Creating Server Aliases:

... An alias is an alternate name that can be used to make a connection ... Aliases can be used by any client application ...

And the rest of that introductory paragraph makes it pretty clear that these are for client machines, rather than server-side setup, and this is confirmed by the structure on the left of the page, where it shows the following:

 Database Engine Instances
   Client Network Configuration
     Configure Client Protocols
     Create or Delete a Server Alias for Use by a Client
     Logging In to SQL Server

etc, showing that this is part of the Client network configuration.

more ▼

answered May 22, 2012 at 08:32 AM

avatar image

ThomasRushton ♦♦
42.2k 20 57 53

(comments are locked)
10|1200 characters needed characters left

SQL Server Configuration Manager is not a tool which is installed on a client. So I assume you run this on a server.

Running cliconfg.exe on a client and creating an alias works, but that's not an option for me. This alias trick was mentioned as a temporary solution, until we manually changed the connection definition on each laptop to the 'real' servername.

more ▼

answered May 22, 2012 at 08:50 AM

avatar image

Wilfred van Dijk
3.6k 28 39 49

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 21, 2012 at 02:37 PM

Seen: 2354 times

Last Updated: Apr 10, 2015 at 07:53 AM

Copyright 2018 Redgate Software. Privacy Policy