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 Expand / Collapse
Author
Message
Posted Friday, July 12, 2013 5:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:18 AM
Points: 1,304, Visits: 1,129
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
Post #1472943
Posted Monday, July 15, 2013 1:52 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:18 AM
Points: 1,304, Visits: 1,129
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
Post #1473881
Posted Monday, July 15, 2013 7:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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!
Post #1473943
Posted Tuesday, July 16, 2013 4:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 2, 2014 8:05 AM
Points: 283, Visits: 1,119
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
Post #1474054
Posted Tuesday, July 16, 2013 5:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:18 AM
Points: 1,304, Visits: 1,129
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
Post #1474078
Posted Wednesday, July 17, 2013 8:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:18 AM
Points: 1,304, Visits: 1,129
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
Post #1474642
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse