SQL Server
Summary
Item | Description |
---|---|
Release State | General Availability |
Products | Excel Power BI (Datasets) Power BI (Dataflows) Fabric (Dataflow Gen2) Power Apps (Dataflows) Dynamics 365 Customer Insights Analysis Services |
Authentication Types Supported | Database (Username/Password) Windows Azure Active Directory (if the specified server supports it) |
M Function Reference | Sql.Database Sql.Databases |
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 SQL Server. However, for optimal performance, we recommend that the customer installs the SQL Server Native Client before using the SQL Server 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 Datasets)
- Advanced options
- Command timeout in minutes
- Native SQL statement
- Relationship columns
- Navigate using full hierarchy
- SQL Server failover support
Connect to SQL Server database from Power Query Desktop
To make the connection, take the following steps:
Select the SQL Server database option in the connector selection.
In the SQL Server database dialog that appears, provide the name of the server and database (optional).
Select either the Import or DirectQuery data connectivity mode (Power BI Desktop only).
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.
Note
If the connection is not encrypted, you'll be prompted with the following dialog.
Select OK to connect to the database by using an unencrypted connection, or follow these instructions to setup encrypted connections to SQL Server.
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 SQL Server database from Power Query Online
To make the connection, take the following steps:
Select the SQL Server database option in the connector selection.
In the SQL Server database dialog that appears, provide the name of the server and database (optional).
If needed, select an on-premises data gateway.
If this is the first time you're connecting to this database, select the authentication kind and input your credentials.
If the connection is not encrypted, and the connection dialog contains a Use Encrypted Connection check box, clear the 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. |
SQL statement | For information, go to Import data from a database using native database query. |
Include relationship columns | 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, Navigator displays only the tables whose columns and rows contain data. |
Enable SQL Server Failover support | If checked, when a node in the SQL Server failover group isn't available, Power Query moves from that node to another when failover occurs. If cleared, no failover will occur. |
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 SQL Server database.
Known issues and limitations
Certificate errors
When using the SQL Server database connector, if encryption is disabled and the SQL Server certificate isn't trusted on the client (Power BI Desktop or on-premises data gateway), you'll experience the following error.
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.
Always Encrypted columns
Power Query doesn't support 'Always Encrypted' columns.
Azure Active Directory authentication
Azure Active Directory authentication is only supported in the SQL Server connector if the specified server also supports Azure Active Directory authentication. Otherwise, you might encounter an error that says that "The OAuth authentication method is not supported in this data source".
Note that in Power BI service, the Azure Active Directory authentication method shows up as "OAuth2".
Next steps
Feedback
Submit and view feedback for