January 11, 2013 at 4:46 am
Hi,
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)
EXEC (@OPENQUERY)
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?
Thanks, Andy
January 15, 2013 at 4:16 am
any thoughts? thanks
January 15, 2013 at 4:50 am
What kind of credentials are used for the linked server, SQL or Windows? If it is windows I would check that Kerberos authentication is working because login errors with NT Authority\Anonymous Logon are usually a double-hop issue seen when authentication is trying to use NTLM instead of Kerberos.
Check the following link for more info:
Using Kerberos Authentication with SQL Server
Joie Andrew
"Since 1982"
January 15, 2013 at 6:31 am
Hi,
The linked server is set up to use the Windows login's current security context. On the face of it, Kerberos seems to be working ok. sys.dm_exec_connections shows several sessions using kerberos authentication and I can test the double-hop successfully.
In my SSIS package, I've set this step up as an execute sql task. It just runs the stored procedure on the remote server (as defined in the connection manager). Every other run fails with the nt authority logon error, which I fix by doing the linked server select query. If I run the stored proc by connecting to the server via SSMS on my local machine (the same pc I envoke the ssis package from) it runs successfully every time.
Cheers,
Andrew
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply