This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](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(156.8, 69%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EH%3C/text%3E%3C/svg%3E)
We have a SQL Managed Instance that we are testing with. I'm attempting to create local packages in SSIS and have them run against the Managed Instance data.
When I manually run the SSIS package, I get an MFA validation. It works. However, when I try to schedule the package to run daily, it hangs and I assume it's due to the MFA.
What options do I have to get this to work? One that I can think of is to use a SQL account but I don't really want to change every single package and connection that I currently have. I'm currently taking existing connections and modifying them in notepad to point to the Managed Instance rather than our local server. It was working fine until our network team increased security and now require MFA to the database.
Can MFA be turned off just for the Managed Instance?
@HSB Thank you for reaching out.
You can configure Kerberos auth with MI, and continue to use Windows Integrated authentication:
or if you can switch to the latest OLE DB or ADO.NET you can use a non-interactive Entra ID auth flow, like Authentication=ActiveDirectoryServicePrincipal.
Regards,
Oury
@HSB I wanted to check if the above answer was helpful. If yes, please don't forget to mark as accept answer. Otherwise please comment below if any additional clarification is needed.
Regards,
Oury
We are able to use Windows Authentication. The issue is our MFA that I can't get around. Even when I run the package locally in VS, I get an MFA request. Once I do that, the package runs fine locally. I just can't get it to work when scheduled to run automatically in SQL Server Agent.
@Oury Ba-MSFT Let me give more detail to see if I'm missing something.
On our on-premise server, we set up an account as a Proxy for SSIS Package Execution. Running a package against our on-prem SQL, we always do a Run As and select this account that is set up and run the package from our SSIS Catalog.
I have set up this account in Azure as well and given it the same permissions.
What I'm attempting to do is to schedule a job from on our on-prem SQL Server but the connections in the package point to our Azure Managed Instance. This is not our plan to run long term but would like to do it now as we aren't using Azure for production just yet.
We need this functionality to test changes to a very large process so we can change the connections to point to our Azure Managed Instance to make sure everything works before publishing using the Lift and Shift method in Azure in Azure so we can use existing SSIS packages. This was working perfectly fine until MFA was required.
@Oury Ba-MSFT A little more information.
On-prem set up:
We have a proxy for AccountX for SSIS Package Execution. All packages are Run As this account. In SQL security, it is set up as [DOMAIN]\AccountX
When we added this user, we had to add it as AccountX@xxxxxxx.xxx
Could our issue of running local packages as the Proxy be failing due to the local vs cloud setup for this account? Meaning, the local proxy account doesn't have access to the Azure Managed Instance?
If so, any recommendations on how to proceed in running packages on-prem and connecting to Azure Managed Instances?