Sudden failure of Kerberos delegation with linked servers

  • Scott Coleman

    One Orange Chip

    Points: 27416

    This is not limited to SQL 2019, my issue is that suddenly on 1/8 all the linked servers on various SQL servers, that use Windows authentication, stopped working due to delegation failure ("Login failed for NT AUTHORITY/ANONYMOUS LOGON").  Kerberos authentication works fine on all servers, but not delegation.

    This applies to a variety of SQL server versions, 2005 through 2016, across the whole domain.  It also affected SSRS reports and Tableau reports using Windows Auth data sources.

    My company has two domains with trust relationships.  In one domain delegation is not working anywhere.  In the other domain delegation is mostly working (only three broken links, on one server), even across the domain boundary.

    I can't find any Windows patches that were installed anywhere near when this started.  Our infrastructure can't push patches to all servers simultaneously in any event, so I can see how a server patch caused this.  One theory is that it could be something installed on a domain controller.

    One website I looked at said there was an update in July 2019 that disabled unconstrained delegation at AD forest boundaries by default.  All of our servers are set up with unconstrained delegation.  The problem with this explanation is that most of the broken links are with the same domain (do not cross a forest boundary), and in the other domain there are a number of links still working find, to servers in the problem domain.

    Any suggestions would be appreciated.

  • Site Owners

    SSC Guru

    Points: 80378

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Michael McCormick-464104

    Grasshopper

    Points: 11

    We are experiencing something very similar, here is our analysis so far:

    Problem: Users are intermittently prompted multiple times and ultimately fail when trying to upload using a custom built asp.net app that copies up a file and updates a table in SQL.  These folks are using a trusted domain machines and user account. This began happening in Spring of 2019 after working throughout 2018 and prior.

    Cause: Beginning in Spring 2019 Microsoft began pushing out an Update that sets the “CROSS_ORGANIZATION_NO_TGT_DELEGATION” flag to “Disabled”. This breaks the cross domain trust from NuVasive Domain Users/Machines to Impulse Domain SQL servers which causes the Double Hop Authentication error users receive. This explains why the issue started appearing in Q2 of 2019. By July of 2019 Microsoft pushed out KB4490425 which forces the flag to Disabled by default. It appears all DC’s have this KB installed except one which has not rebooted since July 2019 Updates and has a ‘pending restart’. THIS is where the intermittency comes from. When a user gets its ticket from the 'good' server the flag is still enabled and so the TGT Delegation is allowed. If a user’s ticket comes from any other DC, the Flag is Disabled and the TGT Delegation is not allowed which causes the double hop auth to fail.

    Solution: The proper solution is to enable Resource-Based Constrained delegation which is only available in Server 2012 and higher. This should be accomplished in the first half of 2020 as we rebuild servers.

    Workaround: To re-enable delegation across trusts and return to the original unsafe configuration until constrained or resource-based delegation can be enabled, set the EnableTGTDelegation flag to Yes.  Another work around is to do a 'run-as' using credentials from the domain on which the servers reside.  This eliminates the cross domain double hop auth since you are sending domain creds from the domain the server resides on.

     

    https://support.microsoft.com/en-us/help/4490425/updates-to-tgt-delegation-across-incoming-trusts-in-windows-server

    https://techcommunity.microsoft.com/t5/Core-Infrastructure-and-Security/Changes-to-Ticket-Granting-Ticket-TGT-Delegation-Across-Trusts/ba-p/440261

    https://github.com/vletoux/pingcastle/issues/9

Viewing 3 posts - 1 through 3 (of 3 total)

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