cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nirrobi
Helper V
Helper V

connect to the same database with 2 different users

Hi all,

 

I have in my company database for 5 companies. Every company has diffrent user name and password but all of them connect to the same database.

I succceed to create stunnig report to my company and now I was asked to create report for all of the companies together to make comparison between the companies,

 

In a nut shell, is it possible to create in the same report connection to one database with few users?

 

Many thanks.

 

 

Nir.

1 ACCEPTED SOLUTION

HI,

 

I have encounter the same probleme with Web connector (API) in PowerBi.

Unfortunetly you can't connect the same data source with different credentials in the same pbix file i think.

 

But an alternative solution you can test (or the DBA administrator)

- In the SQL database you can create a few views who combine data of different company

- This view as a particular shema (different of other tables)

- You can attribute specific acces (read only) in this view using the specific shema

 

And if a day a new company is create you've just modify you're views 😉

 

Hope it's help you.

View solution in original post

12 REPLIES 12
WouterV
Frequent Visitor

Is there already a way to achieve this in the meantime? We'd like to use User X for Report 1 and User Y for Report 2, both using the same Azure SQL Server. So far, seems like behavior is still as in 2015 (start of this thread) where change in credentials for Report 1 also leads to changes in credentials for Report 2.

Anonymous
Not applicable

This is a real issue and I treat it as a bug. 
If I have different reports with own datasets in different workspaces why the connection to the same database is always goes using one of the connection string? 
Why do you have different datasets at all in such case? Just allow only one. I suspect this is your internal optimization to reduce the number of connections caused this issue, but it should check additionally authorization parameters, not only database name and server.

I want to restrict access to the sensitive data in multitenant SaaS application which uses the same database shared schema and I have no choice. And please don't offer me Row-Level Security, because report designer in such case have access to the data which he shouldn't.
This is really weird when cloud multitenant SaaS application designed in a way to the on-premise app. This is not "cloud-first" approach.  

Anonymous
Not applicable

This is a real issue and I treat it as a bug. 
If I have different reports with own datasets in different workspaces why the connection to the same database is always goes using one of the connection string?  
Why do you have a different dataset at all in such case? Just allow only one. I suspect your internal optimization to reduce the number of connections caused this issue, but it should check additionally authorization parameters, not only database name and server.

I want to restrict access to the sensitive data in multitenant SaaS application which uses the same database shared schema and I have no choice. And please don't offer me Row-Level Security, because report designer in such case have access to the data which he shouldn't.
This is really weird when cloud multitenant SaaS application designed in a way to the on-premise app. This is not "cloud-first" approach.  

Greg_Deckler
Super User
Super User

If I am understanding this correctly, what you want to do is create a Content Pack in the service and put your data set and report in that content pack. Then, you can share that out with your users and they can load the content pack into their Power BI. Each user could then set the credentials on the data source to their own. You could do the same thing by sending copies of the PBIX file to each user and have them set the credentials on the data source to their own.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Do you have any insight into how this topic plays out with the OPDG?  Maybe a workaround that doesn't fall to the data layer where we'd need to change schemas etc.  We're in the middle of a POC and at this point the cost of doing that is high.  

 

My company uses multiple user logins for same database to ensure different levels of security.  For example we reference the TNSnames.ora file where multiple databases are listed.  However there are also multiple logins for a particular database.  Some logins use grant selects to get access to lower level schemas, but other are just flat out different and one will never get all access for security purposes.  Note: this isn't an issue with our current Business Objects implementation.  

 

I can create 2 separate PBIX files with different logins and locally load the files separately.  But if I try to create a another database connection using the gateway with the alternate set of credentials it interprets it as a duplicate connection.  Seems to be a deal-killer for schedule refresh, right?  

 

Thanks

 

 

Thank for the reply. 

 

For now the power bi project in our company is "one men show" (me :-)). 

I was able to establish reports and dashboard for one company- great success 

but now the other related company want to implement the power bi - no problem for me (I think) and the owner want to create one dashboard that make comparison between the companies- I need to have data for 3 companies in the same report - (one databease for the group with different user and password to every company). 

I am not familiar yet with content pack but from what I read (and hopefully understood) it not what I look for. 

 

How are you splitting up the data in the database? Are you exposing different schema's and tables to each company, and they only see their own tables? Or are you using some other method like row level permissions?

 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Hi. 

 

As much as I know we have diffrent table to each company (company name.table name)(almost the exact tables to all of the  companies)

In our operational  system every user see only his company (tables) but I need to make comparison to all of the companies  so need to connect to the same database with different user 3 time in the same report. 

Is it possible at all using the power bi?

we succeed to implement this method in powerpivot but till now no success in power bi. 

 

Many thanks (again)!

If you have access to read all the companies (with seperate users), why not just create one read-only user on the SQL database that has access to read all company tables?

I haven't tested out the scenerio of trying to connect to the same source multiple times with different credentials yet, so I can't speak to that.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

I have access to all users and companies. 

I am not sure i am able to create one "super user" as you recommend but I will try. 

 

 

HI,

 

I have encounter the same probleme with Web connector (API) in PowerBi.

Unfortunetly you can't connect the same data source with different credentials in the same pbix file i think.

 

But an alternative solution you can test (or the DBA administrator)

- In the SQL database you can create a few views who combine data of different company

- This view as a particular shema (different of other tables)

- You can attribute specific acces (read only) in this view using the specific shema

 

And if a day a new company is create you've just modify you're views 😉

 

Hope it's help you.

Anonymous
Not applicable

That's a data solution.  Not a pbi solution. 

 

We use oracle.  We have multiple users and logins for security purposes.  The database is the same.   If I use the same db connection it assumes the last login.  No options but to change it.  For security and access reasons, I need to toggle 3 or more.  

 

Is this is situation where the pbi product isn't quite enterprise ready yet?  

 

Also, what if I'm using ezconnect?  Are there any special characters that pbi will ignore and still connect that will fool pbi into prompting me for new user:pass creds?  

 

We're stuck on a POC to replace business objects.  I've got BOBJ universe designers thinking this is a major issue and I agree.  Any advice is appreciated.  

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10

The Power BI Community Show

Join us on October 3 at 11 am PST when Amit Chandak, a Power BI Super User, will demo how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors