Search code examples
sqlsql-servert-sqllinked-server

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

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.

Solution

  • 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.