Forum Discussion

HSBHSB's avatar
HSBHSB
Copper Contributor
Mar 14, 2024

ADF Execute SSIS Package Failed to Acquire Connection

I have deployed an SSIS package into SSISDB that has connections to Azure SQL Managed Instance. The connections use Azure Directory Integrated Authentication. I set up the SSIS IR to use a sql admin username and password if that matters and not a Windows account.

 

Here's the connection string in the connection managers in my pipeline. How can I pass a user/password for Active Directory Integrated or do I need to change this entirely?

Data Source=xxxxxxxxx,3342;Initial Catalog=MYDATABASE;Authentication=Active Directory Integrated;Application Name=MYAPPLICATION-{xxxxx}xxxxxxxxxxx,3342.Stage_D;

 

When I try to debug the pipeline in ADF, I get the following error:

Failed to acquire connection \"MYDATABASE\". Connection may not be configured correctly or you may not have the right permissions on this connection.

 

How do I tell the pipeline to execute the package as a specific windows user who is a sys admin in Azure? I assume this error is because of the integrated authentication and it can't tell who I am.

 

Any suggestions?

1 Reply

  • HSBHSB's avatar
    HSBHSB
    Copper Contributor

    I was able to get it to work by deleting SSISDB, updating the IR to use a managed identity and granting that identity access to the databases involved.  In the package connection managers, I changed the ConnectUsingManagedIdentity to True. Is this the best/proper way to get it to work?

     

     

Resources