SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


NT AUTHORITY\ANONYMOUS LOGON intermittent failures


NT AUTHORITY\ANONYMOUS LOGON intermittent failures

Author
Message
adb2303
adb2303
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1282 Visits: 3143
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
adb2303
adb2303
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1282 Visits: 3143
any thoughts? thanks
Joie Andrew
Joie Andrew
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2299 Visits: 2032
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"
adb2303
adb2303
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1282 Visits: 3143
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search