OLE DB provider “MSOLEDBSQL” with SQL Server not supported?

Question

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.

asked 2020-11-05 by Shekar Kola

---

Answer

OLE DB was undeprecated and released in 2018 as MSOLEDBSQL. The prior (and still-deprecated) versions are SQLOLEDB or SQLNCLI.

As you are using SQL Server 2016, your server probably does not have MSOLEDBSQL installed. You’ll have to install it on the server before you can use it on a linked server.

More information, including a download link, is available in the docs.

answered 2020-11-05 by Andy Mallon