Linked Server

• balamurugan.ganesan

  SSC-Addicted

  Points: 455

  More actions

  May 16, 2008 at 5:27 am

  #190617

  Hi,

  I have added a server using

  EXEC sp_addlinkedserver

  'bala_linked',

  '',

  'SQLOLEDB',

  '',

  NULL,

  'DRIVER={SQL Server};SERVER=xxxxx;UID=xxxx;PWD=xxxx;'

  but when trying to fetch from the linked server it gives

  Login failed for user 'sa'.

  [OLE/DB provider returned message: Invalid connection string attribute]

  any suggestions would be greatly appreciated.

  Thanks,

  Balamurugan

• Yelena Varshal

  SSC-Dedicated

  Points: 34286

  More actions

  May 19, 2008 at 3:40 pm

  #817494

  You have to add the linked server login by mapping the local login to the remote login.

  http://msdn.microsoft.com/en-us/library/ms189811.aspx

  sp_addlinkedsrvlogin (Transact-SQL)

  Regards,Yelena Varsha

• Deepak-405225

  SSCertifiable

  Points: 6120

  More actions

  May 19, 2008 at 10:15 pm

  #817564

  I think you are trying to connect to the remote server using the SA password. Please ensure that the SA password is correct for the remote server.

  [font="Verdana"]- Deepak[/font]

• Simon Facer

  SSCertifiable

  Points: 5427

  More actions

  May 21, 2008 at 8:55 am

  #818624

  This is my standard script for adding a linked server:

  DECLARE @SQLCmd VARCHAR(1024)

  DECLARE @LS_Name SYSNAME

  DECLARE @ServerName SYSNAME

  DECLARE @user-id SYSNAME

  DECLARE @Pswd SYSNAME

  SELECT @LS_Name = '{LS Name}',

  @ServerName = '{Server to Connect to}',

  @user-id = '{Remote Server UID}',

  @Pswd = '{Password for @user-id}'

  -- ******************************************************************************************

  -- Create the Linked Server

  SELECT @SQLCmd = N'sp_AddLinkedServer @server=''' + @LS_Name + ''', @SrvProduct = '''', @Provider=''SQLNCLI'', @DataSrc=''' + @ServerName + ''''

  EXEC (@SQLCmd)

  -- ******************************************************************************************

  -- ******************************************************************************************

  -- Add the login to the Linked Server

  SELECT @SQLCmd = N'sp_addlinkedsrvlogin @RmtSrvName=''' + @LS_Name + ''', @UseSelf= ''FALSE'', @RmtUser=''' + @user-id + ''', @RmtPassword=''' + @Pswd + ''''

  EXEC (@SQLCmd)

  -- ******************************************************************************************

  -- Set the Linked Server options

  EXEC master.dbo.sp_serveroption @server=@LS_Name, @optname=N'collation compatible', @optvalue=N'true'

  EXEC master.dbo.sp_serveroption @server=@LS_Name, @optname=N'data access', @optvalue=N'true'

  EXEC master.dbo.sp_serveroption @server=@LS_Name, @optname=N'dist', @optvalue=N'false'

  EXEC master.dbo.sp_serveroption @server=@LS_Name, @optname=N'pub', @optvalue=N'false'

  EXEC master.dbo.sp_serveroption @server=@LS_Name, @optname=N'rpc', @optvalue=N'true'

  EXEC master.dbo.sp_serveroption @server=@LS_Name, @optname=N'rpc out', @optvalue=N'true'

  EXEC master.dbo.sp_serveroption @server=@LS_Name, @optname=N'sub', @optvalue=N'false'

  EXEC master.dbo.sp_serveroption @server=@LS_Name, @optname=N'connect timeout', @optvalue=N'0'

  EXEC master.dbo.sp_serveroption @server=@LS_Name, @optname=N'collation name', @optvalue=null

  EXEC master.dbo.sp_serveroption @server=@LS_Name, @optname=N'lazy schema validation', @optvalue=N'false'

  EXEC master.dbo.sp_serveroption @server=@LS_Name, @optname=N'query timeout', @optvalue=N'0'

  EXEC master.dbo.sp_serveroption @server=@LS_Name, @optname=N'use remote collation', @optvalue=N'true'

  -- ******************************************************************************************

  just set the {...} values.