Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Linked server for replication

Posted on 2009-07-02
Medium Priority
Last Modified: 2012-05-07
In SQL Server 2088 I am trying to setup a linked server for 'transactional replication with updates'. I need to setup ServerB as publisher and ServerA as subscriber. Within SSMS, running on ServerB box, I am connected to both ServerA and ServerB, I can add or see any users on both SQL Servers.

In SMSS and on ServerA I want to create a Linked Server to ServerB. Createing a New Linked Server I have the following:
  Linked Server = ServerB
  Type = SqlServer
  Local login=sa, remote user = sa, remote password=blue123
  'login not defined, connections will' = Not be Made
Server Options:
 Data Acess = True
 RPC = true
 RPC out = true
 Connection timeout =90
 Subscriber = false
 Enable Promotion of Distributed Transactions=True
When I do that I get the error:
The linked server has been created but failed a connection test. Do you want to keep the linked server?

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Access to the remote server is denied because no login-mapping exists. (Microsoft SQL Server, Error: 7416)

Also what I don't understand is where to I specify the real server name?

If I left the Subscriptoin wizard take over and it creates a linked server for me it calls it something like
and for Security it uses:
  local login= repllinkproxy
  remoteuser = sa
  password = blue123

If I right click on the this llinked server and use Test Link I get the error message:

     The test connection to the linked server failed.

   An exception occurred while executing a Transact-SQL statement or batch.    (Microsoft.SqlServer.ConnectionInfo)

  Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)

Why, what should be changed ?  
Question by:LukeB
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 24762753
Linked server mainly is used to access the the database placed on a different server.
I am bit confused with the question combining Linked Server & Replication.
For creating linked server please check below query...
exec sp_addlinkedserver 'remoteip\instance'   -- accept defaults for all other params
exec sp_addlinkedsrvlogin @rmtsrvname='remoteip\instance', @useself=false, @locallogin=null,
  @rmtuser='username', @rmtpassword='password'

And for creating replication we can directly create by reight clicking on the Replication folder.
For your reference  please check the attached doc.
please let me know if i there is any issue... or if i m wrong..


Author Comment

ID: 24768595
Hi Jagdish

For "Transactional Replication with Subscriber updates" the SSMS adds a linked server .

See http://msdn.microsoft.com/en-us/library/ms152769(SQL.90).aspx:

7. The Login for Updatable Subscriptions page is displayed for subscriptions that use immediate updating or have update_mode set to queued failover. On the Login for Updatable Subscriptions page, specify a linked server over which connections to the Publisher are made for immediate updating subscriptions. Connections are used by the triggers that fire at the Subscriber and propagate changes to the Publisher. Select one of the following options: Create a linked server that connects using SQL Server Authentication. Select this option if you have not defined a remote server or linked server between the Subscriber and the Publisher. Replication creates a linked server for you. The account you specify must already exist at the Publisher.
Use a linked server or remote server that you have already defined. Select this option if you have defined a remote server or linked server between the Subscriber and the Publisher using sp_addserver (Transact-SQL), sp_addlinkedserver (Transact-SQL), SQL Server Management Studio, or another method.

LVL 14

Expert Comment

by:Jagdish Devaku
ID: 24770061
hi Luke, thanks for the info...
regarding the error i found that creating linked with t-sql might solve this oiption.
after creating the linked server... select this option 'Use a linked server or remote server that you have already defined'
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.


Author Comment

ID: 24770822
Hi Jagdish
    exec sp_addlinkedserver ''

That works if I right click on the new Linked Server and use Test Connection.

But why not
    exec sp_addlinkedserver 'ServerB'

the actual UNC name of the server?  If I try that it creates the linked server but when I go to test the connection it fails - 'Unable to complete login process due to deay in opening server connection'

 Because when I use the transactional replication and it asks for 'a server you already defined' how is it going to know which linked server to use??
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 24770884
by creating the alias for as ServerB in SQL Server Configuration Manager.
exec sp_addlinkedserver 'ServerB' -> is possible.
--Because when I use the transactional replication and it asks for 'a server you already defined' how is it going to know which linked server to use??
wait for some time i will let you know.. i am configuring the same here.

Author Comment

ID: 24771530
Hi Jagdish,
how in SSMS do I create an alias ?

Also, I do have under SS Config Mananger | Network Settings | Protocols both Named Pipes and TCPIP enabled so very confused as to why
   exec sp_addlinkedserver 'ServerB'
is not possible and have to create an alias?? Should not have to ?

(MS sure does not make this easy for linking servers by UNC !! A person can connect  server by UNC name with SSMS, why not this wizard, very strange...)
LVL 14

Accepted Solution

Jagdish Devaku earned 2000 total points
ID: 24771701
noramlly replication asks for named instance.
so we create an alias and we call it instead of an ip address...
one more thing--- named instance alias should be same as sql server instance name....
in ss configuration manager -> SQL Native Client Configuration -> Alias
Right click on Alias -> New Alias -> Enter instance name in Name & IP address for the Server option. if require give port. and click OK.
Thats it alias is created. you can use this alias for creating replication, linked server and connecting sql server through ssms.
coming protocols... tcp/ip,named pipers,shared pipes will be enabled.
i am still working with replication in our environment and will let you know the process once it is done.

Author Comment

ID: 24778194
Hi Jagdish,

ok, I have created the alias and made the remote server conneciton and it now tests ok when I use SSMS | Linked Server | TestLink

Now when I go to do the replication wizard again for "transactional with subscriber updates" and it asks for  a remote server I use 'a server you already defined' (it does NOT ask for which remote that is ) see LoginForUpdatableSubscriptions.jpg

When I go to finish the wizard I get the error shown in NewSubscriptionWizard_err.jpg.
When I go to edit a record on ServerB it is ok and gets replicated so ServerA. But when I go to edit a record on ServerA I get the error shown in NoRowUpdated.jpg.

On the linked server I have the settings shown LinkedServerProperties.jpg

LVL 14

Expert Comment

by:Jagdish Devaku
ID: 24791586
Hi Luke,
I tried the same but i am facing a different issue i.e. permissions.
But I am succussful when I have did this from subscriber machine. I configured the linked server on the subscriber machine and then configured the subscription from the subscriber end. It worked fine.
so i suggest you to configure the same from subscriber end.

Author Comment

ID: 24799322
ok Jagdish, it is working now. I had to enable the RPC on the linked server I made on the subscriber.

Currently it is working , .So I will close this Q.

NEW issue : the replication that is working is Publisher PUSH, but I want to setup as Subscriber PULL to reduce load on publisher. When I follow thru the Wizard again, but this time specify PULL I get an error. But I will post another Q on that so comment there once you have tried the same


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question