Local SSIS Package Issues with MFA on Managed Instance

HSB 45 Reputation points
2024-06-13T14:00:26.4533333+00:00

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?

Azure SQL Database
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Oury Ba-MSFT 17,791 Reputation points Microsoft Employee
    2024-06-14T22:40:26.5066667+00:00

    @HSB Thank you for reaching out. 

    You can configure Kerberos auth with MI, and continue to use Windows Integrated authentication:

     How to set up Windows Authentication for Azure SQL Managed Instance using Microsoft Entra ID and Kerberos - Azure SQL Managed Instance | Microsoft Learn

    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