Understanding D365/Dataverse Security in Power BI / Fabric

Data security within Dynamics 365 (D365) can be quite complex and should not be underestimated or under-scoped.  However, not all customers need or implement a complex model – so understanding the customer’s implementation up front is essential in architecting an appropriate business intelligence/analytics solution.

Consider an example where two Dynamics 365 log into the same Dynamics organization and run the same ‘All Opportunities’ report at the same time but the two users get entirely different results in their respective reports – Yet in another organization, two users login to their CRM at the same time and see identical ‘All Opportunities’ reports.  

Which is correct behavior – which is the incorrect behavior?

It’s an impossible question because the ‘correct’ behavior depends on the business’s needs and reflects how Dynamics 365 was implemented to support those needs.  In some implementations, the data is restricted to only the opportunities in the users’ territory – in other implementations; all sales users are allowed to see (but perhaps not edit) all opportunities regardless of the owner/territory.

Why is the challenge of Dynamics 365 security in reporting more interesting than other sources?

The nature of CRM is often that some users should see some data, while not being allowed to see other data.

Consider a hypothetical, but realistic, sales organization with Salespeople, Regional Managers, and a handful of Executive Vice Presidents of Sales - per division.

In this organization, salespeople can see only their opportunities.  Regional managers have visibility to their opportunities, plus the opportunities owned by multiple salespeople in their region.  The Executive VP’s sees all opportunities within their division.

Since an opportunity might cross divisions or regions, those opportunities are “shared” with multiple people who wouldn’t otherwise see it.

Some organizations allow most records to be visible to all users, EXCEPT the highly confidential deals that have additional restrictions on them to limit their visibility.

This type of record-by-record entitlement matrix makes D365 data highly fragmented when it comes to determining who should see which record when preparing a report. – D365 handles this with a complex security model that ensures all data retrieved is filtered through the permissions of the user who retrieves it.

Securing data for reporting is not unique to D365

Many data sources are protected by binary ‘read’ security – either you can see all the data within the reporting area, or you can’t.  e.g., a person with permissions to view transaction log data would be able to see all the data in those logs. – This basic security is simple to implement.

Some data sources have natural segmentation between data and reporting groups – e.g., users in working in payables can see inbound inventory data but cannot see the company’s payroll data – Or users in the Eastern Division can see their warehouses’ inventory transactions, but not the Western Division’s warehouses’ transactions– this type of gross segmentation is common and is relatively easy to manage in the reporting functions.

Entitlements / Data inside CRM and their impact on Power BI Security:

Before diving too deeply, it’s important to ask some basic design questions about the targeted D365 implementation.

1.      Is D365 implemented with any record-level security partitioning? (i.e., Can some users see more, or fewer, records (such as opportunities) than others?)

Some organizations take the approach that all sales users should be able to ‘view’ any quote/order/opportunity etc. – while others take a more restrictive approach and segment sales users’ visibility based on factors like business units, teams, ownership, status, or sharing.  If the organization does not implement any intra-organization "read" security, everything gets simple – otherwise, it’s important to understand the type of security needed.

2.      If record level security/partitioning for "reads" is implemented, Is the audience of the report entitled to see all records being reported on?

Even if the organization implements a complex security model, If the audience of the report is an executive who can already see all records, then the security model inside Dynamics 365 need not be a factor in the design of the report model.

3.      Is the data in the report either summarized sufficiently or already in a state where partitioning the data is no longer needed?

Sometimes the report data may be important to keep private at the individual row level, but the data in the report is sufficiently aggregated to the point where the details of individual rows are sufficiently obscured or are no longer relevant.

4.      Can the data be secured by filtering on a large partition outside of CRM? – e.g., business unit, country, multi-state region?

Power BI has Row-Level security (RLS), and it’s possible to build groups/filters to ensure only users within those groups can see the specific slices of data. – This adds some level of additional management of rights/group membership, but it could address the security challenges faced by organizations that need to separate data by large partitions of the organization. 

5. Is the report result set small enough to use Direct Query over the TDS endpoint?

Power BI can connect to the TDS endpoint of Dynamics and inherit security from Dynamics. The Dynamics TDS endpoint is queried via simple SQL in a report configured to use Direct Query. While it might initially appear to be a simple solution to all security challenges, this comes at a pretty steep performance impact on the report itself.

Conclusion

There are a variety of security/partitioning strategies to implement but knowing when you need to consider implementing partitioning in Power BI – or when you can safely move forward without attempting to replicate the CRM security model.