MS OLE DB provider “MSOLEDBSQL” with SQL Server not supported?
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'SQL02DEV1', @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 ([SQL02DEV1], 'select name from sys.databases'); --- example 2 select name from [SQL02DEV1].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. What is MS OLE DB Provider for SQL Server?
MS 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.
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' GOI 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' GOI 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:00This 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 PropertiesWe'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.