Linked server for replication

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 ?  
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jagdish DevakuSr DB ArchitectCommented:
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..

LukeBAuthor Commented:
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.

Jagdish DevakuSr DB ArchitectCommented:
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'
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

LukeBAuthor Commented:
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??
Jagdish DevakuSr DB ArchitectCommented:
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.
LukeBAuthor Commented:
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...)
Jagdish DevakuSr DB ArchitectCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LukeBAuthor Commented:
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

Jagdish DevakuSr DB ArchitectCommented:
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.
LukeBAuthor Commented:
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.