0

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?

CodeRedick
  • 7,346
  • 7
  • 46
  • 72
  • Might just be easier to backup the 2000 DB(s), and restore them to the 2005 instance? – OMG Ponies May 31 '10 at 17:38
  • It's actually the new version of our phone system we just installed... so that's not much of an option. We could replicate some of the data out to another server, but we pull some real time stats from the DB as well that we'd lose access to. – CodeRedick May 31 '10 at 17:47
  • 1
    This would probably best be asked in Serverfault – Waleed Al-Balooshi May 31 '10 at 18:34

2 Answers2

4

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
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Unfortunately I'm not the one imposing the restrictions... :( – CodeRedick May 31 '10 at 19:08
  • I understand. You can though take a print of the articles I linked, which are official product documentation, and use them to make a case to those that make the decision. – Remus Rusanu May 31 '10 at 19:10
  • Nice clear, detailed answer--as always--and backed up with evidence. Your xp and input on these boards is invaluable. Thank you – Scot Hauder May 31 '10 at 23:36
  • In the end the guy managing the other system found updated documentation saying SQL Authentication was ok as of the most recent patch. Yay for wasting 6 hours on that on a holiday! :( – CodeRedick Jun 02 '10 at 19:23
0

Try this:

Start the SQL Server Management Studio using the following at a command prompt:

runas /netonly /user:targetdomain\targetuser "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe"

Substitute targetdomain above with the AD domain that the remote SQL server is in, and substitute targetuser with the AD username that has the SQL permissions. Also, the path to the SQL Management Studio above may be different on your computer - substitute your path accordingly.

It will prompt for the AD user password. The password must be typed, can't easily be scripted (not going into that now, there's some vbscript solutions to get around the password thing).

What the above does is runs your local management studio program "as" an AD user on the target domain. Now you can create a regular "SQL Server" linked server using the current login's security context...But we're fooling it into thinking you are on the remote domain.

pretty slick, eh!

Has some obvious drawbacks, BUT it gets the job done.

--Eric

Michael
  • 3,093
  • 7
  • 39
  • 83
Eric
  • 1
  • Good idea! You could edit your post with formatting for file paths and numbering for your step list to help make it more readable. – Jake Reece Apr 23 '18 at 20:59