SQL Server Linked Server: Setup, Security, Queries and the SQL Server 2025 Change

With Oracle, OPENQUERY is usually the better choice. The query runs in Oracle’s own dialect and Oracle does the filtering, while four-part names leave SQL Server to translate the query and map data types, which is where most Oracle linked server problems start.

PostgreSQL, MySQL and Other ODBC Sources

Microsoft doesn’t ship an OLE DB provider for PostgreSQL or MySQL. The usual route is the vendor’s ODBC driver, for example the official PostgreSQL ODBC driver (psqlODBC) or MySQL Connector/ODBC, used through MSDASQL. Create a System DSN in the 64-bit ODBC Data Source Administrator on the SQL Server machine, then point the linked server at it:

-- System DSN "PG_CRM" created with the PostgreSQL ODBC driver (64-bit)
EXEC sys.sp_addlinkedserver
     @server     = N'PGCRM',
     @srvproduct = N'PostgreSQL',
     @provider   = N'MSDASQL',
     @datasrc    = N'PG_CRM';

-- Map logins as shown above, then query in PostgreSQL's own dialect
SELECT *
FROM OPENQUERY(PGCRM,
     'SELECT customer_id, email, created_at
      FROM crm.customers
      WHERE created_at >= ''2026-01-01''');

Prefer a System DSN in @datasrc over a full ODBC connection string in @provstr. Microsoft documents a known issue in SQL Server 2022 (from CU 24) and SQL Server 2025 where MSDASQL linked servers that use a provider string fail with error 7416, “Access to the remote server is denied because no login-mapping exists”, for logins that aren’t sysadmin.

Third-Party and Custom Providers

Commercial and custom OLE DB providers or ODBC drivers can be used for systems such as DB2, SAP HANA, cloud data warehouses or in-house APIs. SQL Server works with any OLE DB provider that implements the required interfaces, but Microsoft tests only against its own default provider, so behavior and support come from the provider’s vendor. How SQL Server uses a provider is controlled per provider in SSMS under Server Objects → Linked Servers → Providers:

• Allow inprocess: loads the provider inside the SQL Server process. Some providers need it, but by default providers run out of process, which protects SQL Server from errors in the provider. Enable it only for providers you trust and have tested, because a provider crash in process affects the whole instance.
• Dynamic parameter and Nested queries: let SQL Server send parameterized and nested queries to the provider, which can push more work to the remote side. Enable them only if the provider supports them.
• Supports ‘Like’ operator: lets LIKE predicates be evaluated remotely.
• Disallow ad hoc access: blocks OPENROWSET and OPENDATASOURCE against the provider, so it can only be used through defined linked servers.

For non-SQL Server sources, also set the collation name server option when the source uses a single collation that matches a SQL Server collation, so string comparisons behave predictably.

When PolyBase Is the Better Fit

If you only need to read from Oracle, Teradata, MongoDB or a generic ODBC source, PolyBase is worth comparing. Since SQL Server 2019, it exposes these sources as external tables with T-SQL, without a linked server or provider options to manage. It’s read-only for these sources and needs the PolyBase Query Service installed; generic ODBC was Windows-only until SQL Server 2025 added Linux support.

SQL Server 2025: The Encryption Change That Breaks Linked Servers

In SQL Server 2025, MSOLEDBSQL uses version 19 of the OLE DB driver, which adds TDS 8.0 and TLS 1.3 support. It also changes a default: if the linked server doesn’t specify Encrypt, the connection uses Encrypt=Mandatory and requires a valid certificate on the remote server. Self-signed certificates aren’t accepted in that mode.

After an upgrade, linked servers that worked for years can suddenly fail to connect. You have four ways forward, from best to worst:

• Install a CA-signed certificate on the remote server that matches its FQDN, and keep Encrypt=Mandatory or use Encrypt=Strict for TDS 8.0.
• Recreate the linked server with Encrypt=Optional in the provider string. It connects, but without encryption.
• Add TrustServerCertificate=Yes. Microsoft supports it but doesn’t recommend it for production, because it disables certificate validation.
• Enable trace flag 17600 to keep OLE DB 18 behavior if you can’t change the linked server definitions yet. Treat this as temporary.

Linked Server Security: Login Mappings

When SQL Server connects to the remote side, it has to log in as someone. The Security page in SSMS and sp_addlinkedsrvlogin control who that is:

Credentials stored in a mapping are saved in the server definition, so the remote login should have only the permissions the integration really needs. Linked servers belong in the same permission review as privileged users; see our SQL Server security guide.

Which Security Model Should a Linked Server Use?

There is no single right answer; the right model depends on who connects and how many network hops sit between the client and the remote server. Use this table as a starting point:

Least-privilege checklist (Field practice, supported by Microsoft’s least-privilege guidance):

• Create the linked server with a script and restrict who can run it. Through T-SQL it requires ALTER ANY LINKED SERVER (or setupadmin); the SSMS dialog requires CONTROL SERVER or sysadmin.
• Turn on rpc out only for linked servers that really need EXECUTE ... AT or remote procedures.
• Grant the remote login SELECT on the objects it reads. That is also enough for the optimizer to read statistics on SQL Server 2012 SP1 and later.
• Re-run the audit query at the end of this article on a schedule and review every “all logins” mapping.

How to Query a Linked Server

There are three ways to send work to a linked server, and they behave very differently:

-- 1. Four-part name: the local optimizer builds the plan and decides what to send
SELECT o.OrderID, o.OrderDate, o.Total
FROM SALESDB.Sales.dbo.Orders AS o
WHERE o.OrderDate >= '20260101';

-- 2. OPENQUERY: the whole query string runs on the remote server
SELECT *
FROM OPENQUERY(SALESDB,
     'SELECT OrderID, OrderDate, Total
      FROM Sales.dbo.Orders
      WHERE OrderDate >= ''20260101''');

-- 3. EXECUTE ... AT: runs any batch on the remote server (requires the "rpc out" option)
EXEC ('SELECT OrderID, Total FROM Sales.dbo.Orders WHERE CustomerID = ?', 42) AT SALESDB;

• Four-part names are the most readable and can be joined with local tables. SQL Server decides how much of the work happens remotely, and with complex predicates or joins it may fetch far more rows than you expect.
• OPENQUERY runs a pass-through query on the remote server and returns only the first result set. It doesn’t accept variables, and the query string is limited to 8 KB. To pass a value, build the whole statement as dynamic SQL and run it with sp_executesql.
• EXECUTE ... AT accepts parameters through ? placeholders and can run any batch, including DDL and stored procedures. It needs rpc out enabled on the linked server.

Calling a Table-Valued Function Through a Linked Server

A common surprise is that remote table-valued functions can’t be called with a four-part name. Running SELECT * FROM SALESDB.Sales.dbo.fn_CustomerOrders(42) fails with this error:

The fix is to run the function on the remote server and return only its result:

-- Fixed parameter: OPENQUERY
SELECT *
FROM OPENQUERY(SALESDB, 'SELECT * FROM Sales.dbo.fn_CustomerOrders(42)');

-- Variable parameter: EXECUTE ... AT with a ? placeholder
DECLARE @CustomerID int = 42;

CREATE TABLE #orders (OrderID int, OrderDate date, Total decimal(18,2));

INSERT INTO #orders (OrderID, OrderDate, Total)
EXEC ('SELECT OrderID, OrderDate, Total FROM Sales.dbo.fn_CustomerOrders(?)', @CustomerID) AT SALESDB;

SELECT * FROM #orders;

INSERT ... EXEC ... AT runs inside a transaction. Because the linked server option remote proc transaction promotion is on by default, SQL Server may try to promote it to a distributed transaction through MSDTC. If MSDTC isn’t configured between the servers and you don’t need a distributed transaction, turn that option off for this linked server.

Linked Server Performance

Most slow linked server queries come down to one thing: too many rows crossing the network. A few rules help:

• Filter on the remote side. If a four-part query is slow, look at the execution plan. A Remote Query or Remote Scan operator returning millions of rows means the filter isn’t being sent. Rewrite it with OPENQUERY so the remote server does the filtering.
• Don’t join a large local table to a large remote table directly. Bring the small, filtered remote set into a temporary table first, index it if needed, then join locally.
• Give the remote login access to statistics. The optimizer uses the remote table’s statistics to estimate row counts. Since SQL Server 2012 SP1, SELECT permission is enough to read them; with less, plans can be poor.
• Set collation compatible only when it’s true. When both servers really share the same collation, it lets SQL Server send character comparisons to the remote side. Set wrongly, it returns incorrect results.
• Set timeouts. connect timeout and query timeout stop a hung remote server from holding local sessions and locks indefinitely.

OPENQUERY vs Four-Part Names: Which Pattern Is Faster?

Neither is always faster. They differ in where the work happens, and that decides how many rows cross the network:

To pass a variable to OPENQUERY, the statement has to be built as dynamic SQL:

-- Passing a value to OPENQUERY: build the statement as dynamic SQL.
-- The value is typed (date), converted to a fixed format and never concatenated as free text.
DECLARE @from date = '2026-01-01';
DECLARE @sql nvarchar(max) =
    N'SELECT * FROM OPENQUERY(SALESDB, ''SELECT OrderID, Total FROM Sales.dbo.Orders WHERE OrderDate >= '''''
    + CONVERT(char(8), @from, 112) + N''''''')';
EXEC sys.sp_executesql @sql;

Other performance pitfalls worth checking, all Field practice: SELECT * against wide remote tables, character comparisons where the two servers use different collations, and a missing remote index on the filter column. For the last one, an OPENQUERY pass-through will not fix a remote scan, because the remote server still has to run the query.

Linked Server Options Worth Knowing

EXEC sys.sp_serveroption N'SALESDB', 'rpc out', 'true';
EXEC sys.sp_serveroption N'SALESDB', 'query timeout', '600';

Version Notes

Audit Your Linked Servers

Linked servers accumulate over years, and nobody remembers why half of them exist. This query lists each one with its provider, target and login mappings. A local_principal_id of 0 means the mapping applies to all logins:

SELECT s.name                         AS linked_server,
       s.provider,
       s.data_source,
       s.provider_string,
       s.is_rpc_out_enabled,
       COALESCE(sp.name, '(all logins)') AS local_login,
       ll.uses_self_credential,
       ll.remote_name
FROM sys.servers AS s
LEFT JOIN sys.linked_logins     AS ll ON ll.server_id   = s.server_id
LEFT JOIN sys.server_principals AS sp ON sp.principal_id = ll.local_principal_id
WHERE s.is_linked = 1
ORDER BY s.name, local_login;

Look for three things: providers still set to SQLNCLI or SQLOLEDB, “all logins” mappings to powerful remote logins, and linked servers that point to servers that no longer exist. Also remember that linked servers are server-level objects. In an availability group, they don’t fail over with the databases, so create them on every replica.

Sources and Review Date

This article was checked against the following Microsoft Learn pages on 19 October 2026. Statements labelled Field practice are our own recommendations and are not stated in those pages.

• Linked servers (Database Engine)
• Create linked servers
• sp_addlinkedsrvlogin
• sp_addlinkedserver
• OPENQUERY