Reports uploaded with terraform - not able to refresh azure sql data

[Arildsen]

Need some help with data connection in the Power Bi Service. Here's my scenario:

I connect to my azure sql data with my sql admin user in Power Bi desktop. I create reports and everything is good.

I use terraform to publish the report because we will do this for a lot of customers. The upload process is also fine, but when I look at the report in the power bi service after upload, I am not able to refresh the data. Terraform is the "owner" of the dataset so I cannot see the credentails being used unless I take control of the data with my AAD user. If I do I am able to refresh it, but then I'm not able to update the report later from terraform.

I've tried to create a service princial, its the same principal that creates the workspaces with the report, given it access to my sql server, but still no luck.

Any thoughts or guidance would be much appreciated.

Thanks

[Arildsen · Accepted solution]

Not sure why this is happening, but somewhere between my Power BI desktop via terraform and to the Power Bi service the credentials for my dataset "got lost" or just was not part of the upload of the pbix file. 

The solution was just to manually use the POWER BI API with Postman and using the correct tokens patch the datasource with crendentials in the body payload.

[v-tangjie-msft]

Hi @Arildsen ,

To troubleshoot this issue, there are a few potential solutions to consider:

1.Check the Azure SQL database connection settings: Verify that the connection settings for the Azure SQL database in the report are correct and up-to-date. Check the server name, database name, and authentication method to ensure that they are accurate and match the credentials being used to connect to the database.

2.Check the Power BI data source settings: Verify that the data source settings in Power BI match the connection settings for the Azure SQL database. Ensure that the credentials being used to connect to the database are valid and have sufficient permissions to access the necessary data.

3.Check the Azure SQL firewall settings: Ensure that the IP address of the machine where the report is being generated is allowed through the Azure SQL firewall. If the IP address is not allowed, the database connection will fail, and the data cannot be refreshed.

4.Check the Terraform deployment settings: Verify that the deployment settings for Terraform are correct and up-to-date. Check the configuration files to ensure that the correct connection strings and credentials are being used to connect to the Azure SQL database.

5.Check the refresh settings in Power BI: Ensure that the data refresh settings in Power BI are configured correctly. 6.Check the frequency and time zone settings to ensure that the report is refreshing at the correct intervals and times.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.