Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,623 questions
How to connect to Azure SQL Managed Instance in SSIS using a user-assigned managed identity from an Azure Data Factory pipeline
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(28.799999999999997, 51%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDL%3C/text%3E%3C/svg%3E)
Danny Looby
0
Reputation points
Greetings,
I'm trying to connect to an Azure SQL Managed Instance in SSIS using a user-assigned managed identity called from a pipeline in Azure Data Factory. Inside my pipeline, I'm using an SSIS package activity to call my SSIS package. I have followed the steps outlined in the link below, but I haven't had any success.
The error I receive is as follows:
Execute SQL Task:Error: Failed to acquire connection "my connection"D". Connection may not be configured correctly or you may not have the right permissions on this connection.
I tried to enter connection properties inside the "Property Overrides" of the Execute SSIS package activity by setting
\Package.Connections[{the name of your connection manager}].Properties[ConnectUsingManagedIdentity] to true and setting
\Package.Connections[{the name of your connection manager}].Properties[UserName] to the name of the user-assigned managed identity.
I added a script task inside my SSIS package to log the the connection string using Dts.Events.FireInformation. But the connection string that was set during the creation doesn't appear to be changing.
Here are some ideas I have kicked around. I am limited on what I can do so I have not yet attempted the options below. Hoping someone could shed some light on this issue before I move forward so that I can pull in resources from our DevOps team to help.
- Could this have anything to do with how the SSIS integration runtime was setup? The option to use user-assigned managed identity for data factory was not selected during initial setup. Wondering if this could be causing the issue...
- Does the user-assigned managed identity have to be in the same resource group as the SQL Managed Instance?
Any suggestions would be great! Thank you.
Azure Data Factory
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(86.4, 36%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBM%3C/text%3E%3C/svg%3E)
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator2023-05-11T22:15:16.6066667+00:00 Hello Danny Looby,
Welcome to the MS Q&A platform.
When you configure the SSIS IR using the admin user name and the password, did you check the "Test connection"?
I am trying to set up an environment to reproduce the issue from my end. I will get back to you with more details.
-
Danny Looby 0 Reputation points
2023-05-12T13:11:16.0066667+00:00 Hi @Bhargava-MSFT ,
I was not part of the configuration of the SSIS integration runtime. However, I can say that we are able to run SSIS packages using the same SSIS integration runtime by passing SQL Authentication credentials into a package and plugging those values into the appropriate properties of the expression builder in the connection manager. Even though this works, it is not the desired route we want to take long term. Instead, we would like to use user-assigned managed identity.
-
Danny Looby 0 Reputation points
2023-05-12T13:22:41.55+00:00
-
Danny Looby 0 Reputation points
2023-05-12T21:24:41.3066667+00:00 Another thought that crossed my mind - does the SQL Managed Instance and user-assigned managed identity have to be in the same resource group for this to work? I believe they are in the same subscription, but not sure if they're in the same resource group. I will have to reach out to our DevOps team to verify as I don't have permission to see this.
-
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator
2023-05-15T22:05:55.5466667+00:00 Hello Danny Looby,
Good to know that you can run the SSIS package using SQL authentication.
The user-assigned managed identity need not be in the same resource group as the SQL Managed Instance. However, it needs to have the necessary permissions to access the SQL Managed Instance.
Please let me know if you have any further questions.
-
Danny Looby 0 Reputation points
2023-05-16T16:26:43.5+00:00 Hi @Bhargava-MSFT ,
Thank you for the input. I have two more questions for you. Before I get to those, I will address the recommendation you provided regarding the necessary permissions.
On the SQL managed instance, I created a login assigned to the user assigned managed identity, database user, and granted the database user membership to the db_owner role using the logic shown below. I assume this is what you're referring to when you mentioned the SQL Managed Instance having necessary permission to access the SQL Managed Instance. If there's something I'm missing, please let me know.
Also, I was able to successfully impersonate the user assigned managed identity in SSMS using "EXECUTE AS LOGIN = <user assigned managed identity>". From there I executed a simple insert statement which mimics what my SSIS package is doing.
Questions:
- Is it necessary to add the user assigned managed identity to the SQL Managed Instance in the portal? See below. This was not stated in the documentation, but just taking guesses at this point since I haven't had success.
- I'm wondering if the way I'm setting the properties in Azure Data Factory is not correct. Could you verify what I have setup? Check out the screenshots below. You will see that I'm setting the ConnectUsingManagedIdentity property to True in the connection manager AND assigning the user-assigned managed identity name to the UserName property of the connection manager property overrides.
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(147.20000000000002, 27%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EV%3C/text%3E%3C/svg%3E)
VasimTamboli 5,215 Reputation points2023-05-16T17:36:11.5633333+00:00 To connect to an Azure SQL Managed Instance in SSIS using a user-assigned managed identity from an Azure Data Factory pipeline, you need to follow specific steps to configure the connection correctly. Here are the recommended steps:
Ensure that the user-assigned managed identity is assigned to the Azure Data Factory (ADF) instance that is running the pipeline. You can assign the managed identity to the ADF instance by going to the Azure portal, selecting your ADF instance, and navigating to the "Identity" section. Assign the user-assigned managed identity from there.
Grant the necessary permissions to the user-assigned managed identity on the Azure SQL Managed Instance. You need to provide the managed identity with appropriate permissions to access the database objects and perform the required operations. Typically, you would grant the managed identity the necessary permissions at the database level or specific objects level (e.g., tables, stored procedures) based on your requirements.
Configure the SSIS package to use the user-assigned managed identity for authentication. Follow these steps:
- Open your SSIS package in SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS).
- Locate the connection manager that connects to the Azure SQL Managed Instance.
- Open the connection manager properties and set the "ConnectUsingManagedIdentity" property to "True".
- Optionally, you can remove the username and password from the connection manager, as they are not required when using managed identity authentication.
In the Azure Data Factory pipeline, configure the Execute SSIS Package activity to use the correct connection manager. Follow these steps:
- Open your Azure Data Factory pipeline.
- Locate the Execute SSIS Package activity that calls your SSIS package.
- In the activity properties, set the "IntegrationRuntimeReference" to the appropriate SSIS integration runtime that has access to the user-assigned managed identity.
- In the "Property Overrides" section, add the following property overrides:
- \Package.Connections[{the name of your connection manager}].Properties[ConnectUsingManagedIdentity] = true
- \Package.Connections[{the name of your connection manager}].Properties[UserName] = the name of the user-assigned managed identity
Ensure that you replace "{the name of your connection manager}" with the actual name of your connection manager in the SSIS package.
Regarding your additional questions:
The SSIS integration runtime being used should have the "Use Managed Identity for Azure Resources" option enabled. This ensures that the SSIS package activity can use the user-assigned managed identity. You can check and enable this option by going to the Azure portal, selecting your SSIS integration runtime, and navigating to the "Managed Identity" section.
The user-assigned managed identity does not necessarily need to be in the same resource group as the SQL Managed Instance. The important aspect is that the user-assigned managed identity is assigned to the Azure Data Factory instance that is running the pipeline.
By following these steps and ensuring the correct configuration, you should be able to connect to the Azure SQL Managed Instance in SSIS using the user-assigned managed identity from the Azure Data Factory pipeline. If you continue to encounter issues, consider checking the logs and monitoring outputs for more detailed error messages that can help identify the problem.
-
Danny Looby 0 Reputation points
2023-05-16T19:01:56.6233333+00:00 Hi @VasimTamboli ,
Thank you for the feedback. I need further clarification on your instruction regarding the SSIS Integration runtime option "Use Managed Identity for Azure Resources".
The SSIS integration runtime being used should have the "Use Managed Identity for Azure Resources" option enabled. This ensures that the SSIS package activity can use the user-assigned managed identity. You can check and enable this option by going to the Azure portal, selecting your SSIS integration runtime, and navigating to the "Managed Identity" section.
I'm fairly new to Azure, so please bear with me. First, I'm unable to navigate directly to the SSIS integration runtime in the portal. The only way I can see the SSIS integration runtime is if I first navigate to the data factory and open azure data factory studio. From there, I can see the Intergration runtime in the "Manage" section (see below), but I cannot open the ssis integration runtime. I can only view the JSON. I don't see a "Managed Identity" section either.
Could you provide screenshots to help me navigate to the correct place?
-
Danny Looby 0 Reputation points
2023-05-16T19:27:46.47+00:00 aaaaaa
-
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator
2023-05-16T19:54:29.4366667+00:00 Hello Danny Looby,
Yes, I was referring to the login assigned to the user assigned managed identity.
For your first question, You don't need to add the user-assigned managed identity to the SQL MI in the portal.
For the other question, my understanding is
usernamethe property is not used when connecting using the managed identity. instead, useConnectUsingManagedIdentityproperty to specify the connectionThe
UserNameproperty is typically used when connecting using SQL Server AuthenticationI hope this helps.
-
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator
2023-05-18T00:04:21.8666667+00:00 Hello Danny Looby,
I am checking to see if you have any further questions here.
-
Danny Looby 0 Reputation points
2023-05-18T19:09:38.4566667+00:00 Hi @Bhargava-MSFT ,
The issue still persists. I followed the instructions that @VasimTamboli provided in the answer, but still no luck. I'm waiting on clarification on the instruction below. More detail can be found in my comment below the answer.
The SSIS integration runtime being used should have the "Use Managed Identity for Azure Resources" option enabled. This ensures that the SSIS package activity can use the user-assigned managed identity. You can check and enable this option by going to the Azure portal, selecting your SSIS integration runtime, and navigating to the "Managed Identity" section.
-
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator
2023-05-18T19:53:23.96+00:00 Hello Danny Looby,
There is no managed identity section on the SSIS integration runtime section. Not sure where @VasimTamboli got these details.
Hello @VasimTamboli,
Could you please provide more details on the above comment?
-
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator
2023-05-22T23:34:41.94+00:00 Hello Danny Looby,
I am checking to see if you have further questions about my previous response.
-
Danny Looby 0 Reputation points
2023-05-23T03:47:21.6733333+00:00 Hi @Bhargava-MSFT ,
I have another question. In response to your comment about the username property...
"For the other question, my understanding is
usernamethe property is not used when connecting using the managed identity. instead, useConnectUsingManagedIdentityproperty to specify the connectionThe
UserNameproperty is typically used when connecting using SQL Server Authentication"What happens if we have multiple user-assign managed identities assigned to ADF? In that case, wouldn't we have to explicitly mention which managed identity to use inside the Execute SSIS Package activity?
I found something in the documentation that supports this theory. Here is the link - https://learn.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/managed-identities-faq#what-identity-will-imds-default-to-if-dont-specify-the-identity-in-the-request
-
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator
2023-05-26T22:41:54.7266667+00:00 Hello Danny Looby,
If you have multiple user-assigned managed identities assigned to ADF, you would need to specify which managed identity to use when connecting to the Azure SQL Managed Instance. You need to set the managed identity property to the name of the user-assigned managed identity you want to use here.
-
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator
2023-05-31T00:49:35.46+00:00 Hello Danny Looby,
I hope you are doing well. Please let me know if you still have any further questions.
-
Danny Looby 0 Reputation points
2023-06-01T15:00:46.3366667+00:00 Hi @Bhargava-MSFT ,
I couldn't find any documentation or examples that show how to explicitly set the user-assigned managed identity from ADF Execute SSIS package activity. I have tried two different methods but still not working and I receive the following error.
Failed to configure a connection property that has the following path: \Package.Connections[1001].Properties[ConnectUsingManagedIdentity]. The connection “1001” is not found. This error is throw by Connections collection when the specific connection element is not found.
Method 1
Set ConnectUsingManagedIdentity and UserName in the Connection managers of the Execute SSIS package activity in ADF.
Method 2
Set ConnectUsingManagedIdentity and UserName in the Property Overrides of the Execute SSIS package activity in ADF.
Sign in to comment
Sign in to answer