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.
- 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 does not need to be scheduled). This can be adjusted in the Advanced settings when you connect.
- Q&A is not available for DirectQuery datasets
- Schema changes afre not picked up automatically
These restrictions and notes may change as we continue to improve the experiences. The steps to connect are detailed below.
Power BI Desktop and DirectQuery
In order to connect to Azure SQL Database using DirectQuery, you will need to use Power BI Desktop. This approach provides additional flexability and capabilities. Reports created using Power BI Desktop can then be published to the Power BI service. You can learn more about how to connect to Azure SQL Database using DirectQuery within Power BI Desktop.
After you publish an Azure SQL DirectQuery dataset to the service, you can enable single sign-on (SSO) via 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. This 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.
Finding Parameter Values
Your fully qualified server name and database name can be found in the Azure Portal.