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


NT AUTHORITY\ANONYMOUS LOGON


NT AUTHORITY\ANONYMOUS LOGON

Author
Message
DBA From The Cold
DBA From The Cold
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2181 Visits: 1745
Hello,

I am having a problem with an SSIS 2012 package contacting a database on a separate server. When run manually, under the Integration Services Catalog, the package is failing to contact the database on the separate server. The separate server is recording login failures for [NT AUTHORITY\ANONYMOUS LOGON].

SSIS is running under a domain account which has access to the server, however the second server seems not to recognise the account.

Has anyone seen this before? Any help or advice would be most appreciated.

Andrew
DBA From The Cold
DBA From The Cold
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2181 Visits: 1745
Not giving up on this one!

The problem seems to be that the account is used NTLM authentication, so it is not surviving the "double hop", hence the failure for [NT AUTHORITY\ANONYMOUS LOGON].

I've registered an SPN on the target server:-

setspn.exe -A MSSQLSvc/FQDN:1433 DOMAIN\ACCOUNT

But still the problem remains. Going to try some more options and come back with the answer (not that anyone cares, I know, but for my own sanity).

Andrew
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7217 Visits: 6431
The title of your thread caught my eye because I had a similar problem with SQL Server Agent across remote servers just recently.

Here is the question and solution I posted:
http://www.sqlservercentral.com/Forums/Topic1455796-391-1.aspx

I never did find a clean solution although I did get it working.

I'm hoping that if you or someone else finds a solution to your problem it might help me to make my solution better.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Richard Fryar
Richard Fryar
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 1172
Hi Andrew,

Assuming the details in the SPN are correct (eg. it is listening on port 1433, the FQDN has been specified correctly):

Have you restarted the SQL service since adding the SPN?

Have you checked for duplicate SPNs on the server. I saw a server recently on which SQL Server had been reinstalled and a new SPN created. But the old SPN (for a different service account) was still hanging around. It had to be deleted using SETSPN -D.


Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com
DBA From The Cold
DBA From The Cold
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2181 Visits: 1745
Thank you for the help and advice guys.

I've been doing more research and it looks like SSIS does not support delegation:-

http://msdn.microsoft.com/en-us/library/aa337083.aspx

The work around I have got is to execute the package as a SQL Server Agent job, running under the SQL Server Agent Service AD account. This means that there is no double hop and the service account's credentials will be passed to the second server.

I'm going to have a look for duplicate SPNs on the server though, as I do want to confirm that the server can use KERBEROS authentication. I've worked through the same steps on another development server and the server can now use KERBEROS so it is something specific to the original server.

Andrew
DBA From The Cold
DBA From The Cold
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2181 Visits: 1745
Yep, there were duplicate SPNs. Found them by using the LDP tool detailed here:-

http://technet.microsoft.com/en-us/library/cc772897(v=WS.10).aspx

Once removed, the server started to use KERBEROS authentication.

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