NT AUTHORITY\ANONYMOUS LOGON
-
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
-
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
-
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![/I]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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
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.
-
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
-
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply