Removing the Linked Server 2 hop Limitation

Question

Removing the Linked Server 2 hop Limitation

Slevin

Mr or Mrs. 500

Points: 574
More actions
December 16, 2013 at 10:36 pm

#301468

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

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

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

shayma.ahmad SSC Rookie Points: 34 More actions · Answer 1

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 SSC Rookie Points: 25 More actions · Answer 2

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 SSC Guru Points: 114642 More actions · Answer 3

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 Hall of Fame Points: 3112 More actions · Answer 4

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 SSC Journeyman Points: 75 More actions · Answer 5

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 Mr or Mrs. 500 Points: 574 More actions · Answer 6

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 Mr or Mrs. 500 Points: 574 More actions · Answer 7

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 SSC Guru Points: 114642 More actions · Answer 8

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 SSC Journeyman Points: 75 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

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)

Slevin Mr or Mrs. 500 Points: 574 More actions · Answer 11

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

William The Last Man SSC Journeyman Points: 75 More actions · Answer 12

William The Last Man

SSC Journeyman

Points: 75

December 17, 2013 at 8:38 am

#1674103

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

Slevin Mr or Mrs. 500 Points: 574 More actions · Answer 13

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

Slevin Mr or Mrs. 500 Points: 574 More actions · Answer 14

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