How to add SQL Data Source to your report in PowerBI Sevice using Gateways?

To obtain analytics and reports that offer real-time insights, we need to add a data source to our report. In this blog, we will explore detailed steps for the same.

1. Locate your report and semantic model in the workspace on Power BI Service.

2. Click on the three dots by hovering over the report’s semantic model. A dashboard will open under the semantic model tab.

3. In that dashboard, scroll down a bit to locate the "Gateway and Cloud Connections" dropdown option.

4. Open the dropdown menu to see a list of all the on-premises gateways under the service ID.

5. Click the arrow near the settings icon on the gateway you want to use. Ensure that the gateway is present on the machine, meaning the gateway must be created on the same machine.

6. Now click on the "Add to Gateway" link on the SQL Server connection string under "Data sources included in this semantic model."

7. A new connection menu will appear on the right-hand side of the screen.

8. Provide the connection name you want to give.

9. Select an authentication method to use when connecting to the data source: Basic, Windows, or OAuth2. For SQL Server, choose Windows or Basic (SQL Authentication). Enter the credentials for your data source.

10. Depending on your organization's settings, for DirectQuery-based reports, you can configure "Use SSO via Kerberos for DirectQuery queries," "Use SSO via Kerberos for DirectQuery and Import queries," or "Use SSO via Azure AD for DirectQuery queries." Configure "Use SSO via Kerberos for DirectQuery and Import queries" for refresh-based reports.

11. Now click on the "Create" button or select the above option for direct querying; a connection will be created and will direct you to the previous page.

12. In that page, select "Maps to:" option as we selected "Add to Gateway:" option in step 6. Choose the connection name created earlier and then click on the "Apply" button.

13. Congratulations! We have successfully added a SQL Data Source to our report in Power BI Service using Gateways. Now you can refresh anytime and also set a schedule refresh for this report to get updated data.