Azure SQL Database with DirectQuery
Learn how you can connect directly to Azure SQL Database and create reports that use live data. You can keep your data at the source and not in Power BI.
With DirectQuery, queries are sent back to your Azure SQL Database as you explore the data in the report view. This experience is suggested for users who are familiar with the databases and entities they connect to.
This description assumes that Azure SQL database is not behind a VNET or has private link endpoint enabled.
- Specify the fully qualified server name when connecting (see below for more details).
- Ensure firewall rules for the database are configured to"Allow access to Azure services.
- Every action such as selecting a column or adding a filter will send a query back to the database.
- Tiles are refreshed every hour (refresh doesn't need to be scheduled). You can adjust how often to refresh in the Advanced settings when you connect.
- Schema changes aren't picked up automatically.
- Changing the data source connection string alias from
xxxx.domain.comindicates to the Power BI service that it's an on-premises datasource and always requires a gateway connection to be established.
These restrictions and notes may change as we continue to improve the experiences. The steps to connect are detailed below.
We have been improving our connectivity to Azure SQL Database. For the best experience to connect to your Azure SQL Database data source, use Power BI Desktop. Once you've built your model and report, you can publish it to the Power BI service. The direct connector for Azure SQL Database in the Power BI service is now deprecated.
Power BI Desktop and DirectQuery
To connect to Azure SQL Database using DirectQuery, you must use Power BI Desktop. This approach provides more flexibility and capabilities. Reports created using Power BI Desktop can then be published to the Power BI service. To learn more about how to connect to Azure SQL Database in Power BI Desktop, see Use DirectQuery in Power BI Desktop.
Find parameter values
You can find your fully qualified server name and database name in the Azure portal.
After you publish an Azure SQL DirectQuery dataset to the service, you can enable single sign-on (SSO) using Azure Active Directory (Azure AD) OAuth2 for your end users.
To enable SSO, go to settings for the dataset, open the Data Sources tab, and check the SSO box.
When the SSO option is enabled and your users access reports built atop the data source, Power BI sends their authenticated Azure AD credentials in the queries to the Azure SQL database or data warehouse. This option enables Power BI to respect the security settings that are configured at the data source level.
The SSO option takes affect across all datasets that use this data source. It does not affect the authentication method used for import scenarios.
For SSO to work properly, the dataset must be on the same tenant as the Azure SQL resource.
More questions? Try the Power BI community