CODEWITHSUNDEEP
sql-serverazureazure-sql-databasepowerbi
Simon Bourdeau
Simon Bourdeau

Reputation: 449

PowerBI embedded with Direct Query gets white screen and 403

We have a PowerBI report that was connected to an on-premise SQL Server. This server was moved to Azure so we changed the report to now use Direct Query instead of import.

To do this I went into the existing PBIX and changed the data source settings so that the report dataset would now be a direct query one pointing to an azure SQL Server.

After that I've imported my PBIX to my Azure PowerBI Workspace using "powerbi import". I've then updated the dataset since it is a direct query and the credentials need to be updated with "powerbi update-connection"

All of these steps are successful.

I can then proceed get my reports "powerbi get-reports" and get access "powerbi create-embed-token". This also works.

The report loads in embedded setup but it stays white. There seems to be no data or no connection.

PowerBI generates a weird error that we don't generally see:

GET https://wabi-us-north-central-redirect.analysis.windows.net/powerbi/metadata/models/xxxxxxx/?modelOptions=Default&packageId=xxxxxxx 403 (Forbidden)

We have a lot of other reports running on direct query to other Azure SQL Server but none that are successful on this SQL Server that migrated to Azure.

I'm talking with Microsoft as soon as possible as well.

Update: Microsoft is looking at the problem. It seems my data source object got into a corrupt state due to a initial db property set (basically a bug). Will keep this post updated.

Update2: It seems PowerBi workspaces in Azure created before April/17 do not support connecting to more than one SQL Database. The solution would then be to create a different workspace but it seems PowerBi workspaces created via Azure are now deprecated. The solution is to migrate everything to the powerbi service(app.powerbi.com). A lot of rework in perspective.

Upvotes: 3

Views: 1188

Answers (2)

Simon Bourdeau
Simon Bourdeau

Reputation: 449

I finally fixed this problem. It took a month.

the problem was 2 fold:

  • The PowerBi Azure workspace collection that I had been using was created before April 2017 and that meant that it couldn't support connecting to multiple SQL Server instances. This is undocumented as far as I know.

  • The command PowerBi CLI that I use to call PowerBi API had been modified so that my connection string's format was now deprecated. MS Product Team gave me the proper format. They did say when this changed but it did.

myversion:

powerbi update-connection -c [Workspace Collection Name] -k [Access Key] -w [Workspace Id] -d [Dataset Id] -s "Data Source=[Server];Initial Catalog [DB];User ID=[user];Password=[pwd]"

The version that works:

powerbi update-connection -c [Workspace Collection Name] -k [Access Key] -w [Workspace Id] -d [Dataset Id] -s "Data Source=[server];Initial Catalog=[db];" -u "[user]" -p "[pwd]"

Upvotes: 2

Alberto Morillo
Alberto Morillo

Reputation: 15698

That error is related to authentication. Try to modify the authentication to prompt for credentials as a workaround.

Hope this helps.

Upvotes: 0

Related Questions