Oh double hop, you're so fun ... Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Question

Oh double hop, you're so fun ... Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Adam Bean

One Orange Chip

Points: 26631
More actions
May 3, 2017 at 10:31 am

#336717

I've fought this issue so many times over the years and perhaps I'm old and rusty, but I'm having a hard time finding resolution.
I've recently begun cutting over to brand new service accounts and now linked servers are failing.
- The engine service account has the ability to create and modify SPN's
- Validated that the two instances in question, have the proper SPN's in place
- Enabled Trust this user for delegation (never have needed to do this before)
- All NT calls are using Kerberos
Yet, when I attempt to use a linked server using current security context "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.".
What am I missing?
Thanks

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

If the accounts all have SPNs trusted for delegation and the auth_scheme in sys.dm_exec_connections is showing Kerberos, then another thing to check is the ports being used by the instances. If it's non-default ports, try specifying the port number for the linked server connections.

Sue

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

Sue_H - Wednesday, May 3, 2017 12:22 PM
If the accounts all have SPNs trusted for delegation and the auth_scheme in sys.dm_exec_connections is showing Kerberos, then another thing to check is the ports being used by the instances. If it's non-default ports, try specifying the port number for the linked server connections.
Sue

Thanks for the response Sue. Unfortunately, they're on 1433.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

This whole trust for delegation thing is the only thing standing out. Perhaps I've overlooked in the past; however, I don't ever remember having to modify this. Actually just checked in with my old job and it's not enabled.

For now, I set it to "Trust this user for delegation to any service (Keberos only), this should suffice yeah? I don't see this being the issue though.

I'm at a loss.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 4

Again, not something I've had to do in the past (3 different environments), but I just enabled delegation in AD on the machine name, and now it's working? Why wouldn't I have had to do that before? Do we have something in this environment that would conflicting that needs this?

Sue_H SSC Guru Points: 90860 More actions · Answer 5

Adam Bean - Wednesday, May 3, 2017 12:38 PM
This whole trust for delegation thing is the only thing standing out. Perhaps I've overlooked in the past; however, I don't ever remember having to modify this. Actually just checked in with my old job and it's not enabled.
For now, I set it to "Trust this user for delegation to any service (Keberos only), this should suffice yeah? I don't see this being the issue though.
I'm at a loss.

Microsoft has a tool for checking Kerberos configs for SQL Server. Only tried it once, on an instance that had no issues, just wanted to see. It might be worth checking to see if it can find any issues. Here is the link:
Microsoft Kerberos Configuration Manager for SQL Server

Otherwise, I used to use Kerbtray but the replacement now is klist. Command line tool on Windows Server, does most of the same as Kerbtray.

Sue

Sue_H SSC Guru Points: 90860 More actions · Answer 6

Adam Bean - Wednesday, May 3, 2017 12:58 PM
Again, not something I've had to do in the past (3 different environments), but I just enabled delegation in AD on the machine name, and now it's working? Why wouldn't I have had to do that before? Do we have something in this environment that would conflicting that needs this?

That is weird. I have always used the trusted for delegation but never on the machine name itself.
It could be that it's related to the service account security changes and the per service SID architecture. The virtual accounts go across the network as domain\servername$ but I thought the domain account for the service was used when going outside if it's own local parts of the network.

Sue

Adam Bean One Orange Chip Points: 26631 More actions · Answer 7

Sue_H - Wednesday, May 3, 2017 1:17 PM
Adam Bean - Wednesday, May 3, 2017 12:58 PM
Again, not something I've had to do in the past (3 different environments), but I just enabled delegation in AD on the machine name, and now it's working? Why wouldn't I have had to do that before? Do we have something in this environment that would conflicting that needs this?
That is weird. I have always used the trusted for delegation but never on the machine name itself.
It could be that it's related to the service account security changes and the per service SID architecture. The virtual accounts go across the network as domain\servername$ but I thought the domain account for the service was used when going outside if it's own local parts of the network.
Sue

Welp, after having my old place of employment setup the same scenario, they're failing too. So I .. may stand corrected that I've never needed this.

So I'm reading articles that say you need delegation on the account + the computer. Yet some are saying just the account, others just the computer. I'm in the middle of testing each scenario now, just delayed as I don't have domain admin. You're saying just on the machine eh?

Adam Bean One Orange Chip Points: 26631 More actions · Answer 8

So current test ... the service account does not have delegation permissions but both computers do, and I appear to be working just fine.

There's no way to auto-set this like how we setup read/write SPN perms on the service account is there?

Thanks for the help!

Sue_H SSC Guru Points: 90860 More actions · Answer 9

Adam Bean - Wednesday, May 3, 2017 1:21 PM
Sue_H - Wednesday, May 3, 2017 1:17 PM
Adam Bean - Wednesday, May 3, 2017 12:58 PM
Again, not something I've had to do in the past (3 different environments), but I just enabled delegation in AD on the machine name, and now it's working? Why wouldn't I have had to do that before? Do we have something in this environment that would conflicting that needs this?
That is weird. I have always used the trusted for delegation but never on the machine name itself.
It could be that it's related to the service account security changes and the per service SID architecture. The virtual accounts go across the network as domain\servername$ but I thought the domain account for the service was used when going outside if it's own local parts of the network.
Sue
Welp, after having my old place of employment setup the same scenario, they're failing too. So I .. may stand corrected that I've never needed this.
So I'm reading articles that say you need delegation on the account + the computer. Yet some are saying just the account, others just the computer. I'm in the middle of testing each scenario now, just delayed as I don't have domain admin. You're saying just on the machine eh?

I'm reading about it now...haven't found exactly but I suspect it very well could be that it's needed for both with the way that the service accounts work now.
The whole per service sid and what is used when can give you as much of a headache as reading the troubleshooting kerberos document.

Sue

Adam Bean One Orange Chip Points: 26631 More actions · Answer 10

Interesting. Well, so far ... I'm working like a champ with only having the delegation on the machines. I'll give it until tomorrow before I commit to this decision to ensure nothing stale/cached.

Sue_H SSC Guru Points: 90860 More actions · Answer 11

Adam Bean - Wednesday, May 3, 2017 1:30 PM
So current test ... the service account does not have delegation permissions but both computers do, and I appear to be working just fine.
There's no way to auto-set this like how we setup read/write SPN perms on the service account is there?
Thanks for the help!

In some scenarios, it's managed on it's own, no need to register SPNs but from what I can tell, that's only if you're using managed service accounts.
There possibly is policy that can be used to manage it but I don't know of a way to automate it off hand.

Sue

tihi Valued Member Points: 70 More actions · Answer 12

Adam Bean - Wednesday, May 3, 2017 10:31 AM
I've fought this issue so many times over the years and perhaps I'm old and rusty, but I'm having a hard time finding resolution.
I've recently begun cutting over to brand new service accounts and now linked servers are failing.
- The engine service account has the ability to create and modify SPN's
- Validated that the two instances in question, have the proper SPN's in place
- Enabled Trust this user for delegation (never have needed to do this before)
- All NT calls are using Kerberos
Yet, when I attempt to use a linked server using current security context "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.".
What am I missing?
Thanks

... you blow my mind 🙂
Could you please check your Linked server configuration and try this:
- If you are using Linked Server with Security option "Be made using the login's current security context" then your user or sql service account user must have login on the Linked Server
- If you are using Linked Server with Security option "Be made using this security context:" then that user must have login

Do maybe you trying execute report which is connecting through Linked Server?
That was my nightmare for some time last year 😉

adb2303 SSCertifiable Points: 7588 More actions · Answer 13

This doesn't sound right. Permitting delegation on the machine account suggests that the service is/was running as a local system account. I would use setspn to delete all the SPN's registered against the new account, old account and machine... then restart SQL Server and let SQL register the SPN's again cleanly. You can then go back into AD, find the service account and permit delegation to those services.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 14

adb2303 - Thursday, May 4, 2017 6:05 AM
This doesn't sound right. Permitting delegation on the machine account suggests that the service is/was running as a local system account. I would use setspn to delete all the SPN's registered against the new account, old account and machine... then restart SQL Server and let SQL register the SPN's again cleanly. You can then go back into AD, find the service account and permit delegation to those services.

I removed the service accounts delegation permissions yesterday and it continued to work. Yet this morning, it is now broken. Perhaps a caching issue.

So currently:
- SQL running as a domain account
- Domain account can create/modify SPN's
- Machines can delegate

So I'm back to the question from earlier, do I really need delegation on BOTH the account and machine?