I am trying to insert into a table from an on-prem app, through a SQL Server 2019 instance running in a Azure VM (Windows), that has a linked server to an Azure Managed Instance. Due to <reasons I'm not going into>, we can't connect to Managed Instances from on-prem, but the (Entity Framework-based) app has to run on-prem. So I figured we could use a linked server on our VM in Azure, to bridge the gap.

I created a linked server on the SQL Server (VM), and then created views on the linked server and set up permissions across. When I run the below command it works, but when the app runs the same command, it fails with the below errors. I grabbed the query from a trace and ran it from the same server as the app, but in SSMS, using the same user as the app, connecting to the SQL VM, and it works. But when the App does it, I get the errors:

OLE DB provider "MSOLEDBSQL" for linked server "My_LS" returned message "The parameter is incorrect.".

followed by

The OLE DB provider "MSOLEDBSQL" for linked server "My_LS" reported an error. One or more arguments were reported invalid by the provider.

if I look for errors on the VM, I also get:

The operation could not be performed because OLE DB provider "MSOLEDBSQL" for linked server "MEDRX01_LS" was unable to begin a distributed transaction.

The insert statement is:

exec sp_executesql N'INSERT [dbo].[mytable]([fieldb], [fieldc], [fieldd], [fielde], [fieldf], [fieldg], [fieldh], [fieldi], [fieldj])
VALUES (@0, @1, @2, @3, @4, @5, @6, @7, @8)
SELECT [id]
FROM [dbo].[mytable]
WHERE @@ROWCOUNT > 0 AND [id] = scope_identity()',N'@0 int,@1 datetime2(7),@2 datetime2(7),@3 datetime2(7),@4 varchar(10),@5 varchar(20),@6 varchar(5),@7 varchar(20),@8 bit',@0=35,@1='2023-04-19 10:43:25.8661564',@2='2023-04-19 10:43:25.6911413',@3='2023-04-23 10:43:25.6911413',@4='12345678',@5='AB12344R',@6='R4321',@7='123456789A',@8=0

and here is the linked server - I have tried with "enable promotion" set to both true and false. Again, it works when I run it, but not when the app does. Oddly, it says SQLNCLI below, but when I look in the GUI it says "Microsoft OLE DB Provider for SQL Server". I created another linked server explicitly using MSOLEDBSQL for the provider, same issue.

/****** Object:  LinkedServer [My_LS]    Script Date: 4/19/2023 10:56:45 AM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'My_LS', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'myazuremi.DATABASE.WINDOWS.NET'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'My_LS',@useself=N'False',@locallogin=NULL,@rmtuser=N'nopermissions',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'My_LS',@useself=N'False',@locallogin=N'userA',@rmtuser=N'userA',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'My_LS',@useself=N'False',@locallogin=N'userB',@rmtuser=N'userB',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'My_LS',@useself=N'False',@locallogin=N'userC',@rmtuser=N'userC',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'My_LS',@useself=N'False',@locallogin=N'myuser',@rmtuser=N'myuser',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'My_LS', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'My_LS', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'My_LS', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'My_LS', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'My_LS', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'My_LS', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'My_LS', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'My_LS', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'My_LS', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'My_LS', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'My_LS', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'My_LS', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'My_LS', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Any help appreciated.