Enabling Single Sign-On (SSO) on Published Reports Created with the Dremio Software Connector

Although it is recommended that you create new reports by connecting from Power BI Desktop to Dremio by using the Dremio Cloud connector if you want to enable SSO on those reports, you can enable SSO on existing reports that you created with the Dremio Software connector. The steps differ according to whether or not Power BI Service connects to your Dremio cluster through a Power BI Gateway.

• Enabling SSO When Power BI Connects to Your Dremio Cluster Directly
• Enabling SSO When Power BI Connects to Your Dremio Cluster Through Power BI Gateway

Enabling SSO When Power BI Connects to Your Dremio Cluster Directly

Ensure that you are using Power BI Desktop February 2024.

1. Open the report in Power BI Desktop.

2. On the Home ribbon in Power BI Desktop, select Transform data > Transform data.

3. On the Home ribbon in the Power Query Editor, click Advanced Editor.

   The Advanced Editor opens. The second line that appears in the editor looks like this: Source = Dremio.Databases(“softwareservername:port”,”Enabled”,null,null,null) The values between the parentheses correspond to the values that you set when you created the connection to Dremio in Power BI Desktop before creating and publishing the report:

   • First value: IP address or hostname, as well as the port, for your Dremio instance.
   • Second value: Specifies that encryption is enabled on the connection.
   • Third value: The name of the engine to send queries on this connection to. This value is optional.
   • Fourth value: The tag to associate with queries on this connection. This value is optional.
   • Fifth value: The queue to send queries on this connection to. This value is optional.

4. In the Source line, follow these steps:

   1. Replace Dremio.Databases with DremioCloud.DatabasesByServerV370.
   2. Add the port number being used by the coordinator node, after the IP address or hostname (e.g., hostname:port-number). Dremio's default port number is 31010.
   3. Replace "Enabled" with null.

5. Click Done in the Advanced Editor.

6. Close the Power Query Editor.

7. Click Yes when the Power Query Editor asks whether you want to apply your changes now. Power BI Desktop now reruns the query.

8. Provide your Dremio authentication credentials, if you are asked for them.

9. Publish the report.

10. Log into Power BI Service.

11. Open the workspace to which you published the report.

12. Click the three dots to the right of the name of the dataset and click Settings.

13. If you see the warning "One or more cloud data sources for this dataset have been deleted.", follow these steps to make the warning disappear:

    1. Expand the Gateway connection section.
    2. Collapse the Gateway connection section.

14. Expand the Data source credentials section and click Edit credentials.

15. In the configure dialog, follow these steps:

    1. In the Authentication method field, ensure that OAuth2 is selected.
    2. In the Privacy level setting for this data source field, ensure that Private is selected.
    3. Select the check box Report viewers can only access this data source with their own Power BI identities using DirectQuery.
    4. Click Sign in.

Enabling SSO When Power BI Connects to Your Dremio Cluster Through Power BI Gateway

To enable SSO, you switch the connection type that a report uses from a Dremio Software connection type to a Dremio Cloud one.

Ensure that you are using Power BI Desktop February 2024.

1. Open the report in Power BI Desktop.

2. On the Home ribbon in Power BI Desktop, select Transform data > Transform data.

3. On the Home ribbon in the Power Query Editor, click Advanced Editor.

   The Advanced Editor opens. The second line that appears in the editor looks like this: Source = Dremio.Databases(“softwareservername:port”,”Enabled”,null,null,null) The values between the parentheses correspond to the values that you set when you created the connection to Dremio in Power BI Desktop before creating and publishing the report:

   • First value: IP address or hostname, as well as the port, for your Dremio instance.
   • Second value: Specifies that encryption is enabled on the connection.
   • Third value: The name of the engine to send queries on this connection to. This value is optional.
   • Fourth value: The tag to associate with queries on this connection. This value is optional.
   • Fifth value: The queue to send queries on this connection to. This value is optional.

4. In the Source line, follow these steps:

   1. Replace Dremio.Databases with DremioCloud.DatabasesByServerV370.
   2. Add the port number being used by the coordinator node, after the IP address or hostname (e.g., hostname:port-number). Dremio's default port number is 31010.
   3. Replace "Enabled" with null. For Dremio Cloud, this parameter has a different meaning than it does for Dremio Software, and it is not necessary to specify a value.

5. Click Done in the Advanced Editor.

6. In the Power Query Editor, click Edit credentials at the end of the message Please specify how to connect.

7. Provide your credentials for logging into Dremio.

8. Close the Power Query Editor.

9. Click Yes when the Power Query Editor asks whether you want to apply your changes now. Power BI Desktop now reruns the query.

10. Provide your authentication credentials for logging into Dremio, if you are asked for them.

11. Click Connect.

12. Publish the report. If the Replace this dataset? message appears, click Replace.

13. Log into Power BI Service.

14. Open the workspace to which you published the report.

15. Click the three dots to the right of the name of the dataset and click Settings.

16. Expand the Gateway connection section.

17. Look in the Maps to field.

    • If the field displays the name of the dataset by default, you're done.
    • If the field does not display the name of the dataset by default, select the name; now, you're done.
    • If the name of the dataset does not appear, select Manually add to gateway. Then, proceed to the next steps.

18. In the New data source dialog, create a data source that matches the one that you previously used for your dataset. However, give the new data source a different name.

19. In the Data Source Type field, ensure that Dremio Cloud is specified.

20. In the Server field, specify the hostname or ID address of your Dremio cluster.

21. Ignore the Project, Engine, Tag, and Queue fields.

22. In the Authentication method field, select OAuth2. Then, follow these steps:

    1. Click Edit credentials and provide your Microsoft credentials for logging into Dremio.
    2. Select the checkbox Use SSO via Azure AD for DirectQuery queries.
    3. Click Create.

23. Return to the settings for your dataset.

24. Under Gateway connection, verify that the new data source is selected in the Maps to field.