Establishing Linked Server to SQL Server using MSOLEDB Driver

Technical Reviewer: 

Krishnakumar Rukmangathan – Support Escalation Engineer, BI Developer team, Microsoft
Troy Moen –  Sr. Escalation Engineer 

Linked server enable the Database Engine to read data from remote data sources and execute commands against remote database servers. This particular blog will discuss leverage the new MSOLEDB driver to establish linked server with .

The MSOLEDB driver is the latest OLEDB Driver from Microsoft. It supports 1.2 along with connection string attributes like MultiSubnetFailover. The previous Microsoft OLE DB Provider for SQL Server (SQLOLEDB) and SQL Server Native Client OLE DB provider (SQLNCLI) are considered deprecated

Supportability:

Establishing a Linked Server to SQL Server using the MSOLEDB driver is supported on following version of SQL Server:
SQL 2016 SP2 CU6 or higher
SQL 2017 CU15 or higher
SQL 2019 and newer


Note:

All other versions of SQL Server except those listed above will result in the following error when using the MSOLEDB driver for establishing linked server.

Msg 8522, Level 16, State 3, Line 8

Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.

By default, SQL Server 2017 and earlier versions use the Microsoft OLEDB Provider for SQL Server when establishing linked server connections. Starting with SQL , the MSOLEDB driver will be shipped as part of the installation, and therefore MSOLEDB is the default driver chosen to establish a linked server to SQL Server.

You can download the MSOLEDB Driver here. (version 18.2 was the latest version at the time of writing this blog in Sept 2019). After installing the MSOLEDB x64 driver for 64-bit SQL Servers, you can use the Management Studio Interface or T-SQL to create a new linked server to another instance of SQL Server.

image.pngLinked Server MSOLEDB Driver

 
 

Or using T-SQL:
USE [master]

GO

If you are establishing a linked server to AlwaysOn Availability Group listener and the listener has multiple IP addresses spanning a different subnets(of nodes), you can add MultiSubnetFailover=Yes in the Provider String, as shown below.

image.pngLinked Server Listener MultiSubnet

Author:  Chirag Shah – Premier Field Engineer, Data Platform

 

This article was originally published by Microsoft's Premier Field Engineering Blog. You can find the original article here.