Редактиране

Споделяне чрез

OLEDB connection manager

  • Статия

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

An OLEDB connection manager enables a package to connect to a data source by using an OLEDB provider. For example, an OLEDB connection manager that connects to SQL Server can use the Microsoft OLEDB Provider for SQL Server.

Note

The SQL Server Native Client 11.0 OLEDB provider doesn't support the new connection string key words (MultiSubnetFailover=True) for multi-subnet failover clustering. For more information, see the SQL Server Release Notes.

Note

If the data source is Microsoft Office Excel 2007 or Microsoft Office Access 2007, the data source requires a different data provider than earlier versions of Excel or Access. For more information, see Connect to an Excel Workbook and Connect to an Access Database.

Several SQL Server Integration Services tasks and data flow components use an OLEDB connection manager. For example, the OLEDB source and OLEDB destination use this connection manager to extract and load data. The Execute SQL task can use this connection manager to connect to a SQL Server database to run queries.

You can also use the OLEDB connection manager to access OLEDB data sources in custom tasks written in unmanaged code that uses a language such as C++.

When you add an OLEDB connection manager to a package, Integration Services creates a connection manager that resolves to an OLEDB connection at runtime, sets the connection manager properties, and adds the connection manager to the Connections collection on the package.

The ConnectionManagerType property of the connection manager is set to OLEDB.

Configure the OLEDB 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.

Note

Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).

Log calls and troubleshoot connections

You can log the calls that the OLEDB connection manager makes to external data providers. You can then troubleshoot the connections that the OLEDB connection manager makes to external data sources. To log the calls that the OLEDB 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.

Configure the OLEDB connection manager

You can set properties through SSIS Designer, or programmatically. For more information about the properties that you can set in SSIS Designer, see Configure OLEDB Connection Manager. For information about configuring a connection manager programmatically, see the documentation for T:Microsoft.SqlServer.Dts.Runtime.ConnectionManager class in the Developer Guide.

Configure OLEDB connection manager

Use the Configure OLEDB Connection Manager dialog box to add a connection to a data source. This connection can be new, or a copy of an existing connection.

Note

If the data source is Microsoft Office Excel 2007, the data source requires a different connection manager than earlier versions of Excel. For more information, see Connect to an Excel Workbook.

If the data source is Microsoft Office Access 2007, the data source requires a different OLEDB provider than earlier versions of Access. For more information, see Connect to an Access Database.

To learn more about the OLEDB connection manager, see OLEDB Connection Manager.

Options

Data connections
Select an existing OLEDB data connection from the list.

Data connection properties
View properties and values for the selected OLEDB data connection.

New
Create an OLEDB data connection by using the Connection Manager dialog box.

Delete
Select a data connection, and then delete it by selecting Delete.

Managed identities for Azure resources authentication

When running SSIS packages on Azure-SSIS integration runtime (IR) in Azure Data Factory (ADF), you can use Microsoft Entra authentication with system-assigned or user-assigned managed identities for your ADF to access your Azure SQL Database or SQL Managed Instance. Your Azure-SSIS IR can access and copy data from or to your database using this managed identity.

Note

To use Microsoft Entra authentication with the managed identity for your ADF to access Azure SQL Database server, follow these steps:

  1. Provision a Microsoft Entra administrator for your logical server in Azure portal, if you haven't already done so. The Microsoft Entra administrator can be a Microsoft Entra user or group. If you assign a group as the admin, you can add your ADF's managed identity to the group and skip steps 2 and 3. The administrator has full access to your logical server for Azure SQL Database.

  2. Create a contained database user for the managed identity of your ADF. Use SQL Server Management Studio (SSMS) to connect to the database with a Microsoft Entra user that has at least ALTER ANY USER permission. Run the following T-SQL statement:

    CREATE USER [your managed identity name] FROM EXTERNAL PROVIDER;

    If you use the system-assigned managed identity for your ADF, then your managed identity name should be your ADF name. If you use a user-assigned managed identity for your ADF, then your managed identity name should be the specified user-assigned managed identity name.

  3. Grant the managed identity for your ADF the required permissions, as you normally do for SQL users. Refer to Database-level roles for appropriate roles. Run the following T-SQL statement. For more options, see this article.

    EXEC sp_addrolemember [role name], [your managed identity name];

To use Microsoft Entra authentication with the managed identity for your ADF to access Azure SQL Managed Instance, follow these steps:

  1. Provision a Microsoft Entra administrator for your Azure SQL Managed Instance in Azure portal, if you haven't already done so. The Microsoft Entra administrator can be a Microsoft Entra user or group. If you assign a group as the admin, you can add your ADF's managed identity to the group and skip steps 2 and 3. The administrator has full access to your Azure SQL Managed Instance.

  2. Create a login assigned to the managed identity for your ADF. On SSMS, connect to your Azure SQL Managed Instance using SQL Server account that is a sysadmin. In master database, run the following T-SQL statement:

    CREATE LOGIN [your managed identity name] FROM EXTERNAL PROVIDER;

    If you use the system managed identity for your ADF, then your managed identity name should be your ADF name. If you use a user-assigned managed identity for your ADF, then your managed identity name should be the specified user-assigned managed identity name.

  3. Create a contained database user representing the managed identity for your ADF. Connect to the database from or to which you want to copy data using SSMS and run the following T-SQL statement:

    CREATE USER [your managed identity name] FROM EXTERNAL PROVIDER;

  4. Grant the managed identity for your ADF the required permissions, as you normally do for SQL users. Run the following T-SQL statement. For more options, see this article.

    ALTER ROLE [role name e.g., db_owner] ADD MEMBER [your managed identity name];

You can then configure the OLEDB provider on your OLEDB connection manager. Here are the options to do this:

  • Configure at design time. In SSIS Designer, double-click on your OLEDB connection manager to open the Connection Manager window. In the Provider drop-down list, select Microsoft OLEDB Driver for SQL Server.

    Note

    Other providers in the drop-down list might not support Microsoft Entra authentication with your ADF's managed identity.

  • Configure at run time. When you run your package via SSMS or Execute SSIS Package activity in ADF pipeline, find the connection manager property ConnectionString for OLEDB connection manager. Update the connection property Provider to MSOLEDBSQL (that is Microsoft OLEDB Driver for SQL Server).

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

You can now configure Microsoft Entra authentication with the managed identity of your ADF on the OLEDB connection manager. Here are the options to do this:

  • Configure at design time. In SSIS Designer, right-click on your OLEDB connection manager, and select Properties. Update the property ConnectUsingManagedIdentity to True.

    Note

    Currently, the connection manager property ConnectUsingManagedIdentity doesn't take effect when you run your package in SSIS Designer or on SQL Server, indicating that authentication with the managed identity of your ADF doesn't work.

  • Configure at run time. When you run your package via SSMS or Execute SSIS Package activity in ADF pipeline, find the OLEDB connection manager and update its property ConnectUsingManagedIdentity to True.

    Note

    On Azure-SSIS IR, all other authentication methods (for example, integrated security and password) preconfigured on your OLEDB connection manager are overridden when using Microsoft Entra authentication with a managed identity.

To configure Microsoft Entra authentication with the managed identity for your ADF on your existing packages, the preferred way is to rebuild your SSIS project with the latest SSIS Designer at least once. Redeploy your SSIS project to run on Azure-SSIS IR, so that the new connection manager property ConnectUsingManagedIdentity is automatically added to all OLEDB connection managers in your project. Alternatively, you can use property overrides, with the property path \Package.Connections[{the name of your connection manager}].Properties[ConnectUsingManagedIdentity] assigned to True at run time.

See also