PowerBI fails to connect to Azure Synapse works with SQL login

MrFlinstone 501 Reputation points
2024-06-20T22:24:05.6066667+00:00

0

I am connecting to a synapse SQL database from powerBI, this is using a data gateway. If I use SQL authentication, it works fine however if i change it to OAuth authentication, I get the error below.

Unable to create connection for the following reason: Unable to connect to the data source. Either the data source is inaccessible, a connection timeout occurred, or the data source credentials are invalid. Please verify the data source configuration and contact a data source administrator to troubleshoot this issue.

Details:

Microsoft SQL: Reason: An instance-specific error occurred while establishing a connection to SQL Server. Connection was denied since Deny Public Network Access is set to Yes (https://docs.microsoft.com/azure/azure-sql/database/connectivity-settings#deny-public-network-access). To connect to this server, use the Private Endpoint from inside your virtual network (https://docs.microsoft.com/azure/sql-database/sql-database-private-endpoint-overview#how-to-set-up-private-link-for-azure-sql-database).

Please have this information handy if you choose to create a support ticket.
Cluster URI: https://api.powerbi.com
Status code: 400

Whilst there is a private endpoint, its an issue related to using oAuth authentication or service principal. I wonder if any specified Azure AD settings need to be in place.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,621 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. hossein jalilian 4,695 Reputation points
    2024-06-20T23:03:13.9833333+00:00

    Thanks for posting your question in the Microsoft Q&A forum.

    When using OAuth authentication for Azure Synapse SQL, you need to ensure that the Azure AD settings are correctly configured:

    • Make sure that the service principal or managed identity used by Power BI to connect to Azure Synapse SQL has appropriate permissions
    • Ensure that the Azure AD admin has granted necessary permissions to the application or service principal to access the Azure Synapse SQL database
    • For OAuth authentication, you typically need to connect via a private endpoint or ensure that the OAuth token allows access

    Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful