Get data for Power BI report
Ensure you’re connected to the appropriate network with access to the target SQL server and open Power BI Desktop which can be downloaded from https://powerbi.microsoft.com/en-us/downloads/ or https://www.microsoft.com/en-us/download/details.aspx?id=58494.
Select Get data followed by SQL Server.
Import data from table(s)
WARNING: You must ensure the gateway is using the same hostname or IP address that has been configured as the data source in the report. Use the IP address for both the gateway and report SQL data source to be safe from name resolution errors.
Specify the SQL Server, Database and select Import as the Data Connectivity mode and click OK.
Select Windows to use Windows authentication protocols, Database to use SQL database authentication, or Microsoft account to use Microsoft’s cloud-based authentication system to authenticate to the target SQL server.
Note that you want to use a service account here and not a personal account, as this report will be using these credentials every time a SQL query is conducted within this report.
Select the database(s) and table(s) you’d like to include in the report, then select Load to load the entire table(s) or Transform Data to transform the data.
Import data from stored procedure
WARNING: You must ensure the gateway is using the same hostname or IP address that has been configured as the data source in the report. Use the IP address for both the gateway and report SQL data source to be safe from name resolution errors.
Specify the SQL Server, Database, select Import as the Data Connectivity mode, click Advanced options, inside SQL statement (optional, requires database) enter EXEC followed by the name of your stored procedure and then click OK.
Select Windows to use Windows authentication protocols, Database to use SQL database authentication, or Microsoft account to use Microsoft’s cloud-based authentication system to authenticate to the target SQL server.
Previous article: Configure Gateway
Next article: Transform Report Data