Linked Servers (Database Engine)
Linked servers enable the SQL Server Database Engine and Azure SQL Managed Instance 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 SQL Server. Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, 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 CosmosDB.
Linked servers are available in SQL Server Database Engine and Azure SQL Managed Instance. They are not enabled in Azure SQL Database singleton and elastic pools. There are some constraints in Managed Instance that can be found here.
When to use linked servers?
Linked servers enable you to implement distributed databases that can fetch and update data in other databases. They 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 SQL Server.
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 SQL Server Management Studio or by using the sp_addlinkedserver (Transact-SQL) 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 (Transact-SQL). Some OLE DB providers allow SQL Server 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 a variety of files and file formats. These include text files, spreadsheet data, and the results of full-text content searches.
Starting with SQL Server 2019 (15.x), the Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) (PROGID: MSOLEDBSQL) is the default OLE DB provider. In earlier versions, the SQL Server Native Client OLE DB provider (SQLNCLI) (PROGID: SQLNCLI11) was the default OLE DB provider.
The SQL Server Native Client (often abbreviated SNAC) has been removed from SQL Server 2022 (16.x) and SQL Server Management Studio 19 (SSMS). Both the SQL Server Native Client OLE DB provider (SQLNCLI or SQLNCLI11) and the legacy Microsoft OLE DB Provider for SQL Server (SQLOLEDB) are not recommended for new development. Switch to the new Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server going forward.
Linked servers to Microsoft Access and Excel sources are only supported by Microsoft when using the 32-bit Microsoft.JET.OLEDB.4.0 OLE DB provider.
SQL Server distributed queries are designed to work with any OLE DB provider that implements the required OLE DB interfaces. However, SQL Server has been tested against the default OLE DB provider.
Linked server details
The following illustration shows the basics of a linked server configuration.
Typically, linked servers are used to handle distributed queries. When a client application executes a distributed query through a linked server, SQL Server parses the command and sends requests to OLE DB. The rowset request may be in the form of executing a query against the provider or opening a base table from the provider.
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 SQL Server.
When an OLE DB provider is used, the account under which the SQL Server 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.
Linked servers support Active Directory pass-through authentication when using full delegation. Starting with SQL Server 2017 (14.x) CU17, pass-through authentication with constrained delegation is also supported; however, resource-based constrained delegation is not supported.
There is a set of options that control how SQL Server loads and uses OLE DB providers that are specified in the registry.
Manage linked server definitions
When you are setting up a linked server, register the connection information and data source information with SQL Server. 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
View information about the linked servers defined in a specific instance of SQL Server by running a query against the
sys.serverssystem 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 SQL Server Management Studio. 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.
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 are not supported for many operations, such as distributed transactions.
Azure SQL Managed Instance linked server authentication
Azure SQL Managed Instance linked servers support both SQL authentication, and Azure AD (AAD) authentication. Two supported AAD 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 linked server. Prerequisites for pass-through authentication are that the same principal is added as a login on the remote server and that both instances are members of the SQL trust group.
Existing definitions of linked servers that were configured for pass-through mode will support Azure AD authentication. The only requirement for this would be to add Managed Instances to Server Trust Group.
Limitations of Azure AD authentication
- Azure AD authentication is not supported for Managed Instances in different Azure AD tenants.
- Azure AD authentication for linked servers is supported only with OLE DB driver version 18.2.1 and higher.
- Azure AD authentication for linked servers from Managed Instance to SQL Server is supported for mapped local logins only. Propagating security context is not supported. That means that managed identity authentication is supported, while pass-through authentication is not supported.
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.