Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

NT AUTHORITY\ANONYMOUS LOGON intermittent failures Expand / Collapse
Author
Message
Posted Friday, January 11, 2013 4:46 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:47 AM
Points: 837, Visits: 2,460
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
Post #1405906
Posted Tuesday, January 15, 2013 4:16 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:47 AM
Points: 837, Visits: 2,460
any thoughts? thanks
Post #1407148
Posted Tuesday, January 15, 2013 4:50 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:02 AM
Points: 709, Visits: 1,422
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"
Post #1407178
Posted Tuesday, January 15, 2013 6:31 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:47 AM
Points: 837, Visits: 2,460
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
Post #1407230
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse