[SQL] How to access remote data in a Transact-SQL

I was facing an issue with a friend of mine who need to perform a select statement that selects data from a remote SQL instance.

All  I was thinking about is how to use something like connection string on the execute command to have a permission that could be used to connect to the remote server.

I found that I might use the following syntax:

 OPENDATASOURCE ( provider_name, init_string )

where,

provider_name
Is the name registered as the PROGID of the OLE DB provider used to access the data source. provider_name is a char data type, with no default value.

init_string
Is the connection string passed to the IDataInitialize interface of the destination provider. The provider string syntax is based on keyword-value pairs separated by semicolons, such as: 'keyword1=value; keyword2=value'. 

In order to use OPENDATASOURCE for connection I found that OPENDATASOURCE can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled. When these options are not set, the default behavior does not allow for ad hoc access.

The OPENDATASOURCE function can be used in the same Transact-SQL syntax locations as a linked-server name. Therefore, OPENDATASOURCE can be used as the first part of a four-part name that refers to a table or view name in a SELECT, INSERT, UPDATE, or DELETE statement, or to a remote stored procedure in an EXECUTE statement. When executing remote stored procedures, OPENDATASOURCE should refer to another instance of SQL Server. OPENDATASOURCE does not accept variables for its arguments.

Like the OPENROWSET function, OPENDATASOURCE should only reference OLE DB data sources that are accessed infrequently. Define a linked server for any data sources accessed more than several times. Neither OPENDATASOURCE nor OPENROWSET provide all the functionality of linked-server definitions, such as security management and the ability to query catalog information. All connection information, including passwords, must be provided every time that OPENDATASOURCE is called.

I used the following testing procedure and it works:

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
select *
from opendatasource('SQLOLEDB.1','Data Source=xxxxx;Password=xxxxx;User ID=xxxxx;Initial Catalog=DATABASE_NAME').[DATABASENAME].[dbo].[TABLE_NAME]

As you see, in order to enable 'Ad Hoc Distributed Queries', you have at first to enable 'show advanced options'.

Hope it could help :)