Authentication methods and SSO explained in Paginated Reports
Last Tuesday I had the pleasure of teaching a “Paginated Reports in A Day” (aka PRIAD) workshop sponsored by Microsoft. It’s been a really stimulating class with a lot of questions on Paginated Reports design and how to correctly integrate them into Power BI ecosystem.
During the workshop attendees create reports and publish them on Power BI Workspace backed by Premium Capacity.
One of the Data Source used during the workshop, is an Azure SQL Database and you may know that after publishing a Paginated Report on a Workspace you could be request to reconfigure credentials for the data source.
While doing this, one of the attendees fired a doubt.
Riccardo, do I have to check that “Report viewers can only access this data source with their own Power BI identities using DirectQuery” flag?
If you click on that “Learn more” you’ll be sent to a page referring to SSO on DirectQuery sources in Power BI:
After you publish an Azure SQL DirectQuery dataset to the service, you can enable single sign-on (SSO) using Azure Active Directory (Azure AD) OAuth2 for your end users.
When the SSO option is enabled and your users access reports built atop the data source, Power BI sends their authenticated Azure AD credentials in the queries to the Azure SQL database or data warehouse. This option enables Power BI to respect the security settings that are configured at the data source level.
In other words, the user’s credentials will be used to access the data source if this flag is checked and it makes sense to me while using OAuth2, but what does it mean in Basic (username and password)? And if I specify OAuth2 authentication and do not enable that check?
After a lot searching, contacting directly Microsoft also, I came up to this summary that clarify the possible scenarios:
A) OAuth2 Mode + SSO Flag Enabled → user consuming report credentials used to access the source
B) OAuth2 Mode + SSO Flag Disabled → the “stored” OAuth2 identity will be used to access the source, it’s the author’s identity stored while publishing
C) Basic Mode + SSO Flag Disabled → the specified username and password are used to connect to the source
D) Basic Mode + SSO Flag Enabled → same as above, while in basic the SSO Flag is simply ignored
Conclusions
While publishing Paginated Reports on a Power BI Workspace, if it’s supported by your source, you could use different combinations of authentication mode and SSO flag. According to the combination you choose different credentials will be used to connect to the source.
