ADO.NET connection manager

Applies to: SQL Server (all supported versions) SSIS Integration Runtime in Azure Data Factory

An ADO.NET connection manager enables a package to access data sources by using a .NET provider. Typically, you use this connection manager to access data sources such as Microsoft SQL Server. You can also access data sources exposed through OLE DB and XML in custom tasks that are written in managed code, by using a language such as 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 runtime. It 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 then 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 generates 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 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 created from the connection manager is retained at runtime.

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, see Configure ADO.NET Connection Manager.

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. For example, one such provider is 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 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 Azure Active Directory (AAD) authentication with the specified system/user-assigned managed identity for your ADF to access Azure SQL Database server/Managed Instance. Your Azure-SSIS IR can access and copy data from or to your database by using this managed identity.

Note

When you use AAD authentication to access Azure SQL Database server/Managed Instance, you might encounter a problem related to package execution failure or unexpected behavior change. For more information, see AAD features and limitations.

To use AAD authentication with the specified system/user-assigned managed identity for your ADF to access Azure SQL Database server, follow these steps:

  1. Provision an AAD administrator for your Azure SQL Database server in Azure portal, if you haven't already done so. The AAD administrator can be an AAD user or group. If you grant the group with specified system/user-assigned managed identity for your ADF an admin role, skip step 2 - 3. The administrator will have full access to your Azure SQL Database server.

  2. Create a contained database user to represent the specified system/user-assigned managed identity for your ADF. Connect to the database from or to which you want to copy data using SQL Server Management Studio (SSMS) with an AAD 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 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 specified system/user-assigned 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 AAD authentication with the specified system/user-assigned managed identity for your ADF to access Azure SQL Managed Instance, follow these steps:

  1. Provision an AAD administrator for your Azure SQL Managed Instance in Azure portal, if you haven't already done so. The AAD administrator can be an AAD user or group. If you grant the group with specified system/user-assigned managed identity for your ADF an admin role, skip step 2 - 4. The administrator will have full access to your Azure SQL Managed Instance.

  2. Create a login assigned to the specified system/user-assigned 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 specified system/user-assigned 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 specified system/user-assigned 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];
    

Finally, you can configure AAD authentication with the specified system/user-assigned managed identity for your ADF on the ADO.NET connection manager. Here are the options to do this:

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

    Note

    Currently, the connection manager property ConnectUsingManagedIdentity doesn't take effect (indicating that AAD authentication with the specified system/user-assigned managed identity for your ADF doesn't work) when you run your package in SSIS Designer or on SQL Server.

  • Configure at run time. When you run your package via SSMS or Execute SSIS Package activity in ADF pipeline, find the ADO.NET 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 ADO.NET connection manager are overridden when using AAD authentication with the specified system/user-assigned managed identity for your ADF.

To configure AAD authentication with the specified system/user-assigned 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 ADO.NET connection managers in your project. The alternative way is to directly 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