Embedded and direct query: All groups have the same gateway and datasource

[set]

 We are currently migrating from the old PowerBI Embedded Workspace Collection to the new PowerBI service.

For each of our tenants, we have a Group (formerly Workspace) in which we set the connection string for the direct query of the database and update the credentials (each tenant/group has its own credentials). 

The problem is, that every group uses the same gateway and the same datasource, which means the last connection string/credentials that are set are automatically used by all groups. We did the migration as described here: https://docs.microsoft.com/en-us/power-bi/developer/migrate-from-powerbi-embedded#directquery-datase...

Gateway ID and Datasource ID is always the same for all groups. 

Our implementation worked very well with the old Workspace Collection.

How can we achieve different direct query connections for each group with the new PowerBI Embedded Service?

[v-jiascu-msft]

Hi @set,

If I understand your scenario correctly, you used many tenants in the old embed type while only one tenant in the new embed type. Yes, all the workspaces share the same gateway. The solution could be using Power BI RLS instead. What's your embed scenario exactly?

1. Do the end users share the same application (software)? If they don't share the same application, you can embed different reports for each applicatioin.

2. Users own data or App owns data?

Best Regards,

Dale

[set]

Hi @v-jiascu-msft,

before the migration each tenant had its own workspace collection. Now, we mapped this to each tenant has its own group/workspace in Power BI Service.

If I'm informed correctly we can't use Power BI RLS with direct query. 

The tenants access their reports through the same application. The scenario is "app owns data". 

Meanwhile I've also contacted the Azure support. They also told me that all workspaces/groups and datasets within them, that connect to the same database server share the same gateway and datasource. Which is a bummer, since our solution was working before with the old Workspace Collection. We could assign different credentials to the same database server for each dataset. This is a very hard breaking change for us, no easy migration.

See: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20381530-allow-multiple-credentia...

Kind regards

[set · Accepted solution]

I have solved this now by adding a DNS alias for each tenant to the Azure SQL Server.

[Anonymous]

Hi @set,

- what kind of datasource do you use (Azure Sql, on-premises SQL, etc)?

- do you have one database for all tenants or one database for each?

- do you use any connection parameters in the reports

You may read my blog post on how we tackled the multitenant scenario. You may find some useful code in there.

Regards,

Thanos

[set]

Hi @Anonymous,

- We use Azure SQL as database

- It's one database for all tenants. But each tenant has its own credentials. We use RLS on SQL level (not to be mistaken with PowerBI RLS).

- The connection string and credentials of the database are set as it is documented in the link of my initial post

PS: For some reason my reply always gets marked as spam. I hope that there won't be any double-posts

Kind regards