Removing the Linked Server 2 hop Limitation

  • Comments posted to this topic are about the item Removing the Linked Server 2 hop Limitation

  • Thank you for posting about Linked Servers,

    any tips when transferring an excel linked server from sql2005 - 32bit that have the Jet 4 provider to a sql2008-64bit environment that doesn't have the JET or the Microsoft ACE provider listed?

  • Thank you for sharing it. Great way to address this problem. I hope it helps lot of company DBA's and developers

  • Please use constrained delegation (selecting the 3rd option) if configuring Kerberos delegation. It's considered a significant security risk to use unconstrained delegation (where any server can be delegated).

    K. Brian Kelley
    @kbriankelley

  • Wow. This would have been so useful two weeks ago.

    I hadbthe very same issue and took me a while to troubleshoot (as in googleing), when kerberos broke.

    No idea how it broke, but the article does a great job explanining it.

    I would only add that when testing the type of authentication, do it remotely, not connected locally to SQL, so you are connecting using the same protocol

    Thanks

  • Another issue I want to share is that one user (domain\user1) get the error after trying to access the linked server:

    "Login Failed for user 'NT Authority\ANONYMOUS' LOGON"

    But at the same time, another user (domain\user2) can access linked server without any issue. Both accounts are in same domain.

    We had tried . The user (domain\user2) tried to use the PC from the user (domain\user2). Same error received.

    "Login Failed for user 'NT Authority\ANONYMOUS' LOGON"

    Any idea?

    We already tried with purging tickets..

    🙂

  • K. Brian Kelley (12/17/2013)


    Please use constrained delegation (selecting the 3rd option) if configuring Kerberos delegation. It's considered a significant security risk to use unconstrained delegation (where any server can be delegated).

    Thank you for the feedback! Would you mind posting a link that describes some of the security risks when leaving delegation for the service account open to "trust for delegation to any service". I agree that we should limit it to only the use case you are solving for, but I wasn't able to get down to the real risks by leaving that option open.

    Derek

  • William The Last Man (12/17/2013)


    Another issue I want to share is that one user (domain\user1) get the error after trying to access the linked server:

    "Login Failed for user 'NT Authority\ANONYMOUS' LOGON"

    But at the same time, another user (domain\user2) can access linked server without any issue. Both accounts are in same domain.

    We had tried . The user (domain\user2) tried to use the PC from the user (domain\user2). Same error received.

    "Login Failed for user 'NT Authority\ANONYMOUS' LOGON"

    Any idea?

    We already tried with purging tickets..

    🙂

    Have you verified that both user accounts are connecting using Kerberos authentication? Has user2 tried to log into user1's computer to do this? There are a ton of factors that can play a part. For instance, if user1 and user2 are on different subnets separated by a firewall. Are the SPN's created? Do user1 and user2 both have the same permissions\rights?

    Derek

  • Slevin (12/17/2013)


    K. Brian Kelley (12/17/2013)


    Please use constrained delegation (selecting the 3rd option) if configuring Kerberos delegation. It's considered a significant security risk to use unconstrained delegation (where any server can be delegated).

    Thank you for the feedback! Would you mind posting a link that describes some of the security risks when leaving delegation for the service account open to "trust for delegation to any service". I agree that we should limit it to only the use case you are solving for, but I wasn't able to get down to the real risks by leaving that option open.

    Derek

    In short, constrained delegation basically ensures that SQL Server can only connect to SQL Server, if that's what you set up. If you don't, it means SQL Server can connect to any resource as that user. That's the inherent problem. A bit more, but from the IIS perspective:

    http://windowsitpro.com/security/how-windows-server-2012-eases-pain-kerberos-constrained-delegation-part-1

    Therefore, if you're following the Principle of Least Privilege, an important security rule, then you use constrained delegation unless you have to cross forests. In that case, you can't do any better than unconstrained, but this type of setup is much rarer than the cases where you're trying to grant access to intra-forest resources via delegation.

    K. Brian Kelley
    @kbriankelley

  • Hi Derek,

    Have you verified that both user accounts are connecting using Kerberos authentication?

    [William]. No. It's different authentications using for these TWO accounts connections. NTLM; KERBEROS.

    Has user2 tried to log into user1's computer to do this? There are a ton of factors that can play a part. For instance, if user1 and user2 are on different subnets separated by a firewall. [William] Not yet checked. But no firewall.

    Are the SPN's created? [William] Yes. SPN's are created.

    Do user1 and user2 both have the same permissions\rights? [William] Yes. Both have same access rights.

    Derek

  • Between the content of this article and the addition recommendations from Mr. Kelly, this is a definite keeper. Thank you both for taking the time to help solve this common problem with such clarity and concern for security.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • William The Last Man (12/17/2013)


    Hi Derek,

    Have you verified that both user accounts are connecting using Kerberos authentication?

    [William]. No. It's different authentications using for these TWO accounts connections. NTLM; KERBEROS.

    Has user2 tried to log into user1's computer to do this? There are a ton of factors that can play a part. For instance, if user1 and user2 are on different subnets separated by a firewall. [William] Not yet checked. But no firewall.

    Are the SPN's created? [William] Yes. SPN's are created.

    Do user1 and user2 both have the same permissions\rights? [William] Yes. Both have same access rights.

    Derek

    Is the user2 account using NTLM then?

    Derek

  • Yes. The user2 account is using NTLM on user1's PC.

  • Jeff Moden (12/17/2013)


    Between the content of this article and the addition recommendations from Mr. Kelly, this is a definite keeper. Thank you both for taking the time to help solve this common problem with such clarity and concern for security.

    Thank you Jeff! Its great to have an opportunity to help the community.

    Derek

  • William The Last Man (12/17/2013)


    Yes. The user2 account is using NTLM on user1's PC.

    Thanks William, this is a limitation of NTLM as described in the article. So it seems like we identified the problem. You may need to involve your system administrators at this point to look at the AD accounts. It almost seems like user2 cannot read the SPNs. Are the SPN's created under the service account and not User1? Is delegation enabled? You will want to compare like for like for your situation and the article. It is pretty specific, but its for a reason.

    Do any other users have this issue? For instance, is user1 always ok, but all other users are experiencing this? Or maybe all users are ok except user2?

    Derek

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

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