Views:

Applies to Product - Power Apps

What’s happening?
Users are unable to connect to SQL Server using service principal authentication in Power Apps. Specifically, the interface does not display fields for entering the SQL Server name and database name when attempting to establish a connection using a service principal. These fields are available when using SQL authentication.
 

Reason:
This is typically caused by one or more of the following:

  • The Power Apps connection interface does not expose SQL Server and database name fields when using service principal authentication.
  • The SQL Managed Instance may not be accessible due to network security configurations.
  • The service principal may lack the necessary permissions or roles in SQL Server.
  • The on-premises data gateway may not be configured to support OAuth or may be outdated.
  • Azure AD authentication settings may not be correctly configured in the SQL Server or Power Apps connection.

 

Resolution:
To resolve this, follow these steps:

  1. Verify Network Access
    • Ensure the SQL Managed Instance is accessible from the on-premises data gateway.
    • Check Network Security Group (NSG) rules and Private Endpoint configurations to confirm connectivity.
  2. Check Service Principal Permissions
    • Confirm that the Azure Entra ID app registration has the necessary API permissions for Azure SQL Database.
    • Assign appropriate SQL roles (e.g., db_datareader, db_datawriter, or db_owner) to the service principal in the target database.
  3. Configure the On-Premises Data Gateway
    • Ensure the gateway is configured to support OAuth authentication for Power Apps.
    • Update the gateway to the latest version to ensure compatibility.
  4. Validate Azure AD Authentication Settings
    • Confirm that Azure AD authentication is enabled and correctly configured in the SQL Server.
    • Use SQL Server Management Studio (SSMS) or Azure Data Studio to test connectivity using an Azure AD account.
  5. Review Logs
    • Check the on-premises data gateway logs for authentication or connectivity errors.
  6. Fallback Testing
    • Attempt to connect using SQL authentication to isolate whether the issue is specific to service principal authentication.
  7. Advanced Troubleshooting
    • If the issue persists, collect detailed error messages and logs for further analysis.