patternsqlModerate

OLE DB provider "MSOLEDBSQL" with SQL Server not supported?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

providermsoledbsqlwithsqloleservernotsupported

Problem

I have been using linked server with the old provider (SQLNCLI) without any issue, as recommended by Microsoft, I'm planning to switch to new provider (MSOLEDBSQL). I'm able to add linked server using following T-SQL after installing the drivers

EXEC sp_addlinkedserver     
   @server=N'SQL02\DEV1',   
   @srvproduct=N'',  
   @provider=N'MSOLEDBSQL',   
   @datasrc=N'SQL02,1933';

Unfortunately, getting following error when I try to query new linked server:

Queries that I tried:

--- example 1
select * from OPENQUERY ([SQL02\DEV1], 'select name from sys.databases');

--- example 2
select name from [SQL02\DEV1].master.sys.databases;

--- example 3 (without linked server dependency)
SELECT c.* FROM OPENROWSET(
      'MSOLEDBSQL'
    , 'Server=SQL02,1933;Database=master;Integrated Security=True;'
    , 'SELECT name FROM sys.databases;'
    ) c;

Getting same error from all examples:

Out-of-process use of OLE DB provider "MSOLEDBSQL" with SQL Server is not supported.

Does that really mean SQL-2016 is not supported for using new provider MSOLEDBSQL especially in Linked Servers, or is there anything I missed other than re-installing drivers and restarting the SQL Server.

Solution

You seem to have done everything right. However the link you posted has a comment on the page which states:

This page is no longer maintained. Please read the details below.

If you open the details tab by clicking the + sign you will be greeted with the following information:

This page is no longer maintained. To download the Microsoft OLE DB Driver 18 for SQL Server, please go to the documentation page at https://aka.ms/downloadmsoledbsql.

This will take you to https://learn.microsoft.com/en-gb/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver15 (Just in case the short link becomes obsolete.)

I would grab the newest OLE DB drivers from there.
Microsoft OLE DB Driver for SQL Server

Take your time and read the information about the Different generations of OLE DB Drivers on the web page. In the sub section titled 3. Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) there is this small note that states:

The new OLE DB provider is called the Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL). The new provider will be updated with the most recent server features going forward.

Based on what you wrote in your question, you must be using the correct OLE DB driver. Good....

Create Linked Server from SQL Server 2019 to SQL Server 2016

I have multiple instances on my laptop and created a linked server from my 2019 instance to a SQL Server 2016 instance using the following stored procedures:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver 
    @server = N'MSOLEDBDSQL', 
    @srvproduct=N'SERVER\sql2016', 
    @provider=N'MSOLEDBSQL', 
    @datasrc=N'SERVER\sql2016', 
    @catalog=N'master'

GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MSOLEDBDSQL', @locallogin = NULL , @useself = N'True'
GO

I then queried the linked server with the following statement:

SELECT name, create_date FROM MSOLEDBDSQL.master.sys.databases

Which promptly returned:

+----------------------+-------------------------+
|         name         |       create_date       |
+----------------------+-------------------------+
| master               | 2003-04-08 09:13:36.390 |
| tempdb               | 2020-11-03 16:29:26.787 |
| model                | 2003-04-08 09:13:36.390 |
| msdb                 | 2016-04-30 00:46:38.773 |
| SSODB                | 2020-01-03 14:35:34.143 |
| BizTalkMgmtDb        | 2020-01-03 14:35:39.570 |
| BizTalkDTADb         | 2020-01-03 14:35:43.370 |
| BizTalkMsgBoxDb      | 2020-01-03 14:35:45.137 |
| BizTalkRuleEngineDb  | 2020-01-03 14:36:21.603 |
| BAMPrimaryImport     | 2020-01-03 14:36:34.713 |
| BAMArchive           | 2020-01-03 14:36:35.333 |
| DemoDB               | 2020-01-15 12:04:41.427 |
| BAMAlertsApplication | 2020-01-28 14:40:20.767 |
+----------------------+-------------------------+

Seems to work just fine.
Create Linked Server from SQL Server 2016 (CI) instance to SQL Server 2016 instance

I then created a linked server from my 2016 Case-insensitive instance to the same SQL Server 2016 instance using the following stored procedures:

```
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'MSOLEDBDSQL',
@srvproduct=N'SERVER\sql2016',
@provider=N'MSOLEDBSQL',
@datasrc=N'SERVER\sql2016',
@catalog=N'master'

GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSO

Code Snippets

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver 
    @server = N'MSOLEDBDSQL', 
    @srvproduct=N'SERVER\sql2016', 
    @provider=N'MSOLEDBSQL', 
    @datasrc=N'SERVER\sql2016', 
    @catalog=N'master'

GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MSOLEDBDSQL', @locallogin = NULL , @useself = N'True'
GO

SELECT name, create_date FROM MSOLEDBDSQL.master.sys.databases

+----------------------+-------------------------+
|         name         |       create_date       |
+----------------------+-------------------------+
| master               | 2003-04-08 09:13:36.390 |
| tempdb               | 2020-11-03 16:29:26.787 |
| model                | 2003-04-08 09:13:36.390 |
| msdb                 | 2016-04-30 00:46:38.773 |
| SSODB                | 2020-01-03 14:35:34.143 |
| BizTalkMgmtDb        | 2020-01-03 14:35:39.570 |
| BizTalkDTADb         | 2020-01-03 14:35:43.370 |
| BizTalkMsgBoxDb      | 2020-01-03 14:35:45.137 |
| BizTalkRuleEngineDb  | 2020-01-03 14:36:21.603 |
| BAMPrimaryImport     | 2020-01-03 14:36:34.713 |
| BAMArchive           | 2020-01-03 14:36:35.333 |
| DemoDB               | 2020-01-15 12:04:41.427 |
| BAMAlertsApplication | 2020-01-28 14:40:20.767 |
+----------------------+-------------------------+

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver 
    @server = N'MSOLEDBDSQL', 
    @srvproduct=N'SERVER\sql2016', 
    @provider=N'MSOLEDBSQL', 
    @datasrc=N'SERVER\sql2016', 
    @catalog=N'master'

GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MSOLEDBDSQL', @locallogin = NULL , @useself = N'True'
GO

SELECT name, create_date FROM MSOLEDBDSQL.master.sys.databases

Context

StackExchange Database Administrators Q#279226, answer score: 18

Revisions (0)

No revisions yet.