Setting up a linked server to another server which isn't in a domain without using SQL authentication?

Question

Server A (SQL2005) is in our primary domain, but server B (SQL2000) is just in a windows workgroup. We are not allowed to join it to the domain, or bad things happen...

We also can't enable SQL authentication on server B.

We've got domain accounts for A, and matching local accounts on server B.

I can connect to B from my local PC or A using SSMS and a domain login, but I can't get the linked server to connect.

Any ideas how to do this?

Answer 1

Your SQL Server A will authenticate on the the linked-server connection according to normal linked-server authentication rules:

• by 'self-mapping', in which case delegation occurs and the Configuring Linked Servers for Delegation article applies. Constrained delegation cannot be configured with NT mirrored accounts (aka. as 'matching accounts').
• by explicit mapping, in which case SQL Authentication is used.

These are your two options and you impose restrictions that eliminate both options. You have to go back to the drawing board and review your requirements and constraints, since right now you have very explicitly put yourself in an impossible spot. some alternatives are:

• Make the machine B a new domain (a true domain) and have it trust the domain A
• Upgrade the machine B to SQL 2005 and use Service Broker to sync up the data, since certificate based authentication can cross any domain boundary.
• Allow SQL authentication on server B