Edit

SQL Server

  • Article

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:

  1. Select the SQL Server database option in the connector selection.

  2. In the SQL Server database dialog that appears, provide the name of the server and database (optional).

    SQL Server database connection builder in Power Query Desktop.

  3. Select either the Import or DirectQuery data connectivity mode (Power BI Desktop only).

  4. Select OK.

  5. 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.

    SQL Server database authentication.

    Note

    If the connection is not encrypted, you'll be prompted with the following dialog.

    SQL Server database encryption support.

    Select OK to connect to the database by using an unencrypted connection, or follow these instructions to setup encrypted connections to SQL Server.

  6. 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.

    Power Query Navigator showing the employee data.

Connect to SQL Server database from Power Query Online

To make the connection, take the following steps:

  1. Select the SQL Server database option in the connector selection.

  2. In the SQL Server database dialog that appears, provide the name of the server and database (optional).

    SQL Server database connection builder in Power Query Online.

  3. If needed, select an on-premises data gateway.

  4. If this is the first time you're connecting to this database, select the authentication kind and input your credentials.

  5. If the connection is not encrypted, and the connection dialog contains a Use Encrypted Connection check box, clear the check box.

  6. Select Next to continue.

  7. In Navigator, select the data you require, and then select Transform data.

    Power Query Online Navigator showing the Human Resources employee 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

Optimize Power Query when expanding table columns