Using OPENROWSET to Access Remote Data in SQL Server

This lesson explains how the OPENROWSET function can be used to access remote data sources directly from a Transact-SQL statement. The focus is on understanding what OPENROWSET is designed to do, when it is appropriate to use, and why its role in modern SQL Server environments is more limited than in earlier versions.

OPENROWSET is an ad hoc data access mechanism. It allows SQL Server to execute a one-time query against a remote OLE DB data source without defining a permanent linked server. The result set returned by OPENROWSET behaves like a table expression and can be used in a FROM clause just like a local table or view.

Basic Concept

At a high level, OPENROWSET establishes a connection to a remote provider, executes a query on that provider, and returns the results to SQL Server. No metadata or connection information is retained after execution.

SELECT *
FROM OPENROWSET(
  'provider_name',
  'datasource;user;password',
  'remote_query'
);

The function accepts three logical components:

• Provider – The OLE DB provider used to access the remote source.
• Connection information – Server name and authentication details.
• Remote query – The SQL statement executed on the remote system.

Choosing the Correct OLE DB Provider

For SQL Server connections, use MSOLEDBSQL (Microsoft OLE DB Driver for SQL Server), which is the current recommended provider. Earlier providers such as SQLNCLI (SQL Server Native Client) have been deprecated since SQL Server 2012 and should not be used in new development.

Practical Example

The following example demonstrates accessing a remote SQL Server instance using Windows authentication. The query retrieves selected columns from a remote table and exposes them as a rowset in the local query.

SELECT r.*
FROM OPENROWSET(
  'MSOLEDBSQL',
  'Server=RemoteServerName;Trusted_Connection=yes;',
  'SELECT col1, col2 FROM RemoteDatabase.dbo.RemoteTable'
) AS r;

Because the result is aliased, it can participate in joins, filters, and aggregations just like a local table.

Alternative Syntax Using Table Names

Instead of specifying a query, OPENROWSET can reference a table or view directly by name:

SELECT *
FROM OPENROWSET(
  'MSOLEDBSQL',
  'Server=RemoteServerName;Database=RemoteDatabase;Trusted_Connection=yes;',
  RemoteTable
) AS r;

This approach is simpler for accessing entire tables, but the query-based syntax offers more flexibility for filtering and transforming data at the source.

Using OPENROWSET with BULK for File Operations

One of the most common legitimate uses of OPENROWSET in modern SQL Server is importing file data using the BULK option. This allows direct reading of file contents without requiring BULK INSERT or an external tool.

-- Import entire file as a single large text object
SELECT BulkColumn
FROM OPENROWSET(
  BULK 'C:\data\file.txt',
  SINGLE_BLOB
) AS DataSource;

-- Import CSV data with format specification
SELECT *
FROM OPENROWSET(
  BULK 'C:\data\employees.csv',
  FORMATFILE = 'C:\formats\employees.fmt'

) AS EmployeeData;

The BULK option does not require enabling Ad Hoc Distributed Queries, making it a more secure approach for file-based data loading operations.

Configuration Requirements

By default, SQL Server restricts ad hoc distributed queries. To use OPENROWSET for remote data access, the Ad Hoc Distributed Queries option must be enabled at the server level.

sp_configure 'show advanced options', 1;
RECONFIGURE;

sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

This setting is intentionally disabled in many environments because it broadens the server's external access surface. Enabling it should be a deliberate administrative decision.

Security and Performance Considerations

From a security standpoint, embedding credentials directly in queries is strongly discouraged. Integrated security is preferred, and permissions should be tightly controlled so only authorized users can execute ad hoc remote queries.

From a performance perspective, OPENROWSET establishes a new connection each time it is executed. This makes it inefficient for repeated or high-volume access. For stable, recurring integrations, a linked server or modern data integration features are typically more appropriate.

Is OPENROWSET Still Used in Practice?

In modern SQL Server deployments, OPENROWSET is best viewed as a specialized tool rather than a general-purpose integration mechanism. It is still supported and occasionally useful for:

• One-time data inspections or troubleshooting scenarios
• Controlled administrative tasks
• File import operations using the BULK option
• Environments where linked servers are not permitted

However, for most production workloads, organizations favor linked servers, ETL pipelines, replication, or cloud-native data integration services. These approaches provide stronger security boundaries, better performance characteristics, and clearer operational governance.

The lesson objective, therefore, is not only to understand how OPENROWSET works, but to recognize when its use is appropriate—and when a more modern alternative is the better architectural choice.