Linked server for replication

Posted on 2009-07-02
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 .


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'
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!


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 500 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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

688 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