Tip #1409: Count records for all tables in Dataverse

Want to count all records in all Dataverse tables like a boss? Say no mo.

SELECT 
 CONCAT(
 'SELECT ''', name, ''' AS TableName, 
 COUNT_BIG(1) AS CountOfRows FROM ', 
 name,
 ' UNION')
FROM 
	sys.tables
WHERE
	TYPE = 'U'
AND SCHEMA_ID = 1
  • Copy all results (Ctrl-A), open New Query window (Ctrl-N), paste (Ctrl-V)
  • Scroll to the end, remove the last word UNION and add ORDER BY 2 DESC line
  • Execute
  • You are welcome
Screenshot of T-SQL table results with the partial list of table names and row count for each displayed.

Note: the execution may timeout if your Dataverse is of any decent size. Use trial/error approach to figure out executable chunks – you probably know your largest tables anyway.

Tip #1408: When tomorrow comes in Power Automate

Ever wondered how to use Dataverse date filters like “Today” or “Tomorrow” when listing records in Power Automate? Yes, plonking in the entire FetchXML with <condition attribute="enabler_stamp" operator="tomorrow" /> is one way to handle that. The other is to use query function Tomorrow in the filter:

Microsoft.Dynamics.CRM.Tomorrow(PropertyName='enabler_stamp')

Here’s the catch: “tomorrow” is relative. If you live anywhere west of Cyprus and east of Hawaii, most of the time I can see the future and tell you what weather is going to be tomorrow. Power Automate will use the timezone of the user account used in connection to determine the timezone and relative date/time constructs (see Microsoft DataverseWeb API Query Function Reference for the full list or just use FetchXML Builder for XrmToolBox).

If you use service principal to connect, timezone for the associated app user is UTC by default but you can change it using User Settings Utility from XrmToolBox. (Oh, the irony of fielding a question from Tanguy and send him to his own tool!). Hint: set timezone to Kiribati just to mess with the users – there literally will be no today in your queries, only tomorrow. 😈

Cover photo by Brett Jordan on Unsplash

Tip #1407: How to secure Power Apps portal from making the news

You are a CEO of Rykita, a worldwide manufacturer of power tools used by millions. You wake up invigorated and ready for action only to see the news headlines “Rykita injures more than a thousand customers”, “Calls for Rykita to blunt their tools”, “Rykita customers bleed profusely”, “Users of Rykita tools risk infection if injured”, and “Loyal customer uses Rykita nail gun to shoot themselves in a foot”.

That’s what the reports about Power Apps portals leaking the data looked like this morning. I encourage you to ignore all clickbait headings like “Microsoft Spills 38 Million Sensitive Data Records” and go straight for the original analysis and report by UpGuard By Design: How Default Permissions on Microsoft Power Apps Exposed Millions. While I disagree with some of their assessments, credit is where credit’s due: UpGuard’s behaviour throughout the incident, the analysis, and the report are nothing but professional.

Not all of the affected parties have welcomed the news. Some of the authorities (squinted look in the general direction of state of Indiana) engaged into what I can only classify as a borderline bullying behaviour towards the security company. So what is the kerfuffle all about?

The problem

Power Apps portals have a list feature what is a “data-driven configuration that you use to add a webpage that will render a list of records without the need for a developer to surface the grid in the portal”. To put it simply, lists allow maker to take a Dataverse view and render it on a web page.

Canvas and model-driven Power Apps use Dataverse security roles and privileges to manage users’ access to the tables (see Security concepts in Microsoft Dataverse). Power Apps portals, by definition, provide access for external users (who can be either authenticated or anonymous), and the concepts of security roles and privileges are replaced with web roles and table permissions (see Set up security in portals with table permissions).

One of the important but understated properties of the list has always been the “Enable table permissions”, buried way way down the list form.

Screenshot of the list form with "Enable Table Permissions" checkbox highlighted. Checkbox is cleared.

That box does exactly what it says: it applies table permissions to the list. Let put it this way: if this box is unchecked then the list will return the data as defined by the view, regardless of who the portal user is. But if this box is checked and no permissions are defined for the underlying table, users will get “Access denied” error, authenticated or not, and that includes administrators as well. Any table permission granted needs to be explicit and associated with one of the user’s web roles. (Make sure you have some additional bedtime reading about securing the lists)

Why it has never been a big deal? Because traditionally lists are used as part of a web page and access to the web page can and usually is secured separately, using page permissions. No page access – no data to leak. Simples!

Why is it now a big deal? As the use of the portals grew, makers discovered new techniques and found new applications for the portal technology. One of the features of the portal lists is ability to publish the table data as OData feed.

OData feed tab of the list form containing Enabled checkbox that is checked indicating that OData feeds are enabled.

Hit the https://foobar.powerappsportals.com/_odata/entitysetname from anywhere and get back json containing the view data. With couple mouse clicks and absolutely no code you get yourself a RESTful web service to be used from client-side javascript, in data integration scenarios, you name it.

And here is the problem. If “Enable table permissions” is not checked, anyone can get to that data feed. And that’s exactly what UpGuard folks have uncovered. But wait a minute! How did they get the list of Power Apps portals? The answer is in this quote:

First we identified the addresses of Power Apps portals. Power Apps portals are assigned a subdomain of the site “powerappsportals.com,” so using common subdomain enumeration techniques generated a list of customer portals.

UpGuard

The key is “common subdomain enumeration techniques”. There are plenty of references on the interwebs for those who are interested, and some of the techniques are indeed extremely clever. In short: it’s possible to find out if not all then most of the subdomains that are in active use. Especially if they start pinging services like Google Analytics and start appearing in the search results. That’s just the nature of the public web.

Microsoft response

The official position is quite straightforward

Our products provide customers flexibility and privacy features to design scalable solutions that meet a wide variety of needs. We take security and privacy seriously, and we encourage our customers to use best practices when configuring products in ways that best meet their privacy needs.

Honestly, I wouldn’t expect anything more than that from a company of Microsoft’s size. On a microscale, from what I know, Microsoft folks are working closely with the affected customers to ensure portal settings are consistent with the customers’ needs. To be fair, it’s a very, very small subset of the customers.

The warning has been plastered all over OData feeds documentation for quite some time:

Screenshot of OData feeds documentation page with the following white on red text: "Use caution when enabling OData feeds without table permissions for sensitive information. OData feed is accessible anonymously and without authorization checks if Enable Table Permissions is disabled."

Customers who had anonymous feeds did received email notifications:

Screenshot of a notification email sent from Microsoft Dynamics 365 notification service to customers who have anonymous OData feeds enabled in a portal.

If customer’s security admins were doing their job they would have noticed MC277597 in the Message Center within the Microsoft 365 admin center:

Screenshot of Microsoft 365 security center notification MC277597 titled "Important information about your Microsoft Power Apps Portals service". The notification lists the portals affected by anonymous OData feeds and includes instructions how to fix the problem.

Admittedly some of the actions above seem to be the direct result of UpGuard discovery but the response seems to be well coordinated and targeted.

Power Apps portals Studio, the primary maker experience, have strong settings baked in, and the table permissions are enabled for the lists by default. For the makers still using the Power Apps portal management app (I know I still do every now and then), the latest version will enforce new, more secure defaults where table permissions are checked for new lists.

Even better news is that any new portal provisioned after 15 August 2021 will not allow to disable table permissions when OData feed is enabled:

Screenshot of the error message that stops user from saving a list with OData Feed enabled and table permissions disabled. The error message reads "Table permissions must be enabled from the General tab because the OData feed is enabled".

Note: that means new portals and not just new tables created within an existing portal.

Quick check

Do you have a Power Apps portal and would like to know if you have a problem? Have you provisioned the portal after 15 August 2021? If yes then you do not have the problem. Otherwise use one of these methods:

  1. Open https://foobar.powerappsportals.com/_odata in InPrivate mode. Any collection listed there is a potential security leak. See Anonymous access available to OData feed for step-by-step instructions.
  2. Run Portal Checker. It will flag anonymous access to forms, lists, and OData feeds in one go.
  3. Since portal configuration is stored in Dataverse, Nick Doelman suggested a clever query: find all Lists where Enable Table Permission is not set to Yes.
    Advanced Find screen with a condition that returns all lists without table permissions

General advice. If you can, avoid using blunt tools like OData feeds. Instead, opt for finesse and precision of hand-crafted liquid to return JSON or XML. The upside is that liquid cannot bypass table permissions so you won’t be able to leak the data, not by accident, anyway.

In closing

Despite the clickbait headlines floating around, we are not talking about data breach but rather data leak that is the direct result of the portal misconfiguration. UpGuard did an excellent job in locating and analyzing the leaks, no doubt about that. They did acknowledged in the article that these leaks are the results of the customer configuration choices, and that tooling and documentation exist to avoid and mitigate such a situation.

In the hindsight, Microsoft should’ve enforced the table permissions long time ago. The enforcement could’ve been done, for example, as the portals moved from microsoftcrmportals.com domain to powerappsportals.com. The new portal version does address the weakness, I just wish it was done earlier, perhaps at the expense of a few broken sites (beats the leak in my books).

It’s important to understand who’s the target audience of the mitigation efforts. Many people discussing the incident, including UpGuard, conflate users and makers. Portal users are not the same as portal makers. While we can’t expect users to even remotely be concerned with OData or table permissions, we hold makers to a much higher standard. They should have known better, low code or not. Documentation and training exist for a reason:

Sometimes we have no choice but to sell power tools into the hands of users who are not sufficiently trained. The best we can do in these situations is to make sure the sharp tools are sheathed and some of the more dangerous ones have additional security features like Dead man’s switch.

And if you run a workshop that uses Rykita Power Tools©️ , make sure your employees are up to date with training and certification, read documentation, follow safety instructions, know best practices, and do not toss emails from Rykita Security away. At the same time, keep your first aid kit ready in case someone runs with scissors.

Tip #1406: Flow history takes long time to load

Cover photo by invisiblepower from Pexels

Tip #1405: Unable to assign license to a guest user

B2B collaboration is great. For example, you can share a canvas app with guest users or invite users to use with Azure Active Directory B2B collaboration . One of the steps in the process is adding B2B collaboration users in the Azure portal. But when you try adding a license assignment you maybe faced with the unexpected “License assignments failed”:

Azure "Update license assignments" screen with the user attempting to assign Power BI (free) license to the guest user. Error message "License assignment failed" is displayed.

Why would assignment of a free license fail? The reason is revealed when you click “License assignments failed” link:

Screenshot with the additional error details displayed. Words "License cannot be assigned to a user without a usage location specified" are highlighted

License cannot be assigned to a user without a usage location specified

Albert Einstein, 1948

That’s right, just edit the user profile and tell Azure where the user is and you’ll be able to assign the license, free or otherwise.

Cover photo by Elijah Macleod on Unsplash

Tip #1404: App secrets that last longer than 2 years

Using service principals is the great way to ensure that Dataverse connections in your Power Automate flows are not user-dependent and nothing is going to break when the original creator’s account gets disabled. These are good step-by-step instructions: How to setup a Common Data Service Application User (and why) (I’ll give Nick Doelman a separate Spießrutenlaufen later on for granting permissions nobody needs).

One of the steps is to create a secret and as of lately, the maximum expiration date can only be up to 2 years in the future. (And no, using custom is not going to help). But what if we want to last longer?

Azure dialog to add a client secret. Expiration date dropdown is expanded showing that the maximum expiration date can only be up to 2 years in the future

Yes, it’s a good idea to rotate your secrets. No, it’s not a good idea to do so until there are tools to automatically update all connections / connection references in your environment to use the new secret (managed identities / Azure Vaults, anyone?). I was indirectly told that Isaac Stephens figured it all out and it is indeed possible. Turns out, it’s not that complicated.

  1. Navigate to your app registration in Azure portal. (I trust you know how to do that as you presumably created that registration before, right?)
  2. Get your object id. (I knew it’ll come handy one day!)
    Screenshot of app registration properties in Azure portal. Object ID property is highlighted.
  3. Open Azure CLI prompt and sign in. I like using in-place prompt.
    Screenshot of Azure portal page with an arrow pointing to the icon that launches Azure CLI prompt directly in browser.
  4. Enter this command to regenerate a secret that expires in 42 years.
    az ad app credential reset --id 42937242-dead-beef-dead-42d833e1cb42 --years 42
  5. Don’t forget to copy the generated secret from the output – that will be displayed only once!

For additional syntax see az ad app credential | Microsoft Docs.

Cover photo by Osama Elsayed on Unsplash

Tip #1402: Why you should not use Microsoft Lists/SharePoint Lists for business-critical Power Apps

SharePoint lists (and now Microsoft Lists) are an easy way to quickly create lists of data for use in a Power App. Makers can quickly create lists, add fields, and populate data for canvas apps.

However, not all apps are equal from a complexity perspective.

If the app is simple and contains data that is not sensitive, Microsoft Lists can be a good choice for data storage for canvas apps.

However, if your app contains many different lists, large amounts of data, complex relationships between tables, and sensitive data for which all of the users of your ap should not have access, then you should consider using alternative data sources for you canvas app.

Here are the top 5 reasons why you should not use SharePoint lists for high-criticality Power Apps:

  1. It is not a relational database. Each list is independent and SharePoint lists were never intended to be used as a relational database. While SharePoint does have a field type called Lookup, it does not serve as a true relational database. Lookup fields in SharePoint lists are very difficult to work with in canvas apps, and performance with many lists in the same app will be slower than if you have a true relational database.
  2. Common formulas are not delegatable to Sharepoint: With canvas apps, heavy processing of data is delegated to the data connection. Common functions like if, Collect/ClearCollect are not delegatable in Sharepoint, while they are in other data sources like Dataverse. This means that if you need to return more than 500-2,000 records at a time, it is very difficult to do so with Sharepoint lists. With other connections like Microsoft Dataverse, Collect is delegatable, allowing for a much higher number of records.
  3. Security: if you share an app built on SharePoint with a user, you also need to share the underlying lists with them. This means if the user should not be able to see all of the records in the table (or update all of the records in the table), they could open up the underlying list and view or modify any of the records, even if you are obscuring the records inside of your app. Sure you can hide the list, but obscurity != security.
  4. SharePoint lists have limits in how they can be filtered in Power Apps: for example, filtering on yes/no fields does not work. Microsoft Dataverse based apps support complex filtering via views, which can include multiple tables.
  5. If your app exceeds the capabilities of SharePoint Lists, you need to start over—many apps begin as personal productivity but over time become more critical to an organization. Before you build your app, think through the potential roadmap—if there is a potential that it will increase in data, security sensitivity, or number of users in the future, build it on something other than SharePoint lists.

What is the alternative?

If you want to start with the benefits and ease of use of Microsoft lists without having to purchase premium licenses, start with Dataverse for Teams. Dataverse for Teams is included in the standard licensing for Office and Microsoft Teams.

In Microsoft Teams, click Apps and search for Power Apps, then click Open

Click New App

Select the name of the Team where you want the app to be used, then click Create

This will open the Power Apps Studio inside of Microsoft Teams.

Give your app a name

Click Create new table

Give your table a name

Define your columns in a Microsoft List like experience.

Dataverse for teams table creation has all of the things that are great about Microsoft Lists, without the major downsides.

  • It is included in the standard licensing
  • It’s easy to use to create and modify tables
  • It’s a real relational database—if you choose lookup for field type, it creates a real database relationship between the two tables
  • It supports more advanced delegation and filtering.
  • It supports real record ownership and security. You can specify different table permissions for users based on their role in the Team

It supports more advanced filtering via Dataverse views

  • If you exceed the capabilities of Dataverse for Teams you can upgrade your environment to a full Dataverse environment without having to rebuild your app.

Limitations of Dataverse for Teams

Dataverse for Teams environments are limited to 2 GB of data. For larger environments with more complex security requirements, I recommend Microsoft Dataverse. For a full comparison of Dataverse for Teams vs. Dataverse, see Dataverse for Teams vs. Dataverse – Power Apps | Microsoft Docs.

Thanks to Matt Devaney for contributing ideas to this tip.

Cover photo by Donald Giannatti on Unsplash.

Tip #1401: Round time in Power Automate

Today’s conversation:

- Hey, wanna see cool way to round time in Power Automate?
- Sure
- *shows the trick*
- Why it's not a tip yet?
- Mmmmm, dunno...

True story.

Sometimes we need to round time in Power Automate. For example, you want to schedule a meeting for tomorrow, at the same time as now. Except “now” could be 11:28 AM and everyone loves meetings scheduled for random times that do not align on 30 minutes.

The trick is to use the simple fact that there is no such thing as date/time data type in Power Automate, it’s all text in a prescribed format. This is the magic expression:

concat(substring(utcNow(),0,14),'00:00Z')

What does it do? It strips the minutes and seconds portion of the string by selecting just the date part and hours and then adds 00:00 as minutes:seconds (or you can add 30:00 if you want to be fancy).

‘Z’ is for ‘Ze UTC Time’, if you must know.

That’s it! Occam’s razor for the win.

Tip #1400: Excluding inactive records from duplicate detection

It’s good to be reminded every now and then about the features that have been around for a long time and are documented but somehow any question from the business about them draws a blank and ubiquitous “I have to get back to you”.

Today’s reminder is from Ankita Chavan. (Do you use a feature all the time? Email a reminder to jar@crmtipoftheday.com)

When we merge the two records, one record is a master record (active record) and other record gets deactivated but still present in the system.
In case you make changes to the active record, you will get below alert due to OOB duplicate detection rule set for the table (entities).

In case you do not want this alert, follow below steps – Unpublish the rule and mark “Exclude Inactive matching records” and Publish.

Official docs: Set up duplicate detection rules to keep your data clean – Power Platform | Microsoft Docs