Using OPENROWSET instead of a Linked Server
Published: 30th June 2013
Ever been writing some code and found that you need some data from another SQL Server instance? You have security access to the other instance so what do you do?

Well you could easily use a linked server to obtain it, but what if there is no linked server set up and you lack the rights to (or maybe there’s a company rule forbidding) setting one up? Or maybe you just don’t want to use a linked server as these can have pitfalls (I’ve encountered a few).

This is where OPENROWSET comes to the fore. It provides a tidy, simple, and very effective way to obtain data across instances without requiring a linked server.

There are a couple of variations to the most commonly used versions of this command which you will need to know about but they will all be covered as we go.

Windows Authentication

This is the most common and easiest usage of OPENROWSET.

There are two methods we can use to connect to another instance, a SQL Native Client driver (SQLNCLI), or OLE DB Provider for ODBC and the SQL Server ODBC driver (MSDASQL), and as we are using our Windows Login to connect to both servers then this means we can use a “trusted connection”:

select a.* from openrowset('SQLNCLI', 'Server=MYINSTANCE;Trusted_Connection=yes;', 'select * from sys.databases') as a

select a.* from openrowset('MSDASQL', 'Driver={SQL SERVER}; Server=MYINSTANCE;Trusted_Connection=yes;', 'select * from sys.databases') as a

SQL Server Authentication

Not everyone uses Windows Authentication for their databases or server access or maybe the target server you want to access only allows SQL Server Authentication… in this case we simply need to remove the trusted connection part of the string and replace it with a userID and password:

select a.* from openrowset('SQLNCLI', 'Server=MYINSTANCE;UID=mySQLUser; PWD=*******;', 'select * from sys.databases') as a

select a.* from openrowset('MSDASQL', 'Driver={SQL SERVER}; Server=MYINSTANCE;UID=mySQLUser; PWD=*******;', 'select * from sys.databases') as a

Additional Notes around usage

Note that if a linked server is not present on your instance to the target instance then SQLNCLI will fail when using SQL Server Authentication, however it will work with Windows Authentication. Therefore if using SQL Server Authentication I would advise using the MSDASQL approach.

Secondly, be aware that you can pass multiple statements in an OPENROWSET command, but only the first set of results will be returned by the command.

Lastly, a good deal of people like to write dynamic SQL and there’s nothing wrong with this as it’s incredibly powerful and can make life much easier, however, if you wish to use this with OPENROWSET then there is a slight catch. OPENROWSET will not allow you to pass a variable to it. Therefore if you wish to use dynamic SQL with OPENROWSET you will need to include it as part of the dynamic code itself. For example:

declare @top int = 10, @sql nvarchar(max)

select @sql = 'select a.* from openrowset(''SQLNCLI'', ''Server=MYINSTANCE;Trusted_Connection=yes;'', ''select top ' + convert(varchar, @top) + ' * from sys.databases'') as a'

exec sp_executeSQL @sql

select @sql = 'select a.* from openrowset(''MSDASQL'', ''Driver={SQL SERVER}; Server=MYINSTANCE;UID=mySQLUser; PWD=*******;'', ''select * from sys.databases'') as a'

exec sp_executeSQL @sql

Hopefully that will be enough to get you comfortably using this where needed.
12/01/2015 04:13:00
Thank you for article. Helped much to sort out the use of OPENROWSET method.
Jatan Porecha
21/01/2016 05:00:00
Thanks, it really helped me to nail down the issue with my conn string while using SQLNCLI.
NB: Comments will only appear once they have been moderated.