通过Azure VM链接服务器插入托管实例：应用执行失败SSMS成功

场景概述 #

本地基于Entity Framework的应用无法直接连接Azure SQL托管实例，通过Azure Windows虚拟机上的SQL Server 2019实例配置的链接服务器中转插入数据。在SSMS中执行插入命令可成功，但应用执行相同命令时失败。

报错信息 #

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

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

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

执行的插入语句 #

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

链接服务器配置说明 #

已配置链接服务器及登录映射，尝试过将remote proc transaction promotion设为true和false，也切换过SQLNCLI与MSOLEDBSQL provider，问题依旧。当前配置脚本：

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

解决方案 #

1. 修正链接服务器核心配置 #

当前rpc out设为false会限制远程事务交互，同时强制关闭分布式事务提升：

EXEC master.dbo.sp_serveroption @server=N'My_LS', @optname=N'rpc out', @optvalue=N'true';
EXEC master.dbo.sp_serveroption @server=N'My_LS', @optname=N'remote proc transaction promotion', @optvalue=N'false';

执行后重启SQL Server服务再测试。

2. 用MSOLEDBSQL重建链接服务器 #

原配置混用provider导致兼容性问题，重新创建适配Azure SQL的链接服务器：

-- 删除原有链接服务器及登录
EXEC master.dbo.sp_dropserver @server=N'My_LS', @droplogins='droplogins';

-- 创建新链接服务器，指定MSOLEDBSQL并适配Azure加密要求
EXEC master.dbo.sp_addlinkedserver 
    @server = N'My_LS', 
    @srvproduct=N'', 
    @provider=N'MSOLEDBSQL', 
    @datasrc=N'myazuremi.DATABASE.WINDOWS.NET',
    @provstr=N'Encryption=yes;TrustServerCertificate=no';

-- 重新添加登录映射（替换为实际密码）
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_serveroption @server=N'My_LS', @optname=N'data access', @optvalue=N'true';
EXEC master.dbo.sp_serveroption @server=N'My_LS', @optname=N'rpc', @optvalue=N'true';
EXEC master.dbo.sp_serveroption @server=N'My_LS', @optname=N'rpc out', @optvalue=N'true';
EXEC master.dbo.sp_serveroption @server=N'My_LS', @optname=N'remote proc transaction promotion', @optvalue=N'false';

3. 调整EF插入逻辑避免分布式事务 #

修改EF生成的语句，直接通过链接服务器四部分名称执行，跳过本地事务提升：

-- 直接插入到链接服务器的表中
INSERT INTO [My_LS].[YourDBName].[dbo].[mytable]([fieldb], [fieldc], [fieldd], [fielde], [fieldf], [fieldg], [fieldh], [fieldi], [fieldj])
VALUES (@0, @1, @2, @3, @4, @5, @6, @7, @8);
SELECT SCOPE_IDENTITY();

同时在EF代码中禁用自动事务提升，例如使用TransactionScopeOption.Suppress。

4. 配置MSDTC（若必须用分布式事务） #

如果业务依赖分布式事务，需在Azure VM上配置MSDTC：

• 打开「组件服务」→「本地DTC」→ 属性→「安全」选项卡，勾选「允许远程客户端」「允许入站」「允许出站」，选择「不要求验证」
• 在Azure门户为VM开放MSDTC端口（135及动态端口范围）
• 确保Azure SQL托管实例防火墙允许VM的IP访问

内容的提问来源于stack exchange，提问作者mbourgon