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.
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
Enable cross database folding
Connect to Azure Synapse Analytics (SQL DW) from Power Query Desktop
To make the connection from Power Query Desktop:
Select the Azure Synapse Analytics SQL option in the connector selection.
In the SQL Server database dialog that appears, provide the name of the server and database (optional). In this example, TestAzureSQLServer is the server name and AdventureWorks2012 is the database.
Select either the Import or DirectQuery data connectivity mode.
You can also select and enter advanced options that modify the connection query, such as a command timeout or a native query (SQL statement). More information: Connect using advanced options
Select OK.
If you're connecting to this database for the first time, select the authentication type, input your credentials, and select the level to apply the authentication settings to. Then select Connect.
Note
Although the Windows authentication type is displayed, Azure Synapse Analytics SQL doesn't support the Windows authentication type. Use either the Database or the Microsoft account authentication types.
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 Synapse Analytics (SQL DW) from Power Query Online
To make the connection from Power Query Online:
Select the Azure Synapse Analytics (SQL DW) option in the connector selection.
In the Azure Synapse Analytics (SQL DW) dialog that appears, provide the name of the server and database (optional). In this example, testazuresqlserver is the server name and AdventureWorks2012 is the database.
You can also select and enter advanced options that modify the connection query, such as a command timeout or a native query (SQL statement). More information: Connect using advanced options
If you're connecting to this database for the first time, select the authentication kind and input your credentials.
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.
If checked, includes columns that might have relationships to other tables. If this box is cleared, you don’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.
Enable cross database folding
This option is only available in Power Query Online.
Once you select the advanced options you require, select OK in Power Query Desktop or Next in Power Query Online to connect to Azure Synapse Analytics.
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.