How To Share SQL Data Source Credentials Without Giving Them?

[RBurke]

Here's an issue we're looking to solve:

I work for a software company where our program stores backend data to an SQL database. This SQL server can be hosted by the end user or by us, in Azure. We're looking at providing sample .pbit or .pbix files for BI reporting on this backend data. The .pbit file works fine for On-Prem/self hosted clients, as they host their own data.

For clients where we host their data, according to security policy, we do not currently provide SQL credentials to their contained Azure database. That's what I'm looking for a solution on. How can we share .pbix/.pbit files with clients and connect them to their hosted database without giving them their sysadmin credentials?

What I've tried:

• We could remote in and manually enter credentials for a customer, thus saving those credentials in C:\Users\{Username}\AppData\PowerBI Desktop (or similar folder)\User.zip.
  • The issue with this is when we try and copy that file to another user, Power BI says "Can't decrypt credentials". I wasn't finding any help on this, as I don't think that's something that's intended to work.
• Parametize the credentials
  • Obviously that wouldn't work, because they would just be stored in the file in plain text
• Use an ODBC DSN file
  • Again, doesn't quite work. You can technically store passwords in a DSN file, but it's still in plain text.

The only solutions I'm currently thinking of are:

• Just creating a read-only user for the client database and providing those credentials, not ideal for what we intend
• Our program stores and uses an encrypted SQL password for connecting. I don't know much about creating custom Data Connectors, but maybe we could have our Dev team take a look and try and develop a connector for this. This might sound like a more obvious solution, but we're probably too limited on resources at the moment for that to be feasible.

Any other solutions on this?

Thanks in advance.

[otravers]

Creating a read-only user to the client database - probably alongside creating a set of user-facing views rather than giving them access to physical tables - sounds like the way to go. Anything you'd try to do on the desktop is going to feel like an unsupported hack.

You may also want to consider setting up dataflows (and/or possibly the new datamarts, though they're not mature enough right now) in the customer's tenant, save the SQL credentials in the Power BI service, and point the client's PBIX to their dataflow/datamart. They'll authenticate using their Microsoft account and won't ever see the SQL credentials.