PowerBI Embedded: Datasource has no credentials, unable to Patch the gateway

Question

I wanted to test out PowerBI embedded so I downloaded the the sample app that is able to publish a pbix file and to embed it. So I created the easiest PowerBI file one is able to make with Azure SQL, using the DirectQuery option, as underlying data source.

I succesfully imported the PowerBI file in my workspace collection

I changed the connection string of my PowerBI file succesfully

After that the code to patch the gateway with the username and password credentials fails

Then when I tried to view the embedded report I got this error.

I believe the connectionstring is in the correct format because it was updated succesfully. I also already tried to point it to another SQL database and then the error shows the other SQL database in the error message.

1) I thought this could be because the Gateway does not get the credentials that I gave it is that correct? 2) Does someone know how can I fix this?

Thanks in advance!

Answer 1

As @Cuong Le stated, this was a Microsoft Issue at first. When the problem was fixed I still received a BadRequest exception. After trying to update the credentials with the PowerBI-CLI the problem became clearer. I needed to grant rights for Azure IP addresses to the relevant SQL database. Once I did that I was able to update the credentials. Unfortunately PowerBI API SDK's exception messages are not as good as the PowerBI-CLI messages. I also tried it with PowerBI API SDK and it also worked.

The exception message I got was the following:

[ powerbi ] {"error":{"code":"DM_GWPipeline_Gateway_DataSourceAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_DataSourceAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2146232060"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"Cannot open server 'engiep-dev-weeu-sql' requested by the login. Client with IP address 'xx.xx.xx.213' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2146232060"}},{"code":"DM_ErrorDetailNameCode_UnderlyingNativeErrorCode","detail":{"type":1,"value":"40615"}}]}}}

The correct connectionstring format to use is:

Data Source=yourDataSource;Initial Catalog=yourDataBase;User ID=yourUser;Password=yourPass;

(Don't use quotes anywhere.)

Answer 2

I was experiencing the same issue. Also it is an open issue on github.

Attached Image : enter image description here

To solve this, I used the PowerBI Cli 1.0.4 from NPM. And used Update Connection Operation,(remember to add -d).

powerbi update-connection -c [workspace name] -k [access key] -w [workspace id] -d [dataset id] -s "Data Source=xxx.database.windows.net;Initial Catalog=xxx;User ID=xxx;Password=xxx"

If it fails do it(Update-Connection Operation) again.

The issue happens since sometimes datasource credentials are not carried over to the workspace.

In the case of reports that use direct query, credentials are never brought with the pbix as an import is done. All private info are stripped out.

Hope this helps! Thanks