Login failed for user 'sa' when trying to use linked server in select query

• Suresh Arumugam

  Hall of Fame

  Points: 3370

  More actions

  April 17, 2009 at 6:07 am

  #130132

  Hi All,

  I have created linked server in one of my servers using the below statement.

  EXEC sp_addlinkedserver

  @server='LINKED_Server', @srvproduct='',

  @provider='SQLOLEDB', @datasrc='MyServer'

  After creating this, I tried to run the below query and getting error message as - Login failed for user 'sa'

  select * from LINKED_Server.dbtest.dbo.tblemp

  Please help me to fix issue.

  -Suresh

  Regards,
  Suresh Arumugam

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  April 17, 2009 at 6:22 am

  #979571

  Hi

  You need to specify the remote login information by sp_addlinkedsrvlogin:

  DECLARE @LinkName SYSNAME SET @LinkName = 'LINK_NAME'

  DECLARE @SrvName SYSNAME SET @SrvName = 'ServerNameOrIP'

  DECLARE @LocalLogin SYSNAME SET @LocalLogin = NULL -- 'sa'

  DECLARE @RmtLogin SYSNAME SET @RmtLogin = 'RemoteLogin'

  DECLARE @RmtPwd SYSNAME SET @RmtPwd = 'RemotePassword'

  IF NOT EXISTS (SELECT * FROM Master..Sysservers WHERE IsRemote = 1 AND SrvName = @LinkName)

  BEGIN

  EXECUTE sp_addlinkedserver @server = @LinkName,

  @srvproduct = '',

  @provider = 'SQLOLEDB',

  @datasrc = @SrvName

  EXECUTE sp_addlinkedsrvlogin @rmtsrvname = @LinkName,

  @useself = 'false',

  @locallogin = @LocalLogin,

  @rmtuser = @RmtLogin,

  @rmtpassword = @RmtPwd

  END

  Greets

  Flo

• Suresh Arumugam

  Hall of Fame

  Points: 3370

  More actions

  April 17, 2009 at 6:48 am

  #979600

  grt.. That worked well.. Thanks a bunch flo..

  - Suresh

  Regards,
  Suresh Arumugam