Forum Discussion
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
- HSBHSBCopper 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?