Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gjross
New Member

Connection String for Embedded report which uses DirectQuery

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?

 

 

 

1 ACCEPTED SOLUTION
Brian_M
Responsive Resident
Responsive Resident

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

View solution in original post

11 REPLIES 11
sundar
New Member

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
New Member

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
New Member

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
New Member

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

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
Responsive Resident
Responsive Resident

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

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?

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!

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

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

Perfect, thank you.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors