SQL Servers: Linked-servers without linking servers?

Question

Both my local (and remote) SQL SERVER 2005 administrators claim that "allowing linked-servers is a security issue" and forbid their use here. (Huh?)

Anyway, is there a way to do something similar WITHOUT linked-servers?

SELECT *
FROM LOCAL_SERVER.MyDatabase.dbo.MyTable AS t1
INNER JOIN REMOTE_SERVER.MyDatabase.dbo.MyTable AS t2
ON t1.MyField = t2.MyField

Ask your administrators to replicate or mirror the DB to your local server? — StuartLC, Aug 23 '10 at 20:36
If they're going to forbid something, they should be prepared to recommend and support alternatives. — Joe Stefanelli, Aug 23 '10 at 20:39
@Joe Stefanelli, boy are you right there. I'd make them come up with the alternative and if they didn't I'd escalate the problem. — HLGEM, Aug 23 '10 at 21:06

score 8 · Answer 1 · answered Aug 23 '10 at 20:34

You could use OPENROWSET, which'll require the connection info, username & password...

While I understand that the administrator believes that having an always-on connection to their data is risky, that's why you lock down the account. OPENROWSET means including the connection info in plain text.

score 4 · Answer 2 · answered Aug 23 '10 at 20:33

4

yes you can use OPENDATASOURCE but ad-hoc queries have to be enabled

Opendatasource: Provides ad hoc connection information as part of a four-part object name without using a linked server name.

answered Aug 23 '10 at 20:33

SQLMenace

132,095
25
206
225

+1: I'm trying to find the difference between OPENDATASOURCE & OPENROWSET - very little, just OPENROWSET supports bulk operations... – OMG Ponies Aug 23 '10 at 20:47
Yes OPENROWSET added bulk for XML in 2005 IIRC – SQLMenace Aug 23 '10 at 20:55

SQL Servers: Linked-servers without linking servers?

2 Answers2