A remote query creates both a data path and an authentication path. Choosing OPENROWSET or a linked server requires reviewing those paths before a convenient SELECT becomes a permanent dependency.
Choose Between OPENROWSET and a Linked Server
The ad hoc OLE DB form supplies connection information and a remote query within one statement. It does not require a persistent linked-server definition. A linked server stores a named connection and associated settings for repeated use. Both still depend on a supported installed provider, connectivity, and an accepted remote identity.
This article concerns one SQL Server querying another SQL Server. The examples use placeholder names RemoteSql and RemoteReporting and a hypothetical Reporting database. Replace them only with reviewed destinations. The examples read selected columns from an assumed existing remote Orders table; they are not setup scripts for an unknown server.
I choose the pattern from the workload and security contract. Occasional inspection and a recurring application dependency have different ownership requirements. A query that works under an administrator's interactive login can fail under a scheduled job or service identity. Define the real caller before treating a successful test as deployment readiness.
Inspect OPENROWSET Access Before Enabling It
The provider-based ad hoc query path requires the Ad Hoc Distributed Queries setting and suitable provider configuration. Read the current value first. Enabling this option changes an instance-wide capability, so use an approved configuration review instead of adding the change silently to every remote-query example.
SELECT name,value,value_in_use
FROM sys.configurations
WHERE name IN (N'Ad Hoc Distributed Queries',N'show advanced options');If the approved plan requires enabling it, use sp_configure with the option name and value one, followed by RECONFIGURE. Record the original advanced-options and ad hoc values so an intentional temporary change can restore the accepted state afterward. A disabled setting is not a reason to weaken the instance policy through another hidden path.
An OPENROWSET request relies on that approved instance capability and provider policy. Provider-level restrictions can also prevent ad hoc access. Have the administrator validate the installed provider and its supported settings. Keep that investigation separate from query text troubleshooting. A connection failure caused by policy does not demonstrate that the remote SELECT or its table definition is wrong.
Use Integrated Authentication and Encryption With OPENROWSET
The following statement uses Windows integrated authentication and explicitly requests encryption with certificate validation. It avoids putting a reusable password in the query text. The registered provider name and accepted connection-string syntax must match the installed driver version. Until an administrator enables Ad Hoc Distributed Queries, this statement stops with error 15281.
SELECT r.OrderID,r.OrderDate
FROM OPENROWSET
(
'MSOLEDBSQL',
'Server=RemoteSql;Trusted_Connection=Yes;Encrypt=Yes;TrustServerCertificate=No;',
'SELECT OrderID,OrderDate FROM Reporting.dbo.Orders WHERE OrderDate >= ''2026-09-01'''
) AS r;An OPENROWSET statement still needs an accepted authentication identity and trusted certificate. Verify the remote certificate chain and server-name matching. SQL Server 2025 uses the newer OLE DB driver behavior for linked-server connections, making explicit encryption and valid certificate planning particularly relevant. Do not resolve a trust failure by casually disabling validation. Provision the accepted certificate and connection identity instead.
Integrated authentication can require delegation when a caller's identity must cross another server hop. A local interactive success does not prove that the actual job identity can make the same connection. Test the supported authentication path without granting a shared powerful identity merely to make the demonstration succeed.
Inspect Existing Linked Servers and Mappings
Review the persistent connections before creating another one. The server catalog describes provider and access settings; the linked-login catalog describes caller mappings. A wildcard local principal represents a broad mapping, while an explicit local principal narrows the caller population. Metadata visibility can limit what your review identity sees.
SELECT s.name AS LinkedServerName,s.provider,s.data_source,
s.is_data_access_enabled,s.is_rpc_out_enabled,
l.local_principal_id,
CASE WHEN l.local_principal_id=0 THEN N'All local logins'
ELSE p.name END AS LocalPrincipal,
l.uses_self_credential,l.remote_name,l.modify_date
FROM sys.servers AS s
LEFT JOIN sys.linked_logins AS l ON l.server_id=s.server_id
LEFT JOIN sys.server_principals AS p
ON p.principal_id=l.local_principal_id
WHERE s.is_linked=1
ORDER BY s.name,l.local_principal_id;On SQL Server 2022 and later, the mapping review requires appropriate server security-state permission. The catalog does not expose the remote password. Do not attempt to retrieve it for an inventory report. Record the destination, allowed callers, credential model, and remote permission scope through the accepted configuration process.
Avoid a Powerful Catch-All Remote Login
Mapping every local caller to one remote privileged identity collapses otherwise distinct permission boundaries. A user with narrow local access can then receive broader remote capabilities through the shared mapping. The connection's friendly name does not restrict what its mapped account can do on the remote server.
Prefer explicit permitted callers and a remote identity with the minimum accepted data permissions. Review default mappings as part of creation and remove or replace them through the approved setup plan where necessary. Restrict remote procedure execution separately when the workload only needs distributed reads.
I test both an allowed caller and a caller that should be denied. A configuration that works for the allowed account is only half the permission test. Also check ownership chains, scheduled-job context, and any application impersonation that changes the effective identity. Keep denied access visible as an expected result rather than treating every rejection as a defect.
Verify Where the Filtering Happens
A four-part query uses the linked-server name as its first component. The local optimizer and provider determine which work can be pushed to the remote side. Do not assume a WHERE clause's presence guarantees a small remote transfer. Inspect the actual distributed plan and the remote work where permitted. This query and the OPENQUERY example below need an existing linked server named RemoteReporting; without one they stop with error 7202.
SELECT OrderID,OrderDate
FROM [RemoteReporting].[Reporting].[dbo].[Orders]
WHERE OrderDate >= CONVERT(date,'2026-09-01');Functions, joins to local data, conversions, and provider capabilities can change pushdown behavior. A remotely filtered result is different from shipping many rows locally and filtering afterward. For large sources, that distinction affects network traffic, memory, and elapsed time even when the final result contains few rows.
Use Pass-Through Queries for an Explicit Remote Shape
OPENQUERY sends its literal query to the linked server for execution. Put the selective predicate inside that remote query when the intended contract is remote filtering. An additional local predicate outside it is a separate step and does not automatically rewrite the submitted remote text.
SELECT r.OrderID,r.OrderDate
FROM OPENQUERY
(
[RemoteReporting],
'SELECT OrderID,OrderDate FROM Reporting.dbo.Orders WHERE OrderDate >= ''2026-09-01'''
) AS r;The literal-query interface has practical limitations, including its query-text length and lack of direct variable arguments. Do not solve parameterization by concatenating untrusted text into a remote command. Choose a reviewed remote procedure or another supported parameterized design when the workload needs varying inputs.
Validate the Whole Dependency
Which identity, remote workload, and connection settings will the production request actually use? Test that exact path with a bounded query and representative permissions. Include certificate validation, remote filtering, timeout behavior, and failure handling in the review. A quick administrator SELECT cannot validate those application conditions.
Use the ad hoc form for an accepted one-time pattern and a linked server for an owned persistent dependency where appropriate. Keep configuration evidence and caller restrictions with the connection. Remote data access is ready when both the query and its permission path have a clear, verified owner.
Related reading on this blog: Linked Servers and What Goes Wrong With Them and FIX: Msg 15281: SQL Server Blocked Access to STATEMENT 'OpenRowset/OpenDatasource' of Component 'Ad Hoc Distributed Queries'.
A remote connection is not just a query convenience, it is a persistent or ad hoc permission path that needs deliberate control.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
