Establishing Linked Server to SQL Server using MSOLEDB Driver

Published Sep 27 2019 02:34 PM 2,760 Views
Microsoft

Technical Reviewer: 

Krishnakumar Rukmangathan - Support Escalation Engineer, SQL Server BI Developer team, Microsoft
Troy Moen -  Sr. Escalation Engineer 

 

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

The MSOLEDB driver is the latest OLEDB Driver from Microsoft. It supports TLS 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 Server 2019, 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.

 

Linked Server MSOLEDB DriverLinked Server MSOLEDB Driver

 
 

Or using T-SQL:

USE [master]

GO

EXEC master.dbo.sp_addlinkedserver @server = N'<Linked Server Name>', @srvproduct=N'', @Provider=N'MSOLEDBSQL', @datasrc=N'<Target SQL Server Name'

 

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

 

Linked Server Listener MultiSubnetLinked Server Listener MultiSubnet

 

Author:  Chirag Shah – Premier Field Engineer, Data Platform

1 Comment
Visitor

Thanks Chirag, this is long awaited and solves a few issues for us.  Just need to remember to also enable "Allow inprocess" under MSOLEDBSQL provider properties.

Version history
Last update:
‎Apr 29 2020 10:25 AM
Updated by: