Search code examples
sqllinked-server

Permissions for Linked Server Remote Login

I am trying to connect 2 databases (not on same server or domain) using Linked Servers. Below is the query I am running to connect the 2.

My problem is that if I specify @rmtuser = 'sa', I am able to connect just fine. However, if I switch @rmtuser to 'RemoteIntegrationUser', I get an error saying unable to connect. (Error 18456)

I have verified that I can connect directly to the "Remote" server via SSMS using the 'RemoteIntegrationUser' account and it connects just fine.

Is there some special permission 'RemoteIntegrationUser' must have on the "Remote" server in order to be able to handle these incoming connections?

Exec sp_dropserver 'RemoteDB', 'droplogins'
go


EXEC master.dbo.sp_addlinkedserver 
@server = N'RemoteDB'
   ,@srvproduct = 'OLE DB Provider for SQL'
   ,@provider = N'SQLNCLI'
   ,@datasrc = '111.2222.33.44'
   ,@catalog = 'myremotedb'

EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname = N'RemoteDB'
   ,@useself = false
   ,@locallogin = 'LocalIntegrationUser'
   ,@rmtuser = N'RemoteIntegrationUser'
   ,@rmtpassword = N'************'
go
Solution

  • FACE SMACK

    Talk about missing the forest for the trees. Somehow I wound up using the wrong password. Swore I was using the correct one, but nope. Once again, the weakest link in the human/computer relationship turns out to be NOT the computer.