SSO Not Working for Dataverse Datasource

[djskro]

Hello, I have a sample-report that has two datasources:

1. Dataverse (new CDS connector, DirectQuery)
2. SQL Server (via a gateway, DirectQuery)

I have enabled the SSO setting in the Dataverse credential:

However, when users access the report, they are able to see Dataverse data for which they've had their permissions revoked--implying that the SSO isn't working and that my Dataverse credential is being used.

Any suggestions? Thanks.

[mscottsewell]

For troubleshooting - open a VSCode or SSMS query window while logged in as that user - then query the table. - if the results are filtered as expected, you can move on to the next steps in troubleshooting. - 
The settings in your original screenshot look correct. - be sure each table in the model that you want to filter is set to Direct Query (and not import or dual.)
You can also try using the SQL connector in power query and write your query as a native SQL statement Power Query Dataverse connector - Power Query | Microsoft Learn (The Dataverse connector is effectively a wrapper around the SQL connector - so I wouldn't assume it works any differently.)

Here's an example native query -
[Replaced - see follow up post]

[mscottsewell]

@djskro I realized the script I posted above is not my 'default' pattern -
This is what I use as the basis of every query:

let
    Dataverse = CommonDataService.Database(TDSEndPoint,[CreateNavigationProperties=false]),
    Source = Value.NativeQuery(Dataverse,"
	
	SELECT [Base].accountid,
		[Base].name [Customer Name]
	FROM [dbo].[account] AS Base

	" ,null ,[EnableFolding=true])
in
    Source

(BTW, I have a parameter named "TDSEndPoint" with a value of "myEnvironment.crm.dynamics.com" in the report and I reference it for all queries - it just makes it easier to move from one destination to another.)

[djskro]

Thank you for this, Scott.

Going through these, what should we do if, in SSMS, logged in as a new test-account, we can, indeed, see and query the tables we do not want the test-account to be able to access (perhaps explaining our results in the Power BI report)?

How do we reconcile the screenshots below of the Power Apps interface with what is clearly visible to the user in SSMS?

[mscottsewell · Accepted solution]

@djskro Adding/removing from the Power Apps interface doesn't have an impact on the security of the table at the dataverse layer. - Dataverse Security Roles (and in some cases, shares or inheritance) are the determining factor as to whether a user can view a row of data in a table.

Look at the security role(s) that the user has been granted - and within those roles, ensure the user has no 'read' permissions for the table. - (The dataverse security roles are additive, so if they have 20 roles and only one gives them organizational 'read' permissions - then they'll have read permission for the data in the table, it doesn't matter what the other roles have.)

Also, be sure to check to see if the user is a member of a team where a security role has granted them permission to read the table.

A really helpful tool for troubleshooting/understanding the security of a specific user on a specific record in Dataverse is the XrmToolBox "Access Checker" -  Also, the "Privileges Discovery" tool in XrmToolBox will be useful.

(I don't think the lack of 'read' pemission on a table will block them from seeing that a table  exists, but it should block the table from returning any data. )

[djskro]

Thank you, Scott. This is helpful info.

[Anonymous]

Hi,@djskro I am glad to help you.

Unfortunately due to circumstances I am unable to test SSO and try to reproduce your issue.
Based on your description, you could try clearing your browser's cache and then reconfigure and test the SSO functionality to ensure that the relevant buttons are set correctly and that the user's permissions are set correctly in the Dataverse to ensure that they can only access the data that they are authorized to access.
This is because browser caching can potentially cause SSO (Single Sign-On) issues. The cache may store old authentication information or session data which can affect the proper functioning of SSO, you can try clearing the browser cache or using privacy mode to access power bi reports.

Here is some documentation to help you out.
URL:
Test single sign-on (SSO) configuration - Power BI | Microsoft Learn
Use OAuth authentication with Microsoft Dataverse (Dataverse) - Power Apps | Microsoft Learn

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[djskro]

Hello, thank you for your reply. Unfortunately, this is not helpful.

We have tested this with a completely new account and are experiencing the same result. Users (test-accounts) with no permissions at all to the Dataverse data are able to see the visuals load fully in DirectQuery mode. This suggests, again, that it is my own (the model owner's) credential that's being used, not the credential of the end-user--i.e., SSO is not working.

I think that the ability to test SSO yourself is crucial here. The documentation did not help in this case, unfortunately. I'm concerned about the functionality of this parameter in the connection, as this has obvious data-security ramifications.

Here is what our test-users see when trying to view the tables in Power Apps, yet the DirectQuery (SSO switch enabled) report to these tables in Power BI returns data:

We have further tested this by disabling and then reenabling the SSO switch in the personal connection. I've also tried creating a sharable cloud connection and doing it that way. I get the same incorrect result.

Is there some kind of special setting to disable all access to Dataverse tables (for specified users) via the various endpoints, not just the front-end?

Is there a way we can verify that this SSO functionality between Dataverse and Power BI (DirectQuery), using the modern Dataverse connector, is operating properly (paging @mscottsewell).

Thank you.