OLE DB 连接管理器OLE DB connection manager

适用对象:是SQL Server,包含 Linux 版 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server, including on Linux yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

OLE DB 连接管理器使包能够用 OLE DB 访问接口连接到数据源。An OLE DB connection manager enables a package to connect to a data source by using an OLE DB provider. 例如,连接到 SQL ServerSQL Server 的 OLE DB 连接管理器可以使用 MicrosoftMicrosoft OLE DB Provider for SQL ServerSQL ServerFor example, an OLE DB connection manager that connects to SQL ServerSQL Server can use the MicrosoftMicrosoft OLE DB Provider for SQL ServerSQL Server.

备注

SQL ServerSQL Server Native Client 11.0 OLEDB 提供程序不支持用于多子网故障转移群集的新连接字符串关键字 (MultiSubnetFailover=True)。The SQL ServerSQL Server Native Client 11.0 OLEDB provider doesn't support the new connection string key words (MultiSubnetFailover=True) for multi-subnet failover clustering. 有关详细信息,请参阅 SQL Server 发行说明For more information, see the SQL Server Release Notes.

备注

如果数据源是 MicrosoftMicrosoft Office Excel 2007 或 MicrosoftMicrosoft Office Access 2007,则数据源需要不同于早期版本 Excel 或 Access 的数据访问接口。If the data source is MicrosoftMicrosoft Office Excel 2007 or MicrosoftMicrosoft Office Access 2007, the data source requires a different data provider than earlier versions of Excel or Access. 有关详细信息,请参阅 连接到 Excel 工作簿连接到 Access 数据库For more information, see Connect to an Excel Workbook and Connect to an Access Database.

有若干 SQL ServerSQL Server Integration ServicesIntegration Services 任务和数据流组件使用 OLE DB 连接管理器。Several SQL ServerSQL Server Integration ServicesIntegration Services tasks and data flow components use an OLE DB connection manager. 例如,OLE DB 源和 OLE DB 目标使用此连接管理器来提取和加载数据。For example, the OLE DB source and OLE DB destination use this connection manager to extract and load data. 执行 SQL 任务可以使用此连接管理器来连接到 SQL ServerSQL Server 数据库以运行查询。The Execute SQL task can use this connection manager to connect to a SQL ServerSQL Server database to run queries.

OLE DB 连接管理器还用于在以使用 C++ 等语言的非托管代码编写的自定义任务中访问 OLE DB 数据源。You can also use the OLE DB connection manager to access OLE DB data sources in custom tasks written in unmanaged code that uses a language such as C++.

将 OLE DB 连接管理器添加到包时,Integration ServicesIntegration Services 创建将在运行时决定 OLE DB 连接的连接管理器,设置该连接管理器的属性,并将该连接管理器添加到包上的“连接” 集合。When you add an OLE DB connection manager to a package, Integration ServicesIntegration Services creates a connection manager that resolves to an OLE DB connection at runtime, sets the connection manager properties, and adds the connection manager to the Connections collection on the package.

该连接管理器的 ConnectionManagerType 属性设置为 OLEDBThe ConnectionManagerType property of the connection manager is set to OLEDB.

按下列方式配置 OLE DB 连接管理器:Configure the OLE DB connection manager in the following ways:

  • 提供配置为满足选定访问接口要求的特定连接字符串。Provide a specific connection string configured to meet the requirements of the selected provider.

  • 包括要连接到的数据源的名称(取决于访问接口)。Depending on the provider, include the name of the data source to connect to.

  • 为选定的访问接口提供相应的安全凭据。Provide security credentials as appropriate for the selected provider.

  • 指示是否在运行时保留从连接管理器中创建的连接。Indicate whether the connection created from the connection manager is retained at runtime.

记录调用和对连接进行故障排除Log calls and troubleshoot connections

可以记录 OLE DB 连接管理器对外部数据访问接口所做的调用。You can log the calls that the OLE DB connection manager makes to external data providers. 然后可以对 OLE DB 连接管理器与外部数据源的连接进行故障排除。You can then troubleshoot the connections that the OLE DB connection manager makes to external data sources. 若要记录 OLE DB 连接管理器对外部数据访问接口所做的调用,请在包级别启用包日志记录并选择“诊断” 事件。To log the calls that the OLE DB connection manager makes to external data providers, enable package logging, and select the Diagnostic event at the package level. 有关详细信息,请参阅 包执行的疑难解答工具For more information, see Troubleshooting Tools for Package Execution.

配置 OLE DB 连接管理器Configure the OLE DB connection manager

可以通过 SSISSSIS 设计器或以编程方式来设置属性。You can set properties through SSISSSIS Designer, or programmatically. 有关可以在 SSISSSIS 设计器中设置的属性的详细信息,请参阅 配置 OLE DB 连接管理器For more information about the properties that you can set in SSISSSIS Designer, see Configure OLE DB Connection Manager. 有关以编程方式配置连接管理器的信息,请参阅开发人员指南中针对 T:Microsoft.SqlServer.Dts.Runtime.ConnectionManager 类的文档。For information about configuring a connection manager programmatically, see the documentation for T:Microsoft.SqlServer.Dts.Runtime.ConnectionManager class in the Developer Guide.

配置 OLE DB 连接管理器Configure OLE DB connection manager

可以使用“配置 OLE DB 连接管理器” 对话框添加与数据源的连接。Use the Configure OLE DB Connection Manager dialog box to add a connection to a data source. 此连接可以是新连接,也可以是现有连接的副本。This connection can be new, or a copy of an existing connection.

备注

如果数据源是 MicrosoftMicrosoft Office Excel 2007,则数据源需要一个不同于早期版本 Excel 的连接管理器。If the data source is MicrosoftMicrosoft Office Excel 2007, the data source requires a different connection manager than earlier versions of Excel. 有关详细信息,请参阅 连接到 Excel 工作簿For more information, see Connect to an Excel Workbook.

如果数据源是 MicrosoftMicrosoft Office Access 2007,则数据源需要一个不同于早期版本 Access 的 OLE DB 访问接口。If the data source is MicrosoftMicrosoft Office Access 2007, the data source requires a different OLE DB provider than earlier versions of Access. 有关详细信息,请参阅 连接到 Access 数据库For more information, see Connect to an Access Database.

若要了解有关 OLE DB 连接管理器的详细信息,请参阅 OLE DB Connection ManagerTo learn more about the OLE DB connection manager, see OLE DB Connection Manager.

选项Options

数据连接Data connections
从列表中选择现有的 OLE DB 数据连接。Select an existing OLE DB data connection from the list.

数据连接属性Data connection properties
查看所选 OLE DB 数据连接的属性和值。View properties and values for the selected OLE DB data connection.

新建New
使用“连接管理器” 对话框来创建 OLE DB 数据连接。Create an OLE DB data connection by using the Connection Manager dialog box.

删除Delete
选择一个数据连接,然后通过选择“删除” 来删除该连接。Select a data connection, and then delete it by selecting Delete.

Azure 资源身份验证的托管标识Managed identities for Azure resources authentication

Azure 数据工厂中的 Azure-SSIS 集成运行时上运行 SSIS 包时,可以使用与数据工厂关联的托管标识进行 Azure SQL 数据库(或托管实例)身份验证。When running SSIS packages on Azure-SSIS integration runtime in Azure Data Factory, use the managed identity associated with your data factory for Azure SQL Database (or managed instance) authentication. 指定的工厂可以使用此标识访问数据库数据或从/向数据库复制数据。The designated factory can access and copy data from or to your database by using this identity.

备注

使用 Azure Active Directory (Azure AD) 身份验证(包括托管标识身份验证)连接到 Azure SQL 数据库(或托管实例)时,可能遇到与包执行失败或意外行为变更有关的问题。When you use Azure Active Directory (Azure AD) authentication (including managed identity authentication) to connect to Azure SQL Database (or managed instance), you might encounter a problem related to package execution failure or unexpected behavior change. 有关详细信息,请参阅 Azure AD 功能和限制For more information, see Azure AD features and limitations.

要对 Azure SQL 数据库使用托管身份验证,请按照以下步骤配置数据库:To use managed identity authentication for Azure SQL Database, follow these steps to configure your database:

  1. 在 Azure AD 中创建组。Create a group in Azure AD. 使托管标识成为该组的成员。Make the managed identity a member of the group.

    1. 从 Azure 门户中查找数据工厂托管标识Find the data factory managed identity from the Azure portal. 转到数据工厂的“属性” 。Go to your data factory's Properties. 复制“托管标识对象 ID” 。Copy the Managed Identity Object ID.

    2. 安装 Azure AD PowerShell 模块。Install the Azure AD PowerShell module. 使用 Connect-AzureAD 命令登录。Sign in by using the Connect-AzureAD command. 运行以下命令以创建组并将托管标识添加为成员。Run the following commands to create a group and add the managed identity as a member.

      $Group = New-AzureADGroup -DisplayName "<your group name>" -MailEnabled $false -SecurityEnabled $true -MailNickName "NotSet"
      Add-AzureAdGroupMember -ObjectId $Group.ObjectId -RefObjectId "<your data factory managed identity object ID>"
      
  2. 在 Azure 门户上为 Azure SQL Server 预配 Azure Active Directory 管理员(如果尚未执行该操作)。Provision an Azure Active Directory administrator for your Azure SQL server on the Azure portal, if you haven't already done so. Azure AD 管理员可以是 Azure AD 用户或 Azure AD 组。The Azure AD administrator can be an Azure AD user or Azure AD group. 如果为具有托管标识的组授予管理员角色,请跳过步骤 3 和 4。If you grant the group with managed identity an admin role, skip steps 3 and 4. 管理员将拥有对数据库的完全访问权限。The administrator will have full access to the database.

  3. 为 Azure AD 组创建包含的数据库用户Create contained database users for the Azure AD group. 使用 SSMS 等工具连接到要从/向其复制数据的数据库,其 Azure Azure 标识至少具有 ALTER ANY USER 权限。Connect to the database from or to which you want to copy data by using tools like SSMS, with an Azure AD identity that has at least ALTER ANY USER permission. 运行以下 T-SQL:Run the following T-SQL:

    CREATE USER [your AAD group name] FROM EXTERNAL PROVIDER;
    
  4. 授予 Azure AD 组所需的权限,就像通常为 SQL 用户和其他用户所做的那样。Grant the Azure AD group needed permissions, as you normally do for SQL users and others. 有关相应角色,请参阅数据库级别角色Refer to Database-Level Roles for appropriate roles. 例如,运行以下代码:For example, run the following code:

    ALTER ROLE [role name] ADD MEMBER [your AAD group name];
    

要对 Azure SQL 数据库托管实例使用托管身份验证,请按照以下步骤配置数据库:To use managed identity authentication for Azure SQL Database managed instance, follow these steps to configure your database:

  1. 在 Azure 门户上为托管实例预配 Azure Active Directory 管理员(如果尚未执行该操作)。Provision an Azure Active Directory administrator for your managed instance on the Azure portal, if you haven't already done so. Azure AD 管理员可以是 Azure AD 用户或 Azure AD 组。The Azure AD administrator can be an Azure AD user or Azure AD group. 如果为具有托管标识的组授予管理员角色,请跳过步骤 2-5。If you grant the group with managed identity an admin role, skip steps 2-5. 管理员将拥有对数据库的完全访问权限。The administrator will have full access to the database.

  2. 从 Azure 门户中查找数据工厂托管标识Find the data factory managed identity from the Azure portal. 转到数据工厂的“属性” 。Go to your data factory's Properties. 复制“托管标识应用 ID”(非托管标识对象 ID) 。Copy the Managed Identity Application ID (not Managed Identity Object ID).

  3. 将数据工厂托管标识转换为二进制类型。Convert the data factory managed identity to the binary type. 使用 SSMS 等工具和 SQL 或 Active Directory 管理员帐户连接到托管实例中的“master”数据库 。Connect to master database in your managed instance by using tools like SSMS, with your SQL or Active Directory admin account. 对“master”数据库运行以下 T-SQL,以获得二进制的托管标识应用 ID :Run the following T-SQL against master database to get your managed identity application ID as a binary:

    DECLARE @applicationId uniqueidentifier = '{your managed identity application ID}'
    select CAST(@applicationId AS varbinary)
    
  4. 在 Azure SQL 数据库托管实例中将数据工厂托管标识添加为用户。Add the data factory managed identity as a user in Azure SQL Database managed instance. 针对 master 数据库运行以下T-SQL :Run the following T-SQL against master database:

    CREATE LOGIN [{a name for the managed identity}] FROM EXTERNAL PROVIDER with SID = {your managed identity application ID as binary}, TYPE = E
    
  5. 向数据工厂托管标识授予所需的权限。Grant the data factory managed identity needed permissions. 有关相应角色,请参阅数据库级别角色Refer to Database-Level Roles for appropriate roles. 对要从/向其复制数据的数据库运行以下 T-SQL:Run the following T-SQL against the database from or to which you want to copy data:

    CREATE USER [{the managed identity name}] FOR LOGIN [{the managed identity name}] WITH DEFAULT_SCHEMA = dbo
    ALTER ROLE [role name] ADD MEMBER [{the managed identity name}]
    

然后为 OLE DB 连接管理器配置 OLE DB 提供程序。Then configure OLE DB provider for the OLE DB connection manager. 完成此操作的方法有两种:Here are the options to do this:

  • 在设计时进行配置。Configure at design time. 在 SSIS 设计器中,双击 OLE DB 连接管理器以打开“连接管理器”窗口 。In SSIS Designer, double-click the OLE DB connection manager to open the Connection Manager window. 在“提供程序”下拉列表中,选择适用于 SQL Server 的 Microsoft OLE DB 驱动程序In the Provider drop-down list, select Microsoft OLE DB Driver for SQL Server.

    备注

    下拉列表中的其他提供程序可能不支持托管标识身份验证。Other providers in the drop-down list might not support managed identity authentication.

  • 在运行时进行配置。Configure at runtime. 通过 SQL Server Management Studio (SSMS)Azure 数据工厂执行 SSIS 包活动运行包时,找到 OLE DB 连接管理器的连接管理器属性 ConnectionStringWhen you run the package via SQL Server Management Studio (SSMS) or Azure Data Factory Execute SSIS Package activity, find the connection manager property ConnectionString for the OLE DB connection manager. 将连接属性 Provider 更新为 MSOLEDBSQL(即 Microsoft OLE DB Driver for SQL Server)。Update the connection property Provider to MSOLEDBSQL (that is, Microsoft OLE DB Driver for SQL Server).

    Data Source=serverName;Initial Catalog=databaseName;Provider=MSOLEDBSQL;...
    

最后,为 OLE DB 连接管理器配置托管标识身份验证。Finally, configure managed identity authentication for the OLE DB connection manager. 完成此操作的方法有两种:Here are the options to do this:

  • 在设计时进行配置。Configure at design time. 在 SSIS 设计器中,右键单击 OLE DB 连接管理器,然后选择“属性” 。In SSIS Designer, right-click the OLE DB connection manager, and select Properties. 将属性 ConnectUsingManagedIdentity 更新为 TrueUpdate the property ConnectUsingManagedIdentity to True.

    备注

    目前,当你在 SSIS 设计器或 MicrosoftMicrosoft SQL Server 中运行 SSIS 包时,连接管理器属性 ConnectUsingManagedIdentity 不生效(表明托管标识身份验证不起作用)。Currently, the connection manager property ConnectUsingManagedIdentity doesn't take effect (indicating that managed identity authentication doesn't work) when you run SSIS package in SSIS Designer or MicrosoftMicrosoft SQL Server.

  • 在运行时进行配置。Configure at runtime. 通过 SSMS 或“执行 SQL 包”活动 运行包时,找到 OLE DB 连接管理器并将其属性 ConnectUsingManagedIdentity 更新为 TrueWhen you run the package via SSMS or an Execute SQL Package activity, find the OLE DB connection manager, and update its property ConnectUsingManagedIdentity to True.

    备注

    在 Azure-SSIS 集成运行时中,当使用托管标识身份验证来建立数据库连接时,OLE DB 连接管理器上预配的所有其他身份验证方法(例如,集成安全性和密码)将被重写。In Azure-SSIS integration runtime, all other authentication methods (for example, integrated security and password) preconfigured on the OLE DB connection manager are overridden when managed identity authentication is used to establish a database connection.

备注

要在现有包上配置托管标识身份验证,首选方法是至少使用最新 SSIS 设计器重新生成一次 SSIS 项目。To configure managed identity authentication on existing packages, the preferred way is to rebuild your SSIS project with the latest SSIS Designer at least once. 将该 SSIS 项目重新部署到 Azure SSIS 集成运行时,这样新的连接管理器属性 ConnectUsingManagedIdentity 才会自动添加到 SSIS 项目中的所有 OLE DB 连接管理器。Redeploy that SSIS project to your Azure-SSIS integration runtime, so that the new connection manager property ConnectUsingManagedIdentity is automatically added to all OLE DB connection managers in your SSIS project. 另一种方法是,直接在运行时结合使用属性重写和属性路径 \Package.Connections[{连接管理器名称}].Properties[ConnectUsingManagedIdentity]The alternative way is to directly use a property override with property path \Package.Connections[{the name of your connection manager}].Properties[ConnectUsingManagedIdentity] at runtime.

另请参阅See also

OLE DB 源 OLE DB Source
OLE DB 目标 OLE DB Destination
执行 SQL 任务 Execute SQL Task
Integration Services (SSIS) 连接Integration Services (SSIS) Connections