OLE DB Connection Manager

APPLIES TO: yesSQL Server, including on Linux yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

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

Note

The SQL Server Native Client 11.0 OLEDB provider does not support the new connection string key words (MultiSubnetFailover=True) for Multi-Subnet Failover Clustering. For more information, see the SQL Server Release Notes and the blog post, Always On Multi-Subnet Failover and SSIS, on www.mattmasson.com.

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 OLE DB connection manager. For example, the OLE DB source and OLE DB destination use this connection manager to extract and load data, and the Execute SQL task can use this connection manager to connect to a SQL Server database to run queries.

The OLE DB connection manager is also used to access OLE DB data sources in custom tasks written in unmanaged code that uses a language such as C++.

When you add an OLE DB connection manager to a package, Integration Services creates a connection manager that will resolve to an OLE DB connection at run time, 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.

The OLE DB connection manager can be configured 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 that is created from the connection manager is retained at run time.

Logging

You can log the calls that the OLE DB connection manager makes to external data providers. You can use this logging capability to troubleshoot the connections that the OLE DB connection manager makes to external data sources. 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.

Configuration of 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 OLE DB 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 OLE DB Connection Manager

Use the Configure OLE DB Connection Manager dialog box to add a connection to a data source, which can be either a new connection 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 OLE DB provider than earlier versions of Access. For more information, see Connect to an Access Database.

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

Options

Data connections
Select an existing OLE DB data connection from the list.

Data connection properties
View properties and values for the selected OLE DB data connection.

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

Delete
Select a data connection, and then delete it by using the Delete button.

Managed Identities for Azure Resources Authentication

When running SSIS packages on Azure-SSIS integration runtime in Azure Data Factory, you can use the managed identity that is 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.

To use managed identity authentication for Azure SQL Database, follow these steps to configure your database:

  1. Create a group in Azure AD. Make the managed identity a member of the group.

    1. Find the data factory managed identity from the Azure portal. Go to your data factory's Properties. Copy the Managed Identity Object ID.

    2. Install the Azure AD PowerShell module. 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. Provision an Azure Active Directory administrator for your Azure SQL server on the Azure portal if you haven't already done so. The Azure AD administrator can be an Azure AD user or Azure AD group. 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. Create contained database users for the Azure AD group. 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. Run the following T-SQL:

    CREATE USER [your AAD group name] FROM EXTERNAL PROVIDER;
    
  4. Grant the Azure AD group needed permissions as you normally do for SQL users and others. For example, run the following code:

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

To use managed identity authentication for Azure SQL Database Managed Instance, follow these steps to configure your database:

  1. Provision an Azure Active Directory administrator for your managed instance on the Azure portal if you haven't already done so. The Azure AD administrator can be an Azure AD user or Azure AD group. 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. Find the data factory managed identity from the Azure portal. Go to your data factory's Properties. Copy the Managed Identity Application ID (NOT Managed Identity Object ID).

  3. Convert the data factory managed identity to binary type. Connect to master database in your managed instance by using tools like SSMS, with your SQL/Active Directory admin account. Run the following T-SQL against master database to get your managed identity application ID as binary:

    DECLARE @applicationId uniqueidentifier = '{your managed identity application ID}'
    select CAST(@applicationId AS varbinary)
    
  4. Add the data factory managed identity as a user in Azure SQL Database Managed Instance. 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. 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 db_owner ADD MEMBER [{the managed identity name}]
    

Then configure OLE DB provider for the OLE DB connection manager. There are two options to do this.

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

    Note

    Other providers in the drop-down list MIGHT NOT support managed identity authentication.

  2. Configure at run time. When you execute 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 and update the connection property Provider to MSOLEDBSQL (i.e, Microsoft OLE DB Driver for SQL Server).

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

Finally configure managed identity authentication for the OLE DB connection manager. There are two options to do this.

  1. Configure at design time. In SSIS Designer, right-click the OLE DB connection manager and click Properties to open the Properties Window. Update the property ConnectUsingManagedIdentity to True.

    Note

    Currently the connection manager property ConnectUsingManagedIdentity DOES NOT take effect (indicating that managed identity authentication does not work) when you run SSIS package in SSIS Designer or Microsoft SQL Server.

  2. Configure at run time. When you execute the package via SSMS or Execute SQL Package activity, find the OLE DB connection manager and update its property ConnectUsingManagedIdentity to True.

    Note

    In Azure-SSIS integration runtime, all other authentication methods (e.g., integrated security, password) preconfigured on the OLE DB connection manager will be overridden when managed identity authentication is used to establish database connection.

Note

To configure managed identity authentication on existing packages, please be sure to rebuild your SSIS project with the latest SSIS Designer at least once and redeploy that SSIS project to your Azure-SSIS integration runtime so that the new connection manager property ConnectUsingManagedIdentity will automatically be added to all OLE DB connection managers in your SSIS project.

See Also

OLE DB Source
OLE DB Destination
Execute SQL Task
Integration Services (SSIS) Connections