Connection String for Embedded report which uses DirectQuery

[gjross]

I have a PBIX which uses a Direct Query to a SQL Azure database. I have followed the steps in https://azure.microsoft.com/en-gb/documentation/articles/power-bi-embedded-get-started-sample to create a workspace etc and get to the point where it states "...run option 7 to update the connection strings". The downfall seems to be the connection string itself; I have tried 'xxxx.database.windows.net: databasename' or 'Server=xxxx.database.windows.net; Database=databasename'.

What is the correct format for the connection string?

[sundar]

While designing the report use Direct query method instead of Import in Power BI desktop.

And then import the Pbix file using Provisionsample command line .

Finally update the dataset using the following connection string format:

"Data Source=xxxx.database.windows.net;Initial Catalog=dbname;User ID=xxx;Password=xxxx;"

It worked well for me. Hope it will help.

Thank you.

[gpanagakis]

A very useful article is How to use Power BI Embedded with REST under the heading "data source connectivity" I found

very useful info how to set connection strings and set credential to the datasource. I could not make it using the provisioning sample option 7 and following the articles advise I used a rest client like PostMan  and send the requests,

Hope I helped!

[gpanagakis]

This tutorial  How to use Power BI Embedded with REST helped me set the credential for my datasource. I was not able to set them using the option 7 of the provision example, I used a Rest client like PostMan  and followed the article  specifically the  part in  "Data Source Connectivity (and multi-tenacy of data)". 

You can see the format of the connection string in the article.

[Infero]

Hey, I also am having a problem here, just wondering, did you find the right format ? or a workaround ?

[gjross]

I built a new workspace and ran the project again, this time, when running Option 7, I only updated the username and password. I hit enter to skip updating the connection string - and it worked.

[Brian_M · Accepted solution]

Yes that will work as it retains the connection string from the .pbix file you upload.  If you want to programmatically change the connection string (e.g. to switch between DEV/TEST/LIVE) then I believe this should help?

https://msdn.microsoft.com/en-us/library/azure/mt711505.aspx

[Oussama]

I have the same problem with a directe connection to ssas cube. My report works fine when uploaded to pbi services. Which parameter I should introduce on step7 ? Is their any save credentials option on the pbix file that I have to do?

[KrisTea]

Hi, can someone please post a sample connection string to connect to an AS model?  My PBIX connects to an AS model rather than SQL Server database.  Thank you in advance!

[KrisTea]

What format is step 7 of the sample program expecting???

I've tried all kinds of combinations for the connection string, and I keep getting errors.  Can someone PLEASE HELP???  Much appreciated.   

“Data Source=server1: port1” will open a connection to an Analysis Services instance running on port “port1” on “server1”.

I've even tried importing a pbix file that connects to our local sql server db using the syntax below and I continue to get errors such as Index was out of range.  Must be non-negative and less than size of the collection.  Or I'd get Operation returned an invalid status code 'InternalServerError'.  

“Data Source=xxx;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=xxx”

 Data Source=xxx;Initial Catalog=xxx

[TheEdcomm]

Are there any updates to this issue? Can somebody please provide exact input expected for SQL Server connection string? 

[gjross]

Perfect, thank you.