使用Vnet配置ADF Lift and Shift SSIS包执行时报OLE DB连接错误

我们正尝试通过配置了VNet的ADF Azure SSIS集成运行时，将现有SSIS包Lift and Shift到Azure。我们已在Azure中成功创建VNet，且可正常通过其完成连接。但在通过ADF执行包时遇到连接报错。

报错详情 #

Package:Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft OLE DB Driver for SQL Server"  Hresult: 0x80004005  Description: "Login timeout expired".
An OLE DB record is available.  Source: "Microsoft OLE DB Driver for SQL Server"  Hresult: 0x80004005  Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".An OLE DB record is available.  Source: "Microsoft OLE DB Driver for SQL Server"  Hresult: 0x80004005  Description: "SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. ".
6/23/2021 12:29:29 PM +00:00 : Data Flow Task:Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "xxxxx\SQLExpress.SampleDB.sa" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
6/23/2021 12:29:29 PM +00:00 : Data Flow Task:Error: OLE DB Source failed validation and returned error code 0xC020801C.

错误截图 #

连接配置文件（Conn_Config）内容 #

<?xml version="1.0"?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedDate="6/24/2021 3:45:49 PM" GeneratedFromPackageID="{5104305D-A0CE-4C60-B044-1DB05C92AB7C}" GeneratedFromPackageName="Package1" GeneratedBy="xxxxSYS\xxxxxxxxxx"/>
</DTSConfigurationHeading>
<Configuration ValueType="Boolean" Path="\Package.Connections[Src_Server\SQL2017.Test.sa].Properties[ConnectByProxy]" ConfiguredType="Property">
<ConfiguredValue>-1</ConfiguredValue>
</Configuration>
<Configuration ValueType="String" Path="\Package.Connections[Src_Server\SQL2017.Test.sa].Properties[ConnectionString]" ConfiguredType="Property">
<ConfiguredValue>Data Source=Src_Server\SQL2017;User ID=sa;Initial Catalog=Test;Provider=SQLNCLI11.1;Auto Translate=False;</ConfiguredValue>
</Configuration>
<Configuration ValueType="Int32" Path="\Package.Connections[Src_Server\SQL2017.Test.sa].Properties[ConnectRetryCount]" ConfiguredType="Property">
<ConfiguredValue>1</ConfiguredValue>
</Configuration>
<Configuration ValueType="Int32" Path="\Package.Connections[Src_Server\SQL2017.Test.sa].Properties[ConnectRetryInterval]" ConfiguredType="Property">
<ConfiguredValue>5</ConfiguredValue>
</Configuration>
<Configuration ValueType="Boolean" Path="\Package.Connections[Src_Server\SQL2017.Test.sa].Properties[ConnectUsingManagedIdentity]" ConfiguredType="Property">
<ConfiguredValue>0</ConfiguredValue>
</Configuration>
<Configuration ValueType="String" Path="\Package.Connections[Src_Server\SQL2017.Test.sa].Properties[Description]" ConfiguredType="Property">
<ConfiguredValue/>
</Configuration>
<Configuration ValueType="String" Path="\Package.Connections[Src_Server\SQL2017.Test.sa].Properties[InitialCatalog]" ConfiguredType="Property">
<ConfiguredValue>Test</ConfiguredValue>
</Configuration>
<Configuration ValueType="String" Path="\Package.Connections[Src_Server\SQL2017.Test.sa].Properties[Name]" ConfiguredType="Property">
<ConfiguredValue>Src_Server\SQL2017.Test.sa</ConfiguredValue>
</Configuration>
<Configuration ValueType="Int32" Path="\Package.Connections[Src_Server\SQL2017.Test.sa].Properties[ProtectionLevel]" ConfiguredType="Property">
<ConfiguredValue>1</ConfiguredValue>
</Configuration>
<Configuration ValueType="Boolean" Path="\Package.Connections[Src_Server\SQL2017.Test.sa].Properties[RetainSameConnection]" ConfiguredType="Property">
<ConfiguredValue>0</ConfiguredValue>
</Configuration>
<Configuration ValueType="Int32" Path="\Package.Connections[Src_Server\SQL2017.Test.sa].Properties[Scope]" ConfiguredType="Property">
<ConfiguredValue>0</ConfiguredValue>
</Configuration>
<Configuration ValueType="Boolean" Path="\Package.Connections[dest_server.database.windows.net.dest_DB.xxxx].Properties[ConnectByProxy]" ConfiguredType="Property">
<ConfiguredValue>-1</ConfiguredValue>
</Configuration>
<Configuration ValueType="String" Path="\Package.Connections[dest_server.database.windows.net.dest_DB.xxxx].Properties[ConnectionString]" ConfiguredType="Property">
<ConfiguredValue>Data Source=dest_server.database.windows.net;User ID=xxxx;Initial Catalog=dest_DB;Provider=MSOLEDBSQL.1;Persist Security Info=True;Auto Translate=False;</ConfiguredValue>
</Configuration>
<Configuration ValueType="Int32" Path="\Package.Connections[dest_server.database.windows.net.dest_DB.xxxx].Properties[ConnectRetryCount]" ConfiguredType="Property">
<ConfiguredValue>1</ConfiguredValue>
</Configuration>
<Configuration ValueType="Int32" Path="\Package.Connections[dest_server.database.windows.net.dest_DB.xxxx].Properties[ConnectRetryInterval]" ConfiguredType="Property">
<ConfiguredValue>5</ConfiguredValue>
</Configuration>
<Configuration ValueType="Boolean" Path="\Package.Connections[dest_server.database.windows.net.dest_DB.xxxx].Properties[ConnectUsingManagedIdentity]" ConfiguredType="Property">
<ConfiguredValue>0</ConfiguredValue>
</Configuration>
<Configuration ValueType="String" Path="\Package.Connections[dest_server.database.windows.net.dest_DB.xxxx].Properties[Description]" ConfiguredType="Property">
<ConfiguredValue/>
</Configuration>
<Configuration ValueType="String" Path="\Package.Connections[dest_server.database.windows.net.dest_DB.xxxx].Properties[InitialCatalog]" ConfiguredType="Property">
<ConfiguredValue>dest_DB</ConfiguredValue>
</Configuration>
<Configuration ValueType="String" Path="\Package.Connections[dest_server.database.windows.net.dest_DB.xxxx].Properties[Name]" ConfiguredType="Property">
<ConfiguredValue>dest_server.database.windows.net.dest_DB.xxxx</ConfiguredValue>
</Configuration>
<Configuration ValueType="Int32" Path="\Package.Connections[dest_server.database.windows.net.dest_DB.xxxx].Properties[ProtectionLevel]" ConfiguredType="Property">
<ConfiguredValue>1</ConfiguredValue>
</Configuration>
<Configuration ValueType="Boolean" Path="\Package.Connections[dest_server.database.windows.net.dest_DB.xxxx].Properties[RetainSameConnection]" ConfiguredType="Property">
<ConfiguredValue>0</ConfiguredValue>
</Configuration>
<Configuration ValueType="Int32" Path="\Package.Connections[dest_server.database.windows.net.dest_DB.xxxx].Properties[Scope]" ConfiguredType="Property">
<ConfiguredValue>0</ConfiguredValue>
</Configuration>
</DTSConfiguration>

从报错信息可以定位故障为源端SQL Server实例（Src_Server\SQL2017）连接失败，核心原因是SSIS集成运行时无法通过实例名称解析找到对应服务，按以下步骤排查修复：

• 网络连通性校验
  • 确认SSIS集成运行时所在VNet与源端SQL Server网络已打通：如果源端为本地部署的SQL Server，需确认VNet已配置点对点VPN、ExpressRoute等专线连接；如果源端为Azure VM上的SQL Server，需确认两者处于同一VNet或已配置VNet对等。
  • 检查VNet NSG规则、源端服务器防火墙规则：需放行SSIS集成运行时子网IP段到源端SQL Server的入站请求，包含SQL实例的TCP端口，以及SQL Browser服务的1434 UDP端口。
• 源端SQL Server配置校验
  • 确认源端SQL Server已开启远程连接，SQL Server配置管理器中已启用TCP/IP协议，建议将实例TCP端口配置为静态端口，避免动态端口在跨网络场景下的解析问题。
• 连接配置优化
  • Azure VNet对NetBIOS名称解析支持有限，不要用「机器名\实例名」的格式访问源端，修改为*[IP地址],[端口号]*的直连格式，无需依赖SQL Browser服务解析实例，示例连接字符串如下：

  Data Source=192.168.1.20,49172;User ID=sa;Initial Catalog=Test;Provider=MSOLEDBSQL.1;Auto Translate=False;
  

  • 将源端连接的Provider从SQLNCLI11.1替换为和目标端一致的MSOLEDBSQL.1，避免驱动版本兼容问题。
• SSIS集成运行时校验
  确认SSIS IR绑定的VNet子网有足够可用IP，未被Azure防火墙拦截出站访问请求。

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