I've created an SSIS package that executes a stored procedure on a linked server. I need to capture the results from the remote SP to a local table, but I don't have permissions for Network DTC access. I am therefore using openquery as below to retrieve the results I need.
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
DECLARE @OPENQUERY nvarchar(4000)
SET @startDate = (SELECT DATEADD(month, -3, Report_Start_Date) FROM xxxx.dbo.REPORT_Config_Params)
SET @endDate = CURRENT_TIMESTAMP
SET @OPENQUERY = 'SELECT * FROM OPENQUERY(LINKEDSERVERNAME, ''SET FMTONLY OFF EXEC RemoteDB.dbo.storedprocedure ''''' +
CONVERT(VARCHAR(20),@startDate) + ''''',''''' + CONVERT(VARCHAR(20), @endDate) + ''''''')'
SET NOCOUNT ON
INSERT INTO LocalDatabase.dbo.table (col1, col2, col3, etc)
The query runs fine, but every other run, I get an "NT AUTHORITY\ANONYMOUS LOGON" failure in the SSIS package that runs it. I'm running the SSIS package from a workstation connected to the server my database is on. The service account for SQL Server has delegation enabled for Kerberos.
If I then RDP to the server that my database is on and run:
SELECT * FROM LINKEDSERVERNAME.master.sys.databases
it returns results (sys.dm_exec_connections show kerberos auth_scheme) and it free's up the block on my SSIS package. I then re-run the package and it goes through to completion without any errors. However, if I run my package again, it fails with the same error more often than not.
Do you think it's related to my package? Or is it more likely to be infrastructure related?