Excel Power BI (Semantic models) Power BI (Dataflows) Fabric (Dataflow Gen2) Power Apps (Dataflows) Excel Dynamics 365 Customer Insights Analysis Services
Authentication types supported
Windows (Power BI Desktop, Excel, Power Query Online with gateway) Database (Power BI Desktop, Excel) Microsoft Account (all) Basic (Power Query Online) Service Principal
The service principal authentication type isn't supported when using an on-premises data gateway or a virtual network (VNet) data gateway.
Note
Some capabilities may be present in one product but not others due to deployment schedules and host-specific capabilities.
Prerequisites
By default, Power BI installs an OLE DB driver for Azure SQL database. However, for optimal performance, we recommend that the customer installs the SQL Server Native Client before using the Azure SQL database connector. SQL Server Native Client 11.0 and SQL Server Native Client 10.0 are both supported in the latest version.
Capabilities supported
Import
DirectQuery (Power BI semantic models)
Advanced options
Command timeout in minutes
Native SQL statement
Relationship columns
Navigate using full hierarchy
SQL Server failover support
Connect to Azure SQL database from Power Query Desktop
To connect to an Azure SQL database from Power Query Desktop, take the following steps:
Select Azure SQL database in the get data experience. The get data experience in Power Query Desktop varies between apps. For more information about the Power Query Desktop get data experience for your app, go to Where to get data.
In SQL Server database, provide the name of the server and database (optional).
Select either the Import or DirectQuery data connectivity mode.
Optionally, you can select and enter advanced options that will modify the connection query, such as a command timeout or a native query (SQL statement). For information: Connect using advance options
Select OK.
If this is the first time you're connecting to this database, select the authentication type, input your credentials, and select the level to apply the authentication settings to. Then select Connect.
If the connection is not encrypted, you'll be prompted with the following message.
Select OK to connect to the database by using an unencrypted connection, or follow the instructions in Enable encrypted connections to the Database Engine to set up encrypted connections to Azure SQL database.
In Navigator, select the database information you want, then either select Load to load the data or Transform Data to continue transforming the data in Power Query Editor.
Connect to Azure SQL database from Power Query Online
To connect to an Azure SQL database from Power Query Online, take the following steps:
Select the Azure SQL database option in the get data experience. Different apps have different ways of getting to the Power Query Online get data experience. For more information about how to get to the Power Query Online get data experience from your app, go to Where to get data.
In Azure SQL database, provide the name of the server and database.
You can also select and enter advanced options that will modify the connection query, such as a command timeout or a native query (SQL statement). More information: Connect using advanced options
If this is the first time you're connecting to this database, select the authentication kind and input your credentials.
If necessary, select the name of your on-premises data gateway.
If the connection is not encrypted, clear the Use Encrypted Connection check box.
Select Next to continue.
In Navigator, select the data you require, and then select Transform data.
Connect using advanced options
Both Power Query Desktop and Power Query Online provide a set of advanced options that you can add to your query if needed.
The following table lists all of the advanced options you can set in Power Query Desktop and Power Query Online.
Advanced option
Description
Command timeout in minutes
If your connection lasts longer than 10 minutes (the default timeout), you can enter another value in minutes to keep the connection open longer. This option is only available in Power Query Desktop.
If checked, includes columns that might have relationships to other tables. If this box is cleared, you won’t see those columns.
Navigate using full hierarchy
If checked, the navigator displays the complete hierarchy of tables in the database you're connecting to. If cleared, the navigator displays only the tables whose columns and rows contain data.
Enable SQL Server Failover support
If checked, when a node in the Azure SQL failover group isn't available, Power Query moves from that node to another when failover occurs. If cleared, no failover occurs.
Once you've selected the advanced options you require, select OK in Power Query Desktop or Next in Power Query Online to connect to your Azure SQL database.
Troubleshooting
Always Encrypted columns
Power Query doesn't support 'Always Encrypted' columns.
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.