Problem With Linked Server

• Balu-535489

  Right there with Babe

  Points: 781

  More actions

  November 26, 2007 at 5:20 am

  #184335

  I created a Linked Server Object with Sql Authentication to connect to another server.

  I am executing this some time well, but some times it giving error Like

  "OLE DB provider "SQLNCLI" for linked server "sa_link" returned message "Cannot generate SSPI context".

  Msg 802, Level 16, State 1, Line 0

  SQL Network Interfaces: The target principal name is incorrect.

  OLE DB provider "SQLNCLI" for linked server "sa_link" returned message "Invalid connection string attribute"." :w00t:

• abmore

  Hall of Fame

  Points: 3628

  More actions

  November 26, 2007 at 6:16 am

  #753990

  Hi Balavardhan,

  Check your linked server connection properties, may be you have not properly added the linked server.

  EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServer', @srvproduct=N'SQL Server'

  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkedServer',@useself=N'False',@locallogin=NULL,@rmtuser=N'Login',@rmtpassword='PWd'

• dineshvishe

  SSCrazy

  Points: 2308

  More actions

  May 19, 2013 at 12:03 pm

  #1616476

  Hi,

  server name='DINESH\TESTMSSQL'

  loginname='test'

  loginpassword='########'

  replace in following script and use it..

  EXEC master.dbo.sp_addlinkedserver @server = N'DINESH\TESTMSSQL', @srvproduct=N'SQL Server'

  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DINESH\TESTMSSQL',@useself=N'False',@locallogin=NULL,@rmtuser=N'test',@rmtpassword='########'

  GO

  EXEC master.dbo.sp_serveroption @server=N'DINESH\TESTMSSQL', @optname=N'collation compatible', @optvalue=N'false'

  GO

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

  GO

  EXEC master.dbo.sp_serveroption @server=N'DINESH\TESTMSSQL', @optname=N'dist', @optvalue=N'false'

  GO

  EXEC master.dbo.sp_serveroption @server=N'DINESH\TESTMSSQL', @optname=N'pub', @optvalue=N'false'

  GO

  EXEC master.dbo.sp_serveroption @server=N'DINESH\TESTMSSQL', @optname=N'rpc', @optvalue=N'false'

  GO

  EXEC master.dbo.sp_serveroption @server=N'DINESH\TESTMSSQL', @optname=N'rpc out', @optvalue=N'false'

  GO

  EXEC master.dbo.sp_serveroption @server=N'DINESH\TESTMSSQL', @optname=N'sub', @optvalue=N'false'

  GO

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

  GO

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

  GO

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

  GO

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

  GO

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

  GO

  EXEC master.dbo.sp_serveroption @server=N'DINESH\TESTMSSQL', @optname=N'remote proc transaction promotion', @optvalue=N'true'

  GO