Windows Authentication user name in Power BI Service?

[SamTrexler]

Is there a way to set a specific user account for Windows Authentication in Power BI Service? 

I am exploring a proof of concept, and trying to sort out what kind of connection we want to create, what gateways we may need, etc. I have created identical queries using our internal and public IP addresses of an OData data feed, and also using the public DNS name.

Depending on the network I am on, Windows Authentication may not work using my current credentials. When I run the queries in Excel or Power BI Desktop, I can specify either "use my current credentials" or "use this specific account", and specifying an account allows me to login and run the query. But I can't find this capability in Power BI Service.

Can this be done?

Thanks in advance,

Sam Trexler

[v-qiuyu-msft]

Hi @SamTrexler,

When we connect to data source in Power BI Service, credential used to access data source can be configured both in personal gateway and enterprise gateway. For more information, please refer to links below:

Power BI Gateway - Personal

Power BI Gateway - Enterprise

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[SamTrexler]

@v-qiuyu-msft, Thanks. That username is set up at configuration time and is the same for all users running a report or dashboard that connects to a data feed that needs the gateway, correct? That is, it is not dynamic or user-based, and can't/doesn't pass or respond to the user's login credentials - it only uses the credentials set up for the gateway?

That may be okay, but we'll have to figure a way to handle different users' access. For example, we don't want the data feed to expose financial tables if the user shouldn't have access to them. So we may need to provide different data feeds for different groups of users, and find a way to limit which feeds each user can see and try to use based on their role. Ideally, they should have one login that gets them access to all the reports & dashboards allocated to them, and they can't get to a table in a data feed that they shouldn't be abe to see. (And, of course, it should work if they use Excel, Power BI Desktop or Power BI Service or Mobile to connect to the data source.)

So in this question I'm exploring the features and limitations of Power BI Service, and would like confirmation of what the Power BI Service does (or can do) in this regard.

Thanks,

Sam

[v-qiuyu-msft · Accepted solution]

Hi @SamTrexler,

1. That username is set up at configuration time and is the same for all users running a report or dashboard that connects to a data feed that needs the gateway, correct? That is, it is not dynamic or user-based, and can't/doesn't pass or respond to the user's login credentials - it only uses the credentials set up for the gateway?

Assume that you are using the enterprise gateway, when you create a data source under the gateway, the specified credential is sent to access data source. The credential can be the same as the user run the report and dashboard or not, as long as the credential has the permission to access the data source. If you publish the .pbix file to service, when you configure data source in gateway, server name and database name need to be the same as defined in .pbix file.

2. That may be okay, but we'll have to figure a way to handle different users' access. For example, we don't want the data feed to expose financial tables if the user shouldn't have access to them. So we may need to provide different data feeds for different groups of users, and find a way to limit which feeds each user can see and try to use based on their role. Ideally, they should have one login that gets them access to all the reports & dashboards allocated to them, and they can't get to a table in a data feed that they shouldn't be abe to see. (And, of course, it should work if they use Excel, Power BI Desktop or Power BI Service or Mobile to connect to the data source.)

In your scenario, I would suggest you configure Row-Level Security to restrict data access for given users. See: Row-level security (RLS) with Power BI (Preview).

Best Regards,
Qiuyun Yu

[SamTrexler]

Hi, @v-qiuyu-msft. Thanks, that's what I needed to know.

And thanks for the link concerning RLS. Last I looked it was only available for Analysis Services, which we aren't using (yet). It looks great. We may not be able to use it with an OData feed since that doesn't suport DirectQuery, but maybe there's another connection we can use thatsatisfies our requirements.

And I definitely need to experiment with groups and roles, that looks like it applies very well to what we need to do.

Thanks for the help.

Sam

[madan]

Hi @Qiuyun_Yu ,

As per the conversation i understood that Power BI service connects to the datasource using the credentials provided in datasource settings for windows authentication and basic authentication, it never uses logged in user credentials to connect to datasource. Let me know if i am wrong here.

If its true the what is the difference between chising basc authentication and windows authentication here? i am talking in perspective of which credentials powerbi uses to connect to datasource.

As per my understanding when i use windows authentication in my connection, application uses logged in user crdentials to connect to datasource but here if powerbi service is using the static credentials provided at design time, do you think its defeting the concept of windows authentication. Can you please share your thoughts on this.

[ankitpatira]

@SamTrexler is data source public ? Can you not use Anonymous access and that way you don't have to worry about credentials in service.

[SamTrexler]

@ankitpatira, Thanks for your prompt reply.

To answer your question, I'm not sure yet - I'm still working on what's possible so I can discuss it with the security folks, developers, etc. But I'm afraid not. Most of the data is not sensitive, but some of it such as financial data is. So we need some sort of login to authenticate the users if this is out in the wild (public), and we'll need to implement some sort of role-based table-level security (probably custom). For example, only users with the FIN role can see the BankTransactions table. I don't think row-level security is needed, though.

I don't have any problem with Power BI Desktop or Excel, Windows authentication will work fine for them - even if they are outside our network. But the Power BI Service is in the cloud by nature, and I'm working to see what sort of authentication scheme will work for all three tools.

At this point I'm thinking about:

• Figuring out how to use Windows Auth with Power BI Service (this post)
• Securing the OData feed with OAuth2 - but I don't know yet how to implement that in Excel and Power BI Desktop, maybe with a Web API?
• Setting the users (internal and customers) up in our Office365 environment and using Organizational accounts

All of those have apparent down sides, but I'm hoping we can work out something that will work with all three tools andwe can integrate with our custom .NET application with its proprietary database-based authentication and authroization mechanism.

So this post is about the first question - Windows Auth in Power BI Service - I haven't really started on the other options yet. But any and all thoughts are welcome.

Thanks,

Sam