Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Removing the Linked Server 2 hop Limitation


Removing the Linked Server 2 hop Limitation

Author
Message
Slevin
Slevin
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 385
Comments posted to this topic are about the item Removing the Linked Server 2 hop Limitation
shayma.ahmad
shayma.ahmad
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 14
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?
lkoduri-1111329
lkoduri-1111329
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 11
Thank you for sharing it. Great way to address this problem. I hope it helps lot of company DBA's and developers
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (7.5K reputation)

Group: Moderators
Points: 7534 Visits: 1917
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
MiguelSQL
MiguelSQL
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 1099
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
William The Last Man
William The Last Man
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 314
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..
:-)
Slevin
Slevin
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 385
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
Slevin
Slevin
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 385
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
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (7.5K reputation)

Group: Moderators
Points: 7534 Visits: 1917
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
William The Last Man
William The Last Man
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 314
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search