Skip to main content
Version: 24.3.x

Troubleshooting Connections to Dremio from Microsoft Power BI

Power BI Desktop update (June 2022)​

Problem​

The June 2022 release for Microsoft Power BI contains an encryption parameter. This includes a change to the Dremio Software connector to set encryption manually in the parameter screen. Because the May 2022 version of Power BI Gateway does not contain Dremio, this will lead to incompatibility errors if Power BI Desktop is upgraded to June 2022 for Dremio Software only.

Solution​

You must upgrade to the June 2022 release for Power BI Gateway if you have the Power BI Desktop (June 2022) installed and you use the Dremio Software connector.

I want to…Use desktop versionUse gateway version
Run (Dremio Software) reports in Power BI Service via GatewayJune 2022June 2022 (should be the same version as Desktop)
Run (Dremio Software) reports in Power BI Service connecting directly to DremioJune 2022Not applicable

Power BI Service update (June 2022)​

Problem​

Microsoft Power BI Service (June 2022 - Client version: 2206.1.11664-train) now contains the ability to directly connect to Dremio Software without a gateway. See the following actions required to connect new or already published reports.

Solution​

I have a report...To turn off a gateway
Created using Power BI Desktop (June 2022 or later) and published to Power BI Service1. In Power BI Service, open the Data hub tab. Click the vertical ellipsis next to the dataset and select Settings.
2. Expand the Data source credentials section and click Edit credentials.
3. Select an authentication method.
4. Set the Privacy level setting for this data source.
5. Click Sign in.
Published using Power BI Desktop (June 2022) to Power BI Service and a gateway was used to display the report1. In Power BI Service, open the Data hub tab. Click the vertical ellipsis next to the dataset and select Settings.
2. Expand the Gateway connection section and toggle On-premises or VNet data gateway off.
3. Expand the Data source credentials section and click Edit credentials.
4. Select an authentication method.
5. Set the Privacy level setting for this data source.
6. Click Sign in.
Published using Power BI Desktop (May 2022 or earlier) to Power BI Service and a gateway was used to display the report1. In Power BI Desktop, open the original report and republish the report to Power BI Service (now using new connector).
OR
1. In Power BI Desktop, open the original report and navigate to Transform data > Advanced Editor.
2. In the Advanced Editor dialog, update source to: Dremio.DatabasesV300(“[your server]”, “Enabled”, null, null, null)
3. Click Done to update and republish the report.

If the connection is successful, the report will run.

SSL verification failed​

Problem​

After authenticating the Dremio connector, I'm receiving the following error:

(1110) SSL verification failed because the server host name specified for the connection does not match the "CN" entry in the "Subject" field or any of the "DNS Name" entries of the "Subject Alternative Name" field in the server certificate.

Solution​

The problem occurs due to a setup that is using encrypted connections for the client port. This occurrence depends on the architecture for your Dremio Software instance.

Check that the certificate has the external hostname set as the Subject.CN entry using the OpenSSL tool. For instance, if the external hostname is my.customers.dremio and the client port is configured to 9050 (in dremio.conf services.coordinator.client-endpoint.port 9050) when you run this command:

Check certificate's external hostname 
openssl s_client -connect my.customers.dremio:9050

you should see that the output shows the level 0 CN field is set to the hostname that you issued in the command:

Output for certificate's external hostname 
CONNECTED(00000006)
some output you can ignore for now
---
Certificate chain
 0 s:CN = my.customers.dremio

more output you can ignore for now

If you see a different hostname in the level 0 CN field, this could be the cause of the error. Recreate the certificate to see if this resolves the error.

It's also worth checking if the certificate is installed directly on the Dremio Software instance, because the dremio.conf should have:

services.coordinator.client-endpoint.ssl.enabled true

If, however, a load balancer or proxy is performing SSL offloading, then the connection arriving at Dremio Software will be unencrypted. In this case, dremio.conf should have:

services.coordinator.client-endpoint.ssl.enabled false

Mismatch of parameters between Power BI Desktop and Power BI Gateway​

Problem​

As new versions of the Dremio Power BI connectors are released, this will change some of the parameters you can enter. When this occurs, there could be errors reported in Power BI Service such as:

(1110) Unable to determine the data source.

(1110) Gateway or datasources not reachable.

Typically this will occur if you have generated a report (pbix) with a newer connector and you tried to open it with an older connector.

Solution​

It is important that both Power BI Desktop and Power BI Gateway use the same connector Dremio.mez file version. You can ensure this by installing Power BI Desktop and Power BI Gateway, which have the same installation date (example: September 2021).

The Power BI connector is contained in a Dremio.mez file, which is not the same as the desktop and gateway versions.

note

It is recommended to use the latest connector version as this offers the best backward compatibility.

Power BI Desktop Installation​

The Power BI Desktop installation should not have the connector Dremio.mez file installed locally. Instead, it should use the Certified Connector that comes with Power BI Desktop.

To ensure there is no local version on your Windows computer:

  1. Open a File Explorer and navigate to C:\Users\<your user>\Documents\Power BI Desktop\Custom Connectors.
  2. If this folder exists and the file Dremio.mez exists in that folder, then delete the file.
  3. If Power BI Desktop is open, then close it. When you reopen Desktop, it will find the correct Dremio connector.

Power BI Gateway Installation​

See the Power BI Gateway installation instructions. Power BI Gateway should not have the connector Dremo.mez installed locally. Instead, it should use the Certified Connector that comes with Power BI Gateway.

To ensure there is no local version on your Windows computer:

  1. Open Power BI Gateway and then select Connectors from the left menu.
  2. Use a File Explorer and navigate to C:\Users<your user>\Documents\Power BI Desktop\Custom Connectors.
  3. If this folder exists and the file Dremio.mez exists in that folder, then delete the file.
  4. Restart Power BI Gateway.

Uncertified connector message​

Problem​

When the Power BI Desktop is opened and the Dremio connector is selected in Get Data, the following pop-up message appears:

This error occurs because there is a Power BI connector installed locally.

note

We are moving away from having connectors installed locally. Instead, you should use the certified connectors that are available as part of the desktop or gateway installations.

Solution​

Check to see if you have a Dremio.mez file in the C:\Users\{Your Username}\Documents\Power BI Desktop\Custom Connectors folder. If so, you should remove it.

Power BI SSO AAD fails with error AADSTS650052​

Problem​

This problem applies to Dremio Software v20 or later, because Single Sign On (SSO) authentication is already working for UI logins.

After configuring SSO to add the tenant info in the Dremio UI, you complete the following steps to connect to Power BI:

  1. Select the Dremio Software connector and enter the Dremio Server name and click OK.
  2. Select Microsoft Account and click Sign in.
  3. On the pop-up message, specify my AAD user and password credentials, and click Sign In.
  4. (Optional) If there is another pop-up message stating Microsoft has enabled Security Defaults to keep your account secure, click Skip for now....

Then in the Power BI login, you are receiving the following error:

access_denied: AADSTS650052: The app needs access to a service ('https://connectors.dremio.app/powerbi') that your <organization ID> has not subscribed to or enabled. Contact your IT Admin to review the configuration of your service subscriptions. <Trace ID> <Correlation ID>

Solution​

First, you need to confirm the correct tenant ID is being used in the Dremio UI. You can find your Azure Active Directory Tenant ID by signing into the Azure portal and navigating to Dremio > Overview > Basic information.

After you have found your tenant ID, paste the following into a browser:

URL to paste into browser 
https://login.microsoftonline.com/<tenant_ID>/v2.0/adminconsent?client_id=429333a8-1521-4502-9101-6d4f2c1de644&&scope=User.Read&redirect_uri=http://localhost/myapp/permissions

You will need to change the tenant ID to the one you obtained before hitting Enter in the browser. The value entered in the Dremio UI should also match this value.

Next, sign in with your Dremio account. You will then be directed to a screen that asks an admin to sign in. It will look similar to this message:

You will see an error at the end about not finding a redirect page, but this is okay. The command above it does not have this set to a valid value.

Then, try connecting with Power BI again. You might be asked once more to grant permissions. An error in the message causes it to state that permissions are required for Microsoft Power Query for Excel. Instead, it is Dremio that the permissions are required for.

What to do if the message appears depends on whether you are an AAD admin and which setting under User consent for applications is selected in AAD:

  • If you are an AAD administrator and Do not allow user consent is selected in AAD, clicking Accept accepts for all of your Power BI users who subsequently connect to Dremio. Those users will not receive a message asking them to grant Dremio required permissions.
  • If you are an AAD administrator and Allow user consent for apps is selected in AAD, clicking Accept accepts only for yourself. All of your Power BI users who connect to Dremio will also be asked to grant Dremio permissions.
  • If you are not an AAD administrator and Do not allow user consent is selected in AAD, you are not able to accept. Instead, you must contact your Power BI administrator.
  • If you are not an AAD administrator and Allow user consent for apps is selected in AAD, clicking Accept accepts only for yourself.

"Request sent" for AAD authentication​

Problem​

When you attempt to log in via AAD, you may get redirected to a page that instructs you to request access from an admin. When you provide a reason for the request and send the request, the admin of your organization never receives any such request.

Solution​

This is caused by a misconfiguration during setup. Walk through PowerBI SSO AAD fails with error AADSTS650052 and Setting Up Azure AD and ensure that every step was followed. Ensure the tenant ID is of your tenant, the application is set up in that tenant, and the following permission is enabled, as shown below:

Unable to connect or Expression.Error​

Problem​

If you're opening Power BI from a PBIDS file generated from Dremio or if you're connecting to Dremio from PBI Desktop, such as when opening a parquet file in PBI Desktop, you may see this error:

This report format is no longer supported. Please recreate the report and set Encryption to Disabled.

Solution​

PBIDS fix​

The PBIDS file may be edited with a text editor to add the encryption parameter, as shown here:

PBIDS file 
{
  "connections" : [ {
    "options" : { },
    "mode" : "DirectQuery",
    "details" : {
      "address" : {
        "server" : <your_server>,
        "schema" : "Samples.samples.dremio.com.tpcds_sf1000.customer",
        "object" : "1_0_0.parquet",
        "encryption" : "Disabled"
      },
      "protocol" : "dremio"
    }
  } ],
  "version" : "0.1"
}

Non-PBIDS fix​

Use this solution if you are trying to create a report or connect directly from Power BI Desktop. In some circumstances, the error reported is incorrect, so this solution will allow you to find the correct error.

Install Power BI Desktop (May 2022) and recreate the report or connection using the May 2022 version. This is because May 2022 has better error reporting than the June 2022 version. Once the error is determined and fixed, then the June 2022 version can be installed again.

The hostname cannot be resolved​

Problem​

Attempting to connect to Dremio from Power BI Service by using Direct Query results in this error message from Power BI Service, where <hostname> is the hostname or IP address of the Dremio cluster: Failed to update data source credentials: ODBC: ERROR: [08S01] [Microsoft][Drill] (70) The hostname of '<hostname>' cannot be resolved. Please check your DNS setup or connect directly to Drillbit.

Solution​

  1. Ensure that the specified hostname or IP address is correct.
  2. Ensure that the prerequisites listed in Enabling Users to Connect to Dremio by Using Direct Query have been met.