Tip #1414: Enforce unique email but ignore inactive records

Alternate keys are great way to enforce uniqueness of the column values. For example, if you want lead emails to be truly unique, just add an alternate key and no user will be able to bypass that (unlike the duplicate detection which is a fairly timid mechanism). The challenge is when you want that uniqueness to apply only to active records. Bear with me.

You thought that lead was lost forever and deactivated the record (you do not ever delete important records, right?). Now they are coming back, rejuvenated and refreshed and ready to buy not one but seven of your wonderful Jigamagigs. New lead is created but with the same email and that’s OK – we don’t hold grudges. What you don’t want is two active leads with the identical emails.

Alternate key using email are not going to work for you in this scenario, but you can enforce the uniqueness using a faux column that contains email for active records and null for inactive.

It works because alternate keys ignore null values.

The walkthrough below uses the contact table.

  1. Create a column large enough to hold email addresses. No need to make it searchable or enable audit.
    Screenshot of a new column properties. Column is called "I Am Unique" with the length of 250 characters.
  2. Create new real-time workflow (and you can do that from the new solution explorer)
    Screenshot of the expanded NEW menu in the new solution explorer. Submenus are opened as New srcset= Automation > Process > Workflow”>
    New workflow properties. DIsplay name is "Unique Email", table is contact, checkbox "Run workflow in the background" is cleared
  3. Set the workflow properties
    Screenshot of workflow properties as described
    • Check As an on-demand process if you have existing records in your Dataverse instance so that you can run it once to populate the values.
    • Start after Record is created, Record status changes, and Record fields change.
    • Select emailaddress1 as the field to trigger.
    • Set the workflow logic: If contact status is Active, set faux column created in Step 1 to emailaddress1 value. Otherwise clear the column.
  4. Create alternate key over the faux column
    Screenshot of an alternate key record that contains only I Am Unique column
  5. Run the workflow over all existing contact records to populate. Note: it may fail if your table contains active duplicates. Check for failed workflows, resolve the duplicates, run again.

Now, when a user tries to create a record with a duplicate email address they will see the error.

A screenshot of a sample error message with a caption "Business Process Error" and the message "A record that has the attribute values I Am Unique already exists. The entity key Unique Email requires that this set of attributes contains unique values. Select unique values and try again."

But users will be able to create the duplicates if only one of the records is active.

List of contacts where two of them have the same email address but one of them is inactive.

The good thing about this method is that it’s bulletproof against clever users importing via Excel online, sneaky developers updating using SDK, and even almighty administrators activating the existing records that contain the duplicates.

This tip wouldn’t be possible without my family, my parents, my sponsors, and David Yack who provided a sanity check where I needed it most.

Cover photo by Rupert Britton on Unsplash

Tip #1413: Components Required in the Solution for Relationship Mapping Deployment

Today’s winner is Linn Zaw “I always” Win. And you can be one too if you email your tip to jar@crmtipoftheday.com!

Have you ever added the 1:N or N:1 relationship into the solution, created new mappings in the solution and deployment the solution to another environment only to find out that the mappings are not deployed? Upon opening the customizations.xml of the solution, you might notice that the newly created mappings are not included under the <EntityMaps /> tag.

These are the components required in the solution to include the relationship mapping.

  1. Relationship which contains the mapping (obviously)
  2. Both Primary and Related tables of the relationship
  3. Target column of the mapping

Relationship

Screenshot of the classic interface for Relationship definition. Information tab is selected. The following screen elements are numbered: 
number 1 - Relationship name
number 2 - Primary Entity name
also number 2 - Related Entity name

Mapping

Screenshot of the classic interface for Relationship definition. Mappings tab is selected. The following screen elements are numbered: 
number 3 - row containing the mapping between source and target columns

Tables and target column

Screenshot of the classic interface for the solution components. Components > Entities node is expanded in the tree, Fields node under the target entity name is selected.
The following screen elements are highlighted and numbered: 
number 2 - Primary Entity and Related entities under Entities node in the tree.
number 3 - mapped target field selected in the list view in the right part of the screen.

Cover photo by Davies Designs Studio on Unsplash

Tip #1412: Changing the position of the WYSWYG editor buttons

It’s spring time in Australia and our tipsters are coming back! Elaiza Benitez is here (at least that’s what her Twitter handle says). And you can be here too – just email jar@crmtipoftheday.com with your brilliant suggestion and we’ll take care of the rest.

For those who have been long time makers with Dynamics 365, you’ll know that the WYSWYG editor buttons for email activities, email templates and knowledge base articles have always been positioned at the top.

When the unified interface came along for model-driven apps improvements were made. The Notes in the timeline has a WYSWYG editor now and if you enable the Rich Text Editor custom control on a multi-line text field the WYSWYG editor appears at the bottom. This is inconsistent as based on what you record you are creating in the model-driven app, it will either show at the top or the bottom.

Screenshot of the notes editor with the toolbar at the bottom. The Notes in the timeline has a WYSWYG editor now and if you enable the Rich Text Editor custom control on a multi-line text field the WYSWYG editor appears at the bottom.

For consistency purposes you can change the positioning of the WYSWYG editor as it’s controlled by a web resource. Simply create a new solution and add the RTEGlobalConfiguration.json web resource. As per the documentation, edit the web resource by updating the toolbarLocation property from bottom to top. Save and publish your changes.

Voila! The WYSWYG editor buttons will now display at the top for Notes.

Screenshot of the notes editor with the toolbar moved to the top.

If you also have fields where the Rich Text Editor custom control is enabled, it will appear at the top as well.


Screenshot of the Rich Text Editor custom control in the standard account form with the toolbar at the top.

Credit to our XrmToolbox creator, Tanguy Touzard, for this tip!

😳 WHAT? TANGUY? WAIT A MINUTE?! WHO’S THE TIPSTER HERE? Oh, well, I’ll leave it here as a joint effort including of course myself. – t.j.

Cover photo by Christopher Machicoane-Hurtaud on Unsplash

Tip #1411: Power Apps portal vulnerability checks using PowerShell

I’ve created couple scripts that quickly check if your Power Apps portal has a potential vulnerability in the portals I look after.

OData Endpoint Probe

This one is quite trivial and simply probes the OData endpoints exposed by the portal. Takes portal url as a parameter and lists all OData endpoints. Endpoint is marked as a suspect if it can be accessed.

Dataverse Probe

This one is a tad smarter and longer. It prompts to connect to Dataverse, grabs the first portal, and checks lists and forms to see if any of them have table permissions switched off. If permissions are on and endpoint is not accessible, we are good. If permissions are on and endpoint is open it’s a potential leak. If permissions are off and there is an open OData endpoint, this is bad.

For forms it’s black and white: script only lists the forms where table permissions are off.

Consider the chapter about Power Apps portals data leak closed.

Tip #1410: 50 Shades of Regex

I usually don’t drool over code but this one is just way too elegant for me not to. StackEdit – In-browser Markdown editor is an awesome app for markdown editing. One of the standout features is ability to comment and review – something sorely missing from the standard markdown. StackEdit very cleverly serializes reviews and notes as a base64 string and inserts it at the end of the document using HTML comment <!– –>. As part of the exercise this comment is saved very tidily in a 50 char wide column. So how does one wrap a string at a fixed width? This is the genius line:

const serializedData = 
  utils.encodeBase64(JSON.stringify(data))
    .replace(/(.{50})/g, '$1\n');

What does it do?

  • utils.encodeBase64(JSON.stringify(data)) – take our data object, serialize it to string, then convert to base64 to make sure it’s printable
  • replace – that’s just replace one thing with another
  • (.{50}) – a group of 50 characters
  • /g – global, i.e. do not stop after the match
  • '$1\n' – replace those groups of 50 characters with themselves plus newlines

Is that a brilliant piece of code or what? 🤤

Cover photo by Ono Kosuki from Pexels

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