BigQuery disconnecting

[marconiqavi]

I am logging in with my credentials in OAuth2 mode and Organizational level, but every 24 or 48 hours the credentials expire and all flows stop working.

Has anyone experienced this and know how to solve it?

[Shravan133 · Accepted solution]

When using OAuth2 authentication in Power BI, it's common for tokens to expire after a certain period, usually 24 or 48 hours. This can disrupt automatic refreshes and other scheduled tasks. Here are some strategies to handle this issue:

Strategies to Handle Expiring OAuth2 Tokens

1. Use a Service Principal:

   • A service principal (an Azure AD app registration) can provide a more stable authentication method for Power BI data sources. Service principals do not expire like user tokens and can help avoid disruptions.

2. Use Power Automate to Refresh Token:

   • Automate the process of refreshing the OAuth2 token using Power Automate (formerly Microsoft Flow). This can help ensure that your credentials are always up-to-date.

3. Dedicated Service Account:

   • Create a dedicated service account with the necessary permissions and use it to set up your OAuth2 credentials. Ensure this account has the least privilege necessary and is not subject to frequent password changes.

Implementing a Service Principal

Step-by-Step Guide:

1. Register an Application in Azure AD:

   • Go to the Azure portal.
   • Navigate to Azure Active Directory > App registrations > New registration.
   • Register your application and note down the Application (client) ID and Directory (tenant) ID.

2. Grant API Permissions:

   • Navigate to the registered application.
   • Go to API permissions > Add a permission.
   • Select the necessary API permissions (e.g., Power BI API, Office 365 Management APIs) and grant admin consent.

3. Create a Client Secret:

   • Navigate to Certificates & secrets.
   • Create a new client secret and note it down.

4. Assign Power BI Service Principal Admin Role:

   • Go to Power BI Admin Portal > Tenant settings.
   • Enable the setting to allow service principals to use Power BI APIs.

5. Configure Data Source in Power BI:

   • In Power BI Desktop, when setting up data source credentials, use the service principal (client ID and client secret) for authentication.

Example: Using a Service Principal in Power BI

1. Configure the Power BI Data Source:

   • Open Power BI Desktop.
   • Get data from the desired source and choose OAuth2 authentication initially.
   • Once you publish the report, change the authentication method in the Power BI service to use the client ID and client secret.

2. Set Up Automatic Refresh in Power BI Service:

   • Go to the Power BI service and navigate to your dataset settings.
   • Under Data source credentials, select OAuth2 and provide the client ID, client secret, and tenant ID.
   • Set up the scheduled refresh as required.

Using Power Automate to Refresh Token

If a service principal is not an option, you can use Power Automate to automate token refresh:

1. Create a Flow in Power Automate:

   • Go to Power Automate.
   • Create a new flow that triggers before your scheduled refresh (e.g., every 23 hours).

2. Add Actions to Refresh Token:

   • Use HTTP actions to call the OAuth2 token endpoint with your credentials.
   • Parse the response to extract the new token.
   • Update the credentials in your data source using Power BI API calls.

Summary

Using a service principal is the most reliable way to handle OAuth2 authentication in Power BI, ensuring that your credentials do not expire and disrupt your workflows. If a service principal is not feasible, automating the token refresh process using Power Automate can help maintain the validity of your OAuth2 tokens. These strategies will help ensure that your Power BI flows and automatic refreshes continue to work smoothly without frequent interruptions