ADO.NET Connection Manager

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

An ADO.NET connection manager enables a package to access data sources by using a .NET provider. This connection manager is typically used to access data sources such as Microsoft SQL Server, and also data sources exposed through OLE DB and XML in custom tasks that are written in managed code by using a language such C#.

When you add an ADO.NET connection manager to a package, SQL Server Integration Services creates a connection manager that is resolved as an ADO.NET 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 ADO.NET. The value of ConnectionManagerType is qualified to include the name of the .NET provider that the connection manager uses.

ADO.NET Connection Manager Troubleshooting

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

When being read by an ADO.NET connection manager, data of certain SQL Server date data types will generate the results shown in the following table.

SQL Server data type Result
time, datetimeoffset The package fails unless the package uses parameterized SQL commands. To use parameterized SQL commands, use the Execute SQL Task in your package. For more information, see Execute SQL Task and Parameters and Return Codes in the Execute SQL Task.
datetime2 The ADO.NET connection manager truncates the millisecond value.

Note

For more information about SQL Server data types and how they map to Integration Services data types, see Data Types (Transact-SQL) and Integration Services Data Types.

ADO.NET Connection Manager Configuration

You can configure an ADO.NET connection manager in the following ways:

You can set properties through SSIS Designer or programmatically.

  • Provide a specific connection string configured to meet the requirements of the selected .NET 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.

Many of configuration options of the ADO.NET connection manager depend on the .NET provider that the connection manager uses.

For more information about the properties that you can set in SSIS Designer, click one of the following topic:

For information about configuring a connection manager programmatically, see ConnectionManager and Adding Connections Programmatically.

Configure ADO.NET Connection Manager

Use the Configure ADO.NET Connection Manager dialog box to add a connection to a data source that can be accessed by using a .NET Framework data provider, such as the SqlClient provider. The connection manager can use an existing connection, or you can create a new one.

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

Options

Data connections
Select an existing ADO.NET data connection from the list.

Data connection properties
View properties and values for the selected ADO.NET data connection.

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

Delete
Select a 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}]
    

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

  1. Configure at design time. In SSIS Designer, right-click the ADO.NET 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 SQL Server Management Studio (SSMS) or Azure Data Factory Execute SSIS Package activity, find the ADO.NET connection manager and update its property ConnectUsingManagedIdentity to True.

    Note

    In Azure-SSIS integration runtime, all other authentication methods (e.g., integrated authentication, password) preconfigured on the ADO.NET 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 ADO.NET connection managers in your SSIS project.

See Also

Integration Services (SSIS) Connections