Edit

Share via

Set up your SQL Server database connection

This article outlines the steps to create a SQL Server database connection.

Supported authentication types

The SQL Server database connector supports the following authentication types for copy and Dataflow Gen2 respectively.

Authentication type Copy Dataflow Gen2
Basic (Username/Password) √ √
Organizational account √ √
Service principal √ (Only for SQL Server on Azure VMs) √
Windows √ (When use on-premises data gateway) √
Workspace identity n/a √

Set up your connection for Dataflow Gen2

You can connect Dataflow Gen2 in Microsoft Fabric to SQL Server database using Power Query connectors. Follow these steps to create your connection:

  1. Check capabilities, limitations, and considerations to make sure your scenario is supported.
  2. Get data in Fabric.
  3. Connect to SQL Server database.

Capabilities

  • 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

Get data

To get data in Data Factory:

  1. On the left side of Data Factory, select Workspaces.

  2. From your Data Factory workspace, select New > Dataflow Gen2 to create a new dataflow.

    Screenshot showing the workspace where you choose to create a new dataflow.

  3. In Power Query, either select Get data in the ribbon or select Get data from another source in the current view.

    Screenshot showing the Power Query workspace with the Get data option emphasized.

  4. In the Choose data source page, use Search to search for the name of the connector, or select View more on the right hand side the connector to see a list of all the connectors available in Power BI service.

    Screenshot of the Data Factory Choose data source page with the search box and the view more selection emphasized.

  5. If you choose to view more connectors, you can still use Search to search for the name of the connector, or choose a category to see a list of connectors associated with that category.

    Screenshot of the Data Factory Choose data source page displayed after selecting view more, with the list of connectors.

Connect to SQL Server database

To make the connection, take the following steps:

  1. Select the SQL Server database option in the connector selection. For more information, go to Where to get data.

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

    Screenshot of the SQL Server database connection builder in Power Query Online.

  3. If the SQL server isn't online, select an on-premises data gateway. Additionally, if using the port with servername, use the value servername* as the server name in the connection settings.

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

  5. If the connection isn't 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.

    Screenshot of the Power Query Online Navigator showing the Human Resources employee data.

Limitations and considerations

SQL Server certificate isn't trusted on the client (Power BI Desktop or on-premises data gateway)

When establishing a connection to an on-premises SQL Server using the on-premises data gateway or Power BI Desktop and the SQL Server utilizes a self-signed certificate, it's possible that the refresh operation for a Fabric semantic model or dataflow can fail with the following error message:

Microsoft SQL: 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.)

To troubleshoot this error when using on-premises data gateway, change the gateway configurations to update the SqlTrustedServers setting using the following steps:

  1. On the local machine where the on-premises data gateway is installed, navigate to C:\Program Files\On-premises data gateway.

  2. Make a backup of the configuration file named Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config.

  3. Open the original Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config configuration file and locate the SqlTrustedServers entry.

  4. Update the SqlTrustedServers value with the names of the SQL servers to trust and connect to.

    The value contains a comma-delimited list of server names and supports * as a wild card. So for instance in the following example:

    <setting name="SqlTrustedServers" serializeAs="String"> <value>contososql*,mysvr</value> </setting>

    the value contososql*,mysvr matches contososql6, contososqlazure, and mysvr, but doesn't match mysvr.microsoft.com.

To troubleshoot this error when using Power BI Desktop, modify the value of the environment variable PBI_SQL_TRUSTED_SERVERS to include the SQL Servers. The supported values are the same as outlined for gateway configuration (as described in step 4 above).

For connections to SQL Server from Power BI Desktop and on-premises data gateway versions starting with February 2025 or later, follow one of these options:

  • Follow the solution mentioned previously to add the environment variable PBI_SQL_TRUSTED_SERVERS.
  • Ask your SQL administrators to acquire a certificate from a well-known certificate authority.
  • Change the setting SqlTrustedServers on the gateway config file.

Always Encrypted columns

Power Query doesn't support 'Always Encrypted' columns.

Microsoft Entra ID authentication

Microsoft Entra ID (Organizational account) authentication is only supported in the SQL Server connector if the specified server also supports Microsoft Entra ID authentication. Otherwise, you might encounter a The OAuth authentication method isn't supported in this data source error.

In Power BI service, the Microsoft Entra ID authentication method shows up as OAuth2.

Related content

  • Last updated on