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

MS OLE DB provider for SQL server

The Microsoft OLE DB Provider is an SQL tool that enables developers to write code that provides an application with ADO access to an SQL database. The OLE DB Provider is not installed by default when SQL Server is installed.OLE DB Driver for SQL Server was designed to provide a simplified method of gaining native data access to SQL Server using OLE DB. It provides a way to innovate and evolve new data access features without changing the current Windows DAC components, which are now part of the Microsoft Windows platform.

According to you description, 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://docs.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'SERVERsql2016', @provider=N'MSOLEDBSQL', @datasrc=N'SERVERsql2016', @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'SERVERsql2016', @provider=N'MSOLEDBSQL', @datasrc=N'SERVERsql2016', @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 in my CI instance with the following statement:
SELECT name, create_date FROM MSOLEDBDSQL.master.sys.databases
Which promptly returned:
Msg 7430, Level 16, State 3, Line 1 Out-of-process use of OLE DB provider "MSOLEDBSQL" with SQL Server is not supported. Completion time: 2020-11-05T13:41:43.0333451+01:00

This seems to correlate with what you are observing..... Hmmm. Let's think this over. Possible Causes

• Collation has to be the same: CI or CS
  

• Something is different on SQL Server 2019 instance compared to SQL Server 2016 instance.
  

Ojbect Explorer | Server Objects | Linked Servers | Providers | MSOLEDBSQL Properties We'll open up the properties of the driver on the SQL Server 2019 instance I used first: ...and the properties of the driver on the SQL Server 2016 CI I used in my second test:

And there we have it. The property Allow inprocess on the SQL Server 2016 CI configured instance isn't ticked. The SQL Server 2019 instance has this setting ticked. Let's change it for our SQL Server 2016 instance and run the query. It works!

Solution

Ensure the option Allow inprocess is ticked for the Microsoft OLE DB Driver for SQL Server in its properties in the Linked Server | Providers.