OLE DB provider “MSOLEDBSQL” with SQL Server not supported?
Shekar Kola (imported from SE) 
I have been using linked server with the old provider (SQLNCLI) without any issue, as recommended [by Microsoft][1], I'm planning to switch to new provider (MSOLEDBSQL). I'm able to add linked server using following T-SQL after installing [the drivers][2]

```
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.

  [1]: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addlinkedserver-transact-sql?view=sql-server-ver15#arguments
  [2]: https://www.microsoft.com/en-us/download/details.aspx?id=56730
Top Answer
John K N (imported from SE) 
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][1].

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

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'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
- Drivers aren't fully 2016 compatible when connecting from a 2016 to a 2016 instance.
- Something is different on SQL Server 2019 instance compared to SQL Server 2016 instance.

Let's go and have a look at the ...

## 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:

[![Microsoft OLE DB Driver for SQL Server Properties in 2019 instance][3]][3]

...and the properties of the driver on the SQL Server 2016 CI I used in my second test:

[![Microsoft OLE DB Driver for SQL Server Properties in 2016 CI instance][4]][4]

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**.

  [1]:https://aka.ms/downloadmsoledbsql
  [2]: https://docs.microsoft.com/en-gb/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver15
  [3]: https://i.stack.imgur.com/OzVdT.png
  [4]: https://i.stack.imgur.com/KbyIf.png
comments
transcript

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.