Why Does My Logic App's SQL Connection Break When Deployed via DevOps pipelines using Service Principal?

Hello Buchammagarivikram Reddy, Thanks for reaching out on Microsoft Q&A and really appreciate your patience while we looked into this.
The core of the issue is that a service principal (SPN), while it can deploy resources, often lacks the specific, underlying API permissions to "handshake" and authorize certain connectors, like the SQL connector, on its own. When you re-authenticate manually, your user account possesses the necessary delegated permissions, temporarily fixing the connection until the next deployment overwrites it.
Recommended Solutions:

1. Best Practice: Switch to Managed Identity for SQL Authentication

This is the recommended approach for any Azure-to-Azure authentication. A managed identity is a special type of service principal that Azure manages automatically, eliminating the need to handle credentials.

• Action: Enable a System-Assigned Managed Identity for your Logic App.

Permissions: Grant this managed identity the appropriate permissions on your SQL Server database (e.g., as an Azure AD user in the database).

Result: The Logic App will now use its managed identity to authenticate, and the connection will not break on subsequent deployments.

For guidance, please refer to the Microsoft documentation on Using a Managed Identity with Azure SQL.

2. Manual Automation: Re-authorize the API Connection Post-Deployment

If you cannot use a managed identity, you can add a step to your deployment pipeline to explicitly re-authorize the connection after the Logic App has been deployed.

• Action: Add a step to your pipeline that uses either the Azure CLI or a REST API call to "patch" the Microsoft.Web/connections resource. This step will pass the SQL credentials securely, re-authenticating the connection.
• Example Command: You would use a command similar to az resource update --ids <connection-id> --set properties.parameterValues.

Result: The connection is authenticated programmatically, and the Logic App works immediately after deployment.

The documentation on Authorizing API Connections via ARM provides the necessary details for this approach.

3. Assign Required API Permissions to the Service Principal

This solution directly addresses the root cause of the permission issue. You can manually grant the service principal the permissions it needs.

• Action: In the Microsoft Entra admin center, navigate to your App Registration and add the following API permissions to Microsoft Graph or the specific connector's API:
  • APIConnectors.ReadWrite.All
  • APIConnectors.Read.All
• Important: An administrator must grant admin consent for these permissions.

Result: The service principal can now manage and authorize the connection during deployment, preventing the manual re-authentication loop.

You can find more information on these permissions in the Microsoft Entra built-in roles.

4. Use ARM Template Deployment Scripts

This is a powerful method that embeds the re-authentication logic directly into your ARM deployment.

• Action: Add a Microsoft.Resources/deploymentScripts resource to your ARM template. This script can contain the Azure CLI or PowerShell commands to re-authenticate the connection.

Result: The authentication step becomes an atomic part of your deployment, ensuring the connection is always valid after a successful deployment.

For further reading on this approach, see the Microsoft documentation on Deployment Scripts in ARM.

Please feel free to share an update at your convenience or let me know if any further clarification or support would be helpful. Thank You!