How to create a connection to a database with integrated credentials and then use it in PBI Desktop?

[Fern_21]

Hi all!

On Power BI, is it possible to create a connection to a database that already includes credentials, so that a user in Power BI Desktop can directly use that connection without needing to provide access credentials?

My user should be able to connect to the database and create their report without having access to the login credentials.

Thanks

[v-prasare]

Hi @Fern_21,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[v-prasare]

Hi @Fern_21,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[Praful_Potphode]

Hi @Fern_21 ,

Directly creating a file (like a .pbix or .pbids) with embedded database credentials for another user to open and use in Power BI Desktop is not possible for security reasons.

To avoid giving access to database for all the users in organization, one person with database access can load

all the data in pbix in import mode and then share it with all users.in this approach, users will require access to semantic model not the database.this approach is known as self service bi.there are lot of articles on it.

https://learn.microsoft.com/en-us/power-bi/connect-data/service-datasets-manage-access-permissions 

https://radacad.com/power-bi-user-access-levels-build-and-edit-are-different/ 

https://learn.microsoft.com/en-us/power-bi/connect-data/service-datasets-across-workspaces 

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[Fern_21]

Hi @Praful_Potphode  and @grazitti_sapna  !

If I create a semantic model and share it with the user, they can only connect in live connection mode and there are limitations. Isn't there a way to use a dataflow?

Thanks

[Praful_Potphode]

Hi @Fern_21 ,

Yes they can use dataflows to bring in user managed data and build composite models on top of it.

another apparoach can be to mirror the SQL Server data in fabric and then provide access inside fabric 

https://learn.microsoft.com/en-us/fabric/mirroring/sql-server 

please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[grazitti_sapna]

@Fern_21, yes you can use a dataflow or fabric lakehouse to achieve it

[Fern_21]

Hi @grazitti_sapna !

What is the fastest and most functional solution? What are the pros and cons of each?
Thanks

[grazitti_sapna]

Hi @Fern_21, 

You can go for Dataflow,

Fast
Easy
Secure (no credentials exposed)
Cheapest
Lets users freely model their own semantic models

[Fern_21]

Hi @grazitti_sapna !

With dataflows, can the user only have access to the tables I have loaded into the dataflows? Can't he put more? furthermore, on the tables they can do all the operations they want?

[grazitti_sapna · Accepted solution]

Hi @Fern_21,

Users can ONLY access the tables you include in the Dataflow. 

When users connect to your Dataflow in Power BI Desktop, they can:

Import only the entities (tables) inside the Dataflow

Build their own model on top of those tables

Combine with other local files (Excel, CSV, etc.) if they want

[grazitti_sapna]

Hi @Fern_21,

Not possible while creating a connection  but you can achieve is by either created a shared dataset or by creating a semantic model and sharing the access to users.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[rohit1991]

Hii @Fern_21 

Desktop always connects to a database using the credentials of the person who is running Desktop, and it will always ask them to authenticate.

If you want users to connect without entering credentials, this is only possible in Power BI Service using a Gateway + SSO (Kerberos/Entra ID).
There is no method or workaround to pass integrated credentials automatically into Power BI Desktop.

[Fern_21]

Hi @rohit1991 !

On power bi service how can the user develop a report? I really need the power bi desktop to create a report from Azure MySQL

[rohit1991]

Hii @Fern_21 

In Power BI Service, a user can only build a report from an already-published dataset.
They can go to Get Data >> select the existing dataset >> Create report and design it in the browser.

But connecting to a new database, creating a new data model, or using Import/DirectQuery is only possible in Power BI Desktop.
Power BI Service cannot create new connections  so Desktop is still required.