Linked Server Error Invalid Authorization Specification

• lmarkum

  SSCertifiable

  Points: 7989

  More actions

  August 5, 2015 at 2:03 pm

  #319556

  I am trying to create a linked server object on a SQL Server 2008R2 box that will connect to a SQL Server 2014 instance. I am using the Linked Server wizard. I provide a name for the Linked Server and select the radio button labeled SQL Server. I go to the Security tab and add a Local Login and select Impersonate. On the Server Options I enable RPC and RPC Out. When I click OK I get the following error:

  "Authentication failed. Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for Linked Server "MyLinkedServer." Invalid Authorization specification."

  This is what the anonymized SQL looks like for this process. What am I overlooking?

  USE [master]

  GO

  EXEC master.dbo.sp_addlinkedserver @server = N'MyServer', @srvproduct=N'SQLSERVER', @provider=N'SQLNCLI10', @datasrc=N'MyServer\MySQLInstance'

  GO

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

  GO

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

  GO

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

  GO

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

  GO

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

  GO

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

  GO

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

  GO

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

  GO

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

  GO

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

  GO

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

  GO

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

  GO

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

  GO

  USE [master]

  GO

  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MyServer', @locallogin = N'PRODUCTIONVM\LinkedServer', @useself = N'True'

  GO

  USE [master]

  GO

  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MyServer', @locallogin = NULL , @useself = N'False'

  GO