Skip to content

Latest commit

 

History

History
159 lines (105 loc) · 13.6 KB

ole-db-connection-manager.md

File metadata and controls

159 lines (105 loc) · 13.6 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords
OLEDB connection manager
An OLEDB connection manager enables a package to connect to a data source by using an OLEDB provider.
chugugrace
chugu
10/13/2023
sql
integration-services
conceptual
sql13.dts.designer.oledbconnection.f1
OLEDB connection manager
data sources [Integration Services], connections
connection managers [Integration Services], OLEDB
connections [Integration Services], OLEDB

OLEDB connection manager

[!INCLUDEsqlserver-ssis]

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 [!INCLUDEssNoVersion] can use the [!INCLUDEmsCoName] OLEDB Provider for [!INCLUDEssNoVersion].

Note

The [!INCLUDEssNoVersion] 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 [!INCLUDEmsCoName] Office Excel 2007 or [!INCLUDEmsCoName] 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 [!INCLUDEssNoVersion] [!INCLUDEssISnoversion] 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 [!INCLUDEssNoVersion] 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, [!INCLUDEssISnoversion] 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.

[!INCLUDE entra-id]

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 [!INCLUDEssIS] Designer, or programmatically. For more information about the properties that you can set in [!INCLUDEssIS] 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 [!INCLUDEmsCoName] 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 [!INCLUDEmsCoName] 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