Sunday, August 19, 2007

How to give alias to the SQL Linked Server


There are no problems to add SQL linked server to the specific host running it. But what if you need to give it an alias, rather than use hostname as linked server name?
Here is how to do it:
1) Step 1:
• In SQL Server Management Studio open Linked Servers and then 'New Linked Server'.
• Inside of appeared wizard – Select the General tab.
• Specify alias name in "Linked server" field.
• Select SQL Native Client as provider.
• Add sql_server in "Product Name" field (that's the magic).
• In "Data Source" – specify name of the host to be used as linked server.
2) Step 2:
• In Security tab – specify proper security options (e.g. security context)
3) Step 3:
• In Server Options tab – put "Data Access", RPC, "Rpc Out" and "Use Remote Collaboration" to be true.
4) Step 4:
• Enjoy.

57 comments:

Anonymous said...

It works! Thanks a lot!

Anonymous said...

Works great! Thanks!

Anonymous said...

yeah.. that's magic !

Anonymous said...

Thx!

Amboy Observer said...

Thank you!!

Alex Pinsker said...

I'm glad it helped to many people. It's amazing that this piece of information is so concealed.

Anonymous said...

thanks!!!

Anonymous said...

Thanks for the info Alex, this is one of those changes that can make life much easier when migrating stored procs from Dev to Prod without having to rename links in the code.

I had to walk one of our DBAs through the process of setting up a link, over the phone, and without permission to access the linked server wizard. Not an easy task - is there any change you could add screen shots for future reference?

Thanks
S*

Anonymous said...

You saved me a ton of time.

Agito obviam astrum:
Move towards the stars??

Alex Pinsker said...

Yes, move toward the stars, :)

Anonymous said...

yes, as someone commented, it helps to do it this way to make it easier to deploy stored procedures from dev to production without the need to change the path to the linked server insode stored procedures code.

thanks!

GScully said...

Excellent, I have servers with "-" in their names, that doesn't work to well in a query. This fixed that issue.

Kim Talley said...

Thanks! This is just what I was looking for. I had to create a linked server in test and wanted it to have the same name as production without linking to the production server. Great post!

[soksa]icy said...

Perfect; exactly what I needed. Thanks a million.

Anonymous said...

Thanks thanks thanks. Very helpfull !!!

Anonymous said...

it works

Unknown said...

it works

Don Luke said...

It works!

Unknown said...

Thanks so much, it works pretty good

Kai said...

Hi,

I'd like to point out that this not a real alias.
Internal Features of SQL Server relay on SQL Server as product name, which you cant achive using this way. e.g. if you try to set it up with the product name SQL Server it will fail.

Issue an select * from sys.servers to compare

However, there is a supported way to alias a native SQL Server

EXEC sp_addserver 'AliasName';
EXEC sp_setnetname 'AliasName','ServerName'

And then administer the properties in the linked server dialog of SMMC.

Regards,
Kai

Cristiano said...

Hi Alex,
this , and also what Kay wrote, are almost what I need.
do you know if exist a way to make also an alias for the dbName?
thanks

Anonymous said...

GREAT!

Anonymous said...

Thanks a milllliooooon !! AWESOME solution.

Anonymous said...

Thanks a lot..You made my day.

cheers
Poorna

Anonymous said...

Thanks a lot for this 'clear' and 'concise' solution.

Anonymous said...

not that i have much to add, but thank you!

Mutuelle sante said...

Many thanks for the guide, this information was especially important for me to learn how to give alias to the sql linked server. Many thanks

Anonymous said...

Worked like a charm

Anonymous said...

Worked perfectly and just what I was searching for. Thanks Very Much!

Ketan said...

Wow... it worked perfectly... Thanks a ton... you are great..

Rajeev M C said...

I love that. great result.

Yaniv Etrogi said...

Cool!
-Yaniv Etrogi

Anonymous said...

Unfotuantely its incorrect.

You should put "SQL Server", not "sql_server".

Anonymous said...

Thanks Alex

Sofya Nizhnaya said...

Great!!! I was looking for the solution, and could not find it!
THANKS!!!!

Sofya Nizhnaya.

Anonymous said...

Very Nice. Thank you so much.
You were a great help. It works great.

Anonymous said...

Just what I needed. Saved me a ton of work!

Anonymous said...

Thanks so much for sharing!

Anonymous said...

cool stuff yaar

Anonymous said...

Perfect. Thanks!

Peter said...

Sweet! Had forgotten about this, thanks!

Anonymous said...

Thank you, it works! :D

Anonymous said...

It works, thanks.

vpv said...

Why does it work?
What magic does 'sql_Server' do?

Anonymous said...

That works great! Thanks a lot.

Anonymous said...

Thanks very much, very useful for prod moves!!!!!!

YanivBD said...

WORKS!!!UR THE BESTTTTTTT

YanivBD said...

WORKS!!!

Babak said...

Thank you so much. It saved me. I wanted to run a distributed transaction and needed to explicitly address the remote server name placing special charactors like "." and "," . Thanks a lot. :-)

MikeT said...

This still works for Sql Server 2012, thanks a bunch!

Aman S. Aneja said...

Thanks a ton Alex!! I want to know what's the magic behind sql_server! Also, I want to know if this method will make any difference in performance?

Anonymous said...


For connecting to SQL2008R2 ,
This Provider "SQL Server Native Client 11.0" wouldnt work.

I had to use the Provider "SQL Server Native Client 10.0"


Thanks Works Great now.

Peter said...

Thanks a lot!

Unknown said...

/****** Object: LinkedServer [DB_ALIASNAME] Script Date: 12-06-2018 11:03:38 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'DB_ALIASNAME', @srvproduct=N'sql_server', @provider=N'SQLNCLI11', @datasrc=N'DB_ACTUALNAME'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB_ALIASNAME',@useself=N'False',@locallogin=NULL,@rmtuser=N'USERNAME',@rmtpassword='########'

Anonymous said...

With the deprecation of SQL Native Client how would we do this now?

Seth Wegner said...

Thank you, this was most helpful

Seth Wegner said...

Thank you this was most helpful