Power Automate -SQL connection keeps disconnecting
Dive into practical advice and clever hacks to make the most of your Power Platform projects every day.
Scott Priebe
5/8/20243 min read
This is a common and frustrating issue. The cloud flow's SQL connection reference using a service account often requires periodic re-authentication. This isn't a bug, but rather a result of security and token-based policies.
Here’s a breakdown of why it happens, how to fix it, and the sources that explain these behaviors.
Why the SQL Connection Breaks
The core issue is that Power Automate connections are not permanent. When you authenticate with a service account (which is technically a standard user account), the connection relies on OAuth 2.0 tokens, which are designed to expire for security reasons.
There are two primary causes for the connection breaking:
1. Token Inactivity Expiration (Most Likely Cause)
○ How it works: To maintain a connection, Power Automate uses a refresh token. This token allows it to get a new access token without you having to sign in again.
○ The policy: By default, Microsoft Entra ID (formerly Azure AD) has a policy that revokes refresh tokens after 90 days of inactivity.
○ The result: If your flow (or any flow using that exact same connection) doesn't run at least once in a 90-day period, the token is considered inactive and is revoked. The next time the flow tries to run, the connection fails and you must re-authenticate it.
2. Credential or Policy Changes
○ Password Change: If an IT administrator resets the service account's password (or it expires based on a password policy), the credentials stored in the Power Automate connection are no longer valid.
○ MFA or Conditional Access: If Multi-Factor Authentication (MFA) is enabled, disabled, or modified for the service account, it invalidates all of its existing sessions and tokens. Similarly, a new or updated Entra ID (Azure AD) Conditional Access policy (e.g., "block sign-ins from outside the country") can suddenly prevent the connection from authenticating.
○ Account Status: The service account was disabled or deleted.
How to Fix This
You have two main solutions. The first is a simple workaround; the second is the architecturally-correct, long-term solution.
Solution 1: The "Keep-Alive" Flow (Workaround)
This solution directly addresses the 90-day inactivity problem. If your flow only runs quarterly or on-demand, you can create a separate, simple flow to keep the connection active.
1. Create a new Scheduled cloud flow.
2. Set the schedule to run once per week or once per month.
3. Add an action that uses the specific SQL connection reference in question.
4. The action can be a very simple, low-impact query, such as SELECT 1.
5. Save and turn on the flow.
This "touches" the connection, proving it's active and resetting the 90-day inactivity timer. This is a common workaround, but it will not protect you from password changes or MFA policies (Cause #2).
Solution 2: Use a Service Principal (Recommended Solution)
This is the most robust and recommended method for production or mission-critical flows. Instead of using a user-based "service account," you use an application-based Service Principal.
A Service Principal is a non-human identity for your application or flow. It authenticates using its own credentials (a Client Secret or Certificate) instead of a username and password.
Benefits of Using a Service Principal:
● No Password Expiration: You manage a client secret, which you can set to expire in 1 or 2 years, giving you a very long and predictable maintenance window.
● No MFA: Service Principals are non-interactive and are not subject to user-based MFA policies.
● No Inactivity Expiration: Authentication is not subject to the 90-day user inactivity policy.
● Clear Ownership: The flow is owned by the application (the Service Principal), not a user account that could be disabled if someone leaves the company.
How to Set it Up (High-Level):
1. Register an Application in Azure AD (Entra ID).
2. Create a Client Secret for that application.
3. Grant SQL Server Permissions: In your SQL database, you must create a user for the Service Principal and grant it the necessary permissions (like EXECUTE on your stored procedures).
○ Run the following T-SQL command in your database:
CREATE USER [Your-App-Registration-Name] FROM EXTERNAL PROVIDER;
○ Then, grant it permissions:
ALTER ROLE db_datareader ADD MEMBER [Your-App-Registration-Name];
GRANT EXECUTE ON OBJECT::[YourStoredProcedureName] TO [Your-App-Registration-Name];
4. Create the Connection: In your flow, when adding the SQL Server connection, select the "Connect with Service Principal" authentication type and provide your Tenant ID, Client ID, and Client Secret.
Sources
● Microsoft Learn (Official Documentation) - Troubleshoot Broken Connections: This document explicitly mentions the 90-day inactivity policy. It states, "For example, the SharePoint connector requires usage at least once every 90 days to remain active. If the connection isn't used within this period, it expires." This same token policy applies to other OAuth-based connections like SQL Server.
● Microsoft Learn (Official Documentation) - Refresh tokens in the Microsoft identity platform: This document details the technical policy. It confirms, "Refresh tokens have a longer lifetime than access tokens. The default lifetime for the refresh tokens is 24 hours for single page apps and 90 days for all other scenarios."
○ Source: https://learn.microsoft.com/en-us/entra/identity-platform/refresh-tokens
● Community Article - Leveraging Service Principal Authentication for SQL Connector: This article provides a step-by-step guide on how to implement the recommended solution (Solution 2), confirming that the SQL Server connector in Power Automate and Power Apps supports Service Principal authentication.
● Microsoft Learn (Official Documentation) - Create Microsoft Entra users using service principals: This official guide provides the necessary T-SQL commands (CREATE USER ... FROM EXTERNAL PROVIDER) to add your Service Principal as a user in your Azure SQL Database, which is a required step.