SQL Server: is it possible to get data from another SQL server without setting linked server?

Question

I need to do the following query (for example):

SELECT c1.CustomerName FROM Customer as c1
INNER JOIN [ExternalServer].[Database].[dbo].[Customer] as c2
ON c2.RefId = c1.RefId

For some security reason my client doesn't allow me to create a linked server. The user under whom I execute this query has access to both tables. Is it possible to make it work without using linked server? Thanks.

Why doesn't he allow you to create a linked server? Would it be OK with him if he created the linked server and you only used it? — Lasse V. Karlsen
– Lasse V. Karlsen, Commented Jul 30, 2010 at 15:59
@Lasse - I know there are some applications where you just CAN'T have linked servers. My company works with health care data, which is tightly tightly regulated. We also have xp_cmdshell disabled, no linked servers, etc. — JNK
– JNK, Commented Jul 30, 2010 at 16:10
You haven't answered my question. There is nothing technical that stops you or your client from creating linked servers. I'm asking why that is not allowed? (from your comment I think I know the answer but I want you to acknowledge it) — Lasse V. Karlsen
– Lasse V. Karlsen, Commented Jul 30, 2010 at 16:11
Talk to your DBA then. To me the question sounds like this: The servers have been locked down and a heap of restrictions have been placed on them. One of those restrictions is that you're not allowed to set up linked servers. The question here, asking basically "is there other ways, other than linked servers to ...", which sounds to me that the question is basically "I know I'm not supposed to do this, but how can I circumvent our security". Your first task is to ask those that placed those restrictions in play in the first place. If they say "NO", it doesn't matter if there are other ways. — Lasse V. Karlsen
– Lasse V. Karlsen, Commented Jul 30, 2010 at 16:19
And the only built-in method is through linked servers. With newer versions of SQL Server you can add CLR-based stored procedures, that can do all kinds of magic, but I doubt any of that is relevant if the security is paramount. — Lasse V. Karlsen
– Lasse V. Karlsen, Commented Jul 30, 2010 at 16:20

OMG Ponies · Accepted Answer · 2010-07-30 16:10:16Z

5

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

While I understand that the client 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.

answered Jul 30, 2010 at 16:10

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lasse V. Karlsen Over a year ago

Which is basically just a temporary linked server.

OMG Ponies Over a year ago

@Lasse V. Karlsen: By that logic, any connection string and username/password is a linked server. I'd prefer that Linked Servers be used, but the OP is clear about the client not allowing it.

andr111 Over a year ago

thanks a lot for suggestion. I will try it. Although, it needs additional configuration that, of course, needs additional permissions which I'm not sure my client can give me.

dave · Accepted Answer · 2010-07-30 23:22:14Z

'Linked Server' is a very specific thing -- basically, a permanent connection between servers. I can think of all sorts of reasons not to want that, while at the same time having no problem with folks writing queries that combine data from the two different data sources.

Anyway, depending on your requirement -- if this is just for ad hoc querying, OPENROWSET is good if inside of SQL-Server, or if you want to do this in MS Access, just link to the two tables, and your Access query won't care that one comes from one server, and one comes from another.

Alternatively, with a web or windows front-end, you could indpendently query each table into a data object, and then build a separate query on top of that.

Http Endpoints...

WebServices...

There's a million ways. I wouldn't be so quick to assume, as @Lasse suggests, that any form of 'linking' this data together would make you some kind of rougue data linker.

Collectives™ on Stack Overflow

SQL Server: is it possible to get data from another SQL server without setting linked server?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related