NTAuthentication with Linked Server.

• Steve Velasco

  SSC Veteran

  Points: 282

  More actions

  May 14, 2007 at 4:39 pm

  #68664

  Hi folks,

  My domain login has SA on the host sqlservers. I'm trying to perform queries against the sqlserver instances using Linked Servers but getting login errors. I'm basically logged on to the source using my NT Login and unable to use the Linked Servers getting a login failure. Here's the error:

  15:38:45.707DBMSdev_CNET42 -- Error: OLE DB provider 'SQLOLEDB' reported an error. Authentication failed.(42000,7399), Batch 1 Line 1

  Thanks,

  SteveV

• dba-don

  SSC Enthusiast

  Points: 120

  More actions

  May 14, 2007 at 11:14 pm

  #706384

  Hi SteveV

  How was the training?

  1.The linked server setup for impersonation or remote security?

   

  Don

   

• Steve Velasco

  SSC Veteran

  Points: 282

  More actions

  May 15, 2007 at 10:51 am

  #706578

  Here's the DDL for the Linked Server:

  USE master

  go

  EXEC sp_addlinkedserver @server='c12-esa-app01', @srvproduct='SQL Server'

  go

  EXEC sp_serveroption @server='c12-esa-app01', @optname='rpc', @optvalue='false'

  go

  EXEC sp_serveroption @server='c12-esa-app01', @optname='collation compatible', @optvalue='true'

  go

  EXEC sp_serveroption @server='c12-esa-app01', @optname='data access', @optvalue='true'

  go

  EXEC sp_serveroption @server='c12-esa-app01', @optname='rpc out', @optvalue='false'

  go

  EXEC sp_serveroption @server='c12-esa-app01', @optname='use remote collation', @optvalue='false'

  go

  EXEC sp_addlinkedsrvlogin @rmtsrvname='c12-esa-app01', @useself='FALSE', @locallogin=NULL, @rmtuser=NULL

  go