topic Re: Connection String for Embedded report which uses DirectQuery in Developer

Connection String for Embedded report which uses DirectQuery

gjross — Thu, 14 Apr 2016 18:54:12 GMT

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?

Re: Connection String for Embedded report which uses DirectQuery

Infero — Fri, 22 Apr 2016 08:47:52 GMT

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

Re: Connection String for Embedded report which uses DirectQuery

gjross — Fri, 22 Apr 2016 10:33:06 GMT

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.

Re: Connection String for Embedded report which uses DirectQuery

Brian_M — Mon, 09 May 2016 16:56:48 GMT

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

Re: Connection String for Embedded report which uses DirectQuery

gjross — Mon, 09 May 2016 19:05:33 GMT

Perfect, thank you.

Re: Connection String for Embedded report which uses DirectQuery

Oussama — Sun, 15 May 2016 19:06:31 GMT

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?

Re: Connection String for Embedded report which uses DirectQuery

KrisTea — Fri, 10 Jun 2016 21:09:18 GMT

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!

Re: Connection String for Embedded report which uses DirectQuery

KrisTea — Fri, 10 Jun 2016 21:57:20 GMT

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

Re: Connection String for Embedded report which uses DirectQuery

TheEdcomm — Wed, 02 Nov 2016 02:43:27 GMT

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

Re: Connection String for Embedded report which uses DirectQuery

gpanagakis — Wed, 14 Dec 2016 14:38:22 GMT

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.

Re: Connection String for Embedded report which uses DirectQuery

gpanagakis — Wed, 14 Dec 2016 14:49:26 GMT

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!

Re: Connection String for Embedded report which uses DirectQuery

sundar — Fri, 03 Feb 2017 13:32:33 GMT

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.