---
title: "Linked Servers (Database Engine)"
description: Linked servers enable SQL Server and Azure SQL Managed Instance to read from and execute data in remote data sources.
author: WilliamDAssafMSFT
ms.author: wiassaf
ms.reviewer: vanto, randolphwest
ms.date: 06/12/2025
ms.service: sql
ms.topic: conceptual
helpviewer_keywords:
  - "OLE DB, linked servers"
  - "OLE DB provider, linked servers"
  - "server management [SQL Server], linked servers"
  - "linked servers [SQL Server]"
  - "distributed queries [SQL Server], linked servers"
  - "servers [SQL Server], linked"
  - "remote servers [SQL Server], linked servers"
  - "linked servers [SQL Server], about linked servers"
---
# Linked servers (Database Engine)

[!INCLUDE [SQL Server SQL MI](../../includes/applies-to-version/sql-asdbmi.md)]

Linked servers enable the [!INCLUDE [ssDEnoversion](../../includes/ssdenoversion-md.md)] and [!INCLUDE [ssazuremi](../../includes/ssazuremi-md.md)] to read data from the remote data sources and execute commands against the remote database servers (for example, OLE DB data sources) outside of the instance of [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)]. Typically linked servers are configured to enable the [!INCLUDE [ssDE](../../includes/ssde-md.md)] to execute a [!INCLUDE [tsql](../../includes/tsql-md.md)] statement that includes tables in another instance of [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)], or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including third-party database providers and Azure Cosmos DB.

> [!NOTE]  
> Linked servers are available in [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] and [!INCLUDE [ssazuremi](../../includes/ssazuremi-md.md)] (with some [constraints](/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server#linked-servers)). Linked servers aren't available in [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)].

## When to use linked servers?

  Linked servers enable you to implement distributed databases that can fetch and update data in other databases. Linked servers are a good solution in the scenarios where you need to implement database sharding without need to create a custom application code or directly load from remote data sources. Linked servers offer the following advantages:

- The ability to access data from outside of [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)].

- The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.

- The ability to address diverse data sources similarly.

You can configure a linked server by using [!INCLUDE [ssManStudioFull](../../includes/ssmanstudiofull-md.md)] or by using the [sp_addlinkedserver](../system-stored-procedures/sp-addlinkedserver-transact-sql.md) statement. OLE DB providers vary greatly in the type and number of parameters required. For example, some providers require you to provide a security context for the connection using [sp_addlinkedsrvlogin](../system-stored-procedures/sp-addlinkedsrvlogin-transact-sql.md). Some OLE DB providers allow [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] to update data on the OLE DB source. Others provide only read-only data access. For information about each OLE DB provider, consult documentation for that OLE DB provider.

## Linked server components

A linked server definition specifies the following objects:

- An OLE DB provider

- An OLE DB data source

An *OLE DB provider* is a DLL that manages and interacts with a specific data source. An *OLE DB data source* identifies the specific database that can be accessed through OLE DB. Although data sources queried through linked server definitions are ordinarily databases, OLE DB providers exist for various files and file formats. These include text files, spreadsheet data, and the results of full-text content searches.

Starting with [!INCLUDE [sql-server-2019](../../includes/sssql19-md.md)], the [Microsoft OLE DB Driver for SQL Server](../../connect/oledb/oledb-driver-for-sql-server.md) (PROGID: MSOLEDBSQL) is the default OLE DB provider. In earlier versions, the [SQL Server Native Client](../native-client/sql-server-native-client.md) (PROGID: SQLNCLI11) was the default OLE DB provider.

> [!IMPORTANT]  
> [!INCLUDE [snac-removed-oledb-only](../../includes/snac-removed-oledb-only.md)]

Linked servers to [!INCLUDE [msCoName](../../includes/msconame-md.md)] Access and Excel sources are only supported by Microsoft when using the 32-bit Microsoft.JET.OLEDB.4.0 OLE DB provider.

> [!NOTE]  
> [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] distributed queries are designed to work with any OLE DB provider that implements the required OLE DB interfaces. However, [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] has been tested against the default OLE DB provider.

## Linked server details

The following illustration shows the basics of a linked server configuration.

:::image type="content" source="media/linked-servers-database-engine/configuration.png" alt-text="Diagram showing client tier, server tier, and database server tier.":::

Typically, linked servers are used to handle distributed queries. When a client application executes a distributed query through a linked server, [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] parses the command and sends requests to OLE DB. The rowset request might be in the form of executing a query against the provider or opening a base table from the provider.

> [!NOTE]  
> For a data source to return data through a linked server, the OLE DB provider (DLL) for that data source must be present on the same server as the instance of [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)].

> [!IMPORTANT]  
> When an OLE DB provider is used, the account under which the [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] service runs must have read and execute permissions for the directory, and all subdirectories, in which the provider is installed. This includes Microsoft-released providers, and any third-party providers.

> [!NOTE]  
> Linked servers support Active Directory pass-through authentication when using full delegation. Starting with [!INCLUDE [ssSQL17](../../includes/sssql17-md.md)] CU17, pass-through authentication with constrained delegation is also supported; however, [resource-based constrained delegation](/windows-server/security/kerberos/kerberos-constrained-delegation-overview) isn't supported.

<a id="managing-providers"></a>

## Manage providers

There's a set of options that control how [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] loads and uses OLE DB providers that are specified in the registry.

<a id="managing-linked-server-definitions"></a>

## Manage linked server definitions

When you're setting up a linked server, register the connection information and data source information with [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)]. After being registered, that data source can be referred to with a single logical name.

You can use stored procedures and catalog views to manage linked server definitions:

- Create a linked server definition by running `sp_addlinkedserver`.

- View information about the linked servers defined in a specific instance of [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] by running a query against the `sys.servers` system catalog view.

- Delete a linked server definition by running `sp_dropserver`. You can also use this stored procedure to remove a remote server.

You can also define linked servers by using [!INCLUDE [ssManStudioFull](../../includes/ssmanstudiofull-md.md)]. In the Object Explorer, right-click **Server Objects**, select **New**, and select **Linked Server**. You can delete a linked server definition by right-clicking the linked server name and selecting **Delete**.

When you execute a distributed query against a linked server, include a fully qualified, four-part table name for each data source to query. This four-part name should be in the form `<linked_server_name>.<catalog>.<schema>.<object_name>`.

References to temporary objects will always resolve to the local instance's `tempdb` where applicable, even when prefixing with the linked server name.

Linked servers can be defined to point back (loop back) to the server on which they are defined. Loopback servers are most useful when testing an application that uses distributed queries on a single server network. Loopback linked servers are intended for testing and aren't supported for many operations, such as distributed transactions.

## Linked servers with Azure SQL Managed Instance

[Azure SQL Managed Instance](/azure/azure-sql/managed-instance/sql-managed-instance-paas-overview) linked servers support both SQL authentication and authentication with Microsoft Entra ID ([formerly Azure Active Directory](/entra/fundamentals/new-name)).

To use SQL Agent jobs on Azure SQL Managed Instance to query a remote server through a linked server, use [sp_addlinkedsrvlogin](../system-stored-procedures/sp-addlinkedsrvlogin-transact-sql.md) to create a mapping from a login on the local server to a login on the remote server. When the SQL Agent job connects to the remote server through the linked server, it executes the T-SQL query in the context of the remote login. For more information, see [SQL Agent jobs with Azure SQL Managed Instance](../../ssms/agent/implement-sql-server-agent-security.md#linked-servers).

<a id="limitations-of-azure-ad-authentication"></a>

### Microsoft Entra authentication

Two supported Microsoft Entra authentication modes are: managed identity and pass-through. Managed identity authentication can be used to allow local logins to query remote linked servers. Pass-through authentication allows a principal that can authenticate with a local instance to access a remote instance via a linked server.

To use Microsoft Entra pass-through authentication for a linked server in Azure SQL Managed Instance, you need the following prerequisites:

- The same principal is added as a login on the remote server.
- Both instances are members of the [SQL trust group](/azure/azure-sql/managed-instance/server-trust-group-overview).

> [!NOTE]  
> Existing definitions of linked servers that were configured for pass-through mode support Microsoft Entra authentication. The only requirement for this would be to add SQL Managed Instance to the [Server Trust Group](/azure/azure-sql/managed-instance/server-trust-group-overview).

The following limitations apply to Microsoft Entra authentication for linked servers in Azure SQL Managed Instance:

- Microsoft Entra authentication isn't supported for SQL managed instances in different Microsoft Entra tenants.
- Microsoft Entra authentication for linked servers is supported only with OLE DB driver version 18.2.1 and higher.

### MSOLEDBSQL19 and linked servers

Currently, MSOLEDBSQL19 prevents the creation of linked servers without encryption and a trusted certificate (a self-signed certificate is insufficient). If linked servers are required, use the existing supported version of MSOLEDBSQL.

Starting in [!INCLUDE [sssql25-md](../../includes/sssql25-md.md)], you can use Microsoft OLE DB Driver version 19 with linked servers. This updated driver introduces significant security enhancements, including support for [TDS 8.0](../security/networking/tds-8.md). TDS 8 introduces a breaking change: you must set the `Encryption` parameter in your provider string.

The `Encryption` parameter offers three distinct settings:

- `Yes`/`True`/`Mandatory`
- `No`/`False`/`Optional`
- `Strict`

The `Strict` option mandates the usage of TDS 8.0, and requires a server certificate for secure connections. For `Yes`/`True`/`Mandatory`, a trusted certificate is expected. You can't use a self-signed certificate.

| OLE DB version | Encryption parameter | Possible values | Default value |
| --- | --- | --- | --- |
| OLE DB 18 | Optional | True/Mandatory, False/No | False/No |
| OLE DB 19 | Required | No/False, Yes/Mandatory, Strict (new) | Yes/Mandatory |

The `TrustServerCertificate` parameter is supported, but not recommended. **Trust Server Certificate** disables certificate validation, weakening the security of encrypted connections.

| Trust Server Certificate client setting | Connection string/connection attribute Trust Server Certificate | Certificate validation |
| --- | --- | --- |
| 0 | No (default) | Yes |
| 0 | Yes | Yes |
| 1 | No (default) | Yes |
| 1 | Yes | No |

These settings must be correctly specified in the provider string when configuring linked server connections, to ensure compatibility and security with the new driver.

For more information about OLE DB 19 and encryption, certificate and Trust Server Certificate behavior for OLE DB 19, see [Encryption and certificate validation in OLE DB](../../connect/oledb/features/encryption-and-certificate-validation.md).

## Related content

- [Create linked servers (SQL Server Database Engine)](create-linked-servers-sql-server-database-engine.md)
- [sys.servers (Transact-SQL)](../system-catalog-views/sys-servers-transact-sql.md)
- [sp_linkedservers (Transact-SQL)](../system-stored-procedures/sp-linkedservers-transact-sql.md)
- [sp_addlinkedserver (Transact-SQL)](../system-stored-procedures/sp-addlinkedserver-transact-sql.md)
- [sp_addlinkedsrvlogin (Transact-SQL)](../system-stored-procedures/sp-addlinkedsrvlogin-transact-sql.md)
- [sp_dropserver (Transact-SQL)](../system-stored-procedures/sp-dropserver-transact-sql.md)