Embedded DirectQuery connection strings with same target/different user interfering with each other?

[ddevogel]

Hi,

We have a single SQL Azure database with a schema per customer and a SQL user per schema that has access only to that schema. We then have a DirectQuery embedded report per customer that is sourced by the customer's schema. In the old workspace collection model we would save an instance of the report pbix in Desktop, deploy it and then update the database connection string based on the appropriate schema/user via the powerbi cli. That worked great.

In the new PowerBI service model (app.powerbi.com) we are deploying the customer's pbix to a customer specific workspace and setting credentials on its dataset but finding that those credentials are flowing to datasets in other workspaces that use the connection to the same server/database. The net result is that a previously loadable report will stop working because it no longer has access to its schema - apparently because it's using the most recently saved credentials from another workspace.

Is it no longer possible to do what we were doing in the workspace collection days - have a dataset connection string that varied from others only by username/pwd? Is a connection string that references the same server/database somehow global across workspaces in the new PowerBI or are we just setting ours incorrectly? Thanks for any hints or advice.

Dave

[v-shex-msft]

HI @ddevogel,

Current I think only template content pack suitable for your requirement.

For power bi app, it still not support these functions, maybe you can submit an idea for your requirement.

BTW, I'd like to suggest you use power bi embedded to achieve dynamic report.(setting RLS based on username function)

3 ways to embed Power BI

RLS with UserName()

Regards,

Xiaoxin Sheng

[ddevogel]

Thanks @v-shex-msft, I'm not sure a content pack will work in our case but I wanted to follow up with an idea I had that seems to be working. I've created a DNS alias to the reporting database server for each of our customers and am using those aliases as server names in each report's connection string. I think that's preventing PowerBI from reusing the single server name connection with potentially the wrong credentials. I'll follow up if I hit any snags with that approach but wanted to post in case anyone has this same issue.

Dave