Linked server - Cross domain - Windows authentication only.

Question

Linked server - Cross domain - Windows authentication only.

NicHopper

SSCrazy Eights

Points: 9090
More actions
May 8, 2019 at 11:22 am

#3638236

Hi all,
I was looking for some advice on the best way to do this, we have our domain (A) and a parent companies domain (B).
For a long time now one of our applications running in our domain has been using a linked server also in domain A to get data from domain B - This was done using SQL authentication.
Now however they have made changes and require us to use a Windows account but I'm not sure how to go about this, I've tried some impersonation settings on the linked server (see image, black is domain B detail, Red is domain A).

The error on the server on domain B is
Login failed for user 'DomainB\User'. Reason: Attempting to use an NT account name with SQL Server Authentication. [CLIENT: 123.123.123.123]
Does anyone have an ideas on how to achieve this?
Thanks,
Nic
Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 7 posts - 1 through 6 (of 6 total)

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

HappyGeek SSCoach Points: 18729 More actions · Answer 1

Did this year's ago, it was way back and what was required was to set up cross domain trusts such that one domain was trusted and the other trusting.

...

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 2

Hi,

Thanks for the reply and when that is done (the trusting) will what I did work? Or do I need to do something else?

Thanks,

Nic

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

oogibah SSC Eights! Points: 827 More actions · Answer 3

Granted that your DNS is clean .. and has forwarders and your spns are correct, it should work once the domains have trust.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

I think you'll need to reconfigure your linked server object as well. The error message suggests that you are trying to force Windows credentials where it's expecting a SQL login. I can't remember exactly how you do it, but it may involve ticking the Impersonate box. Oh, and make sure you've got all the SPNs you need in place in order to make Kerberos work, or you'll suffer from the double-hop problem.

John

Sue_H SSC Guru Points: 90860 More actions · Answer 5

John Mitchell-245523 wrote:

I think you'll need to reconfigure your linked server object as well. The error message suggests that you are trying to force Windows credentials where it's expecting a SQL login. I can't remember exactly how you do it, but it may involve ticking the Impersonate box. Oh, and make sure you've got all the SPNs you need in place in order to make Kerberos work, or you'll suffer from the double-hop problem. John

Yup. You can't specify a windows account as the remote account when setting it up like the screenshot. You need to specify Impersonation, don't put in a remote login and password.

Sue

Andrey SSC Eights! Points: 840 More actions · Answer 6

Also, be aware that if you granted access to an AD group from another domain (despite trusts are in place), such AD group should be either Universal or Global type. Otherwise, your AD account (member of AD group) will not be authenticated.

To check that use xp_logininfo command in the following way on the target server:

exec xp_logininfo 'anotherdomain\ad_group','members'

If this returns nothing, it means that SQL server can't get members of the group and your account will not be authenticated.

I had such issue twice in the past and it was rather hard investigation.