Tag: data

Data Export Service Connection Issues

This is a slightly different post from the usually stuff that I talk about. It’s much more ‘techy/developer’ focused, but I thought it would be quite useful still for people to keep in mind.

The background to this comes from a project that I’ve been working on with some colleagues. Part of the project involves setting up an Azure SQL database, and replicating CDS data to it. Why, I hear you ask? Well, there are some downstream systems that may be heavy users of the data, and as we well know, CDS isn’t specifically build to handle a large number of queries against it. In fact, if you start hammering the CDS layer, Microsoft is likely to reach out to ask what exactly you’re trying to do!

Therefore (as most people would do), we’re putting in database layer/s within Azure to handle the volume of data requests that we’re expecting to occur.

Azure SQL Database | Microsoft Azure

So with setting up things like databases, we need to create the name for them, along with access credentials. All regular ‘run of the mill’ stuff – no surprises there. In order for adequate security, we usually use one of a handful of password generators that we keep to hand. These have many advantages to them, such as ensuring that it’s not something we (as humans) are dreaming up, that might be easier to be guessed at. I’ve used password generators over the years for many different professional & personal projects, and they really are quite good overall.

Sordum Random Password Generator Creates Random Passwords with Ease - MajorGeeks
Example of a password generation tool

Once we had the credentials & everything set up, we then logged in (using SQL Server Management Studio), and all was good. Everything that we needed was in place, and it was looking superb (from the front end, at least).

OK – on to getting the data actually loaded in. To do this, we’re using the Data Export Service (see https://docs.microsoft.com/en-us/power-platform/admin/replicate-data-microsoft-azure-sql-database for further information around this). The reason for using this is that the Data Export Service intelligently synchronises the entire database initially, and thereafter synchronises on a continuous basis as changes occur (delta changes) in the system. This is really good, and means we don’t need to build anything custom to handle it. Wonderful!

Setting up the Data Export Service takes a little bit of time. I’m not going to go into the details of how to set it up – instead there’s a wonderful walkthrough by the AMAZING Scott Durow at http://develop1.net/public/post/2016/12/09/Dynamic365-Data-Export-Service. Go take a look at it if you’re needing to find out how to do it.

So we were going through the process. Part of this is needing to copy the Azure connection string into into a script that you run. When you do this, you need to re-insert the password (as Azure doesn’t include it in the string). For our purposes (as we had generated this), we copied/pasted the password, and ran things.

However all we were getting was a red star, and the error message ‘Unable to validate profile’.

As you’d expect, this was HIGHLY frustrating. We started to dig down to see what actual error log/s were available (with hopefully more information on them), but didn’t make much progress there. We logged in through the front end again – yes, no problems there, all was working fine. Back to the Export Service & scripts, but again the error. As you can imagine, we weren’t very positive about this, and were really trying to find out what could possibly be causing this. Was it a system error? Was there something that we had forgotten to do, somewhere, during the initial setup process?

It’s at these sorts of times that self-doubt can start to creep in. Did we miss something small & minor, but that was actually really important? We went over the deployment steps again & again. Each time, we couldn’t find anything that we had missed out. It was getting absolutely exasperating!

Finally, after much trial & error, we narrowed the issue down to one source. It’s something we hadn’t really expected, but had indeed caused all of this to happen!

What happened was that the password that we had auto-generated had a semi-colon (‘;’) in it. In & of itself, that’s not an issue (usually). As we had seen, we were able to log into SSMS (the ‘front-end’) successfully, with no issues at all.

However when put into code, Azure treats the semi-colon as a special character (a command separator). It was therefore not recognising the entire password, which was causing the entire thing to fail! To resolve this was simple – we regenerated the password to ensure that it didn’t include a semi-colon character within it!

Now, this is indeed something that’s quite simple, and should be at the core of programming knowledge. Most password generators will have an option to avoid this happening, but not all password generators have this. Unfortunately we had fallen subject to this, but thankfully all was resolved in the end.

The setup then carried on successfully, and we were able (after all of the effort above) to achieve what we had set out to do initially.

Have you ever had a similar issue? Either with passwords, or where something worked through a front-end system, but not in code? Drop a comment below – I’d love to hear!

Omnichannel – Data Masking Rules

There are various scenarios in which companies would be quite keen to utilise data masking rules. Examples of these include:

  • Masking credit card details, so that firstly the company isn’t required to comply with credit card handling information requirements, and secondly (and potentially more importantly!) there’s no risk of an agent copying down a credit card number, and using it fraudulently
  • Masking personal information – if a customer mistakenly types in their Social Security Number, UK Tax Reference, etc, then the company is likely to want to avoid having their support agents seeing this sensitive information
  • Socially offensive language – companies will be extremely keen to avoid having their staff exposed to offensive language and behaviour

There are obviously other examples of these as well – you can feel free to let your mind run free as to the possibilities.

Omnichannel for Dynamics includes what are referred to as ‘Data Masking Rules’. Using these, you can create rule/s that will then be used to identify the undesired words (or other types of data) within a conversation, and these will then be automatically masked with the asterisk (*) character. Data masking works for chat and async channels.

Now, a few things to keep in mind. Data masking is done through the use of regular expressions, also know as ‘regex’ (when I heard this, I needed to go and look up what a ‘regular expression’ is, as I had no idea! If you have no idea as well, take a look at https://en.wikipedia.org/wiki/Regular_expression , which has a good summary of things).

Currently, there is a HARD LIMIT of 10 data masking rules. Yes, you read that correctly. You are ONLY able to have 10 rules saved. However if you play cool, there’s a way around it, thanks to the way that regex expressions can work.

So, let’s look at how to set up and configure these data masking rules. As with practically everything else that’s set up in Omnichannel, you’ll need to be in the Omnichannel Administration Hub. Scroll down in the left hand menu, and you’ll find it under the Settings section:

Click on it to open, and you’ll presented with a slightly different looking screen than you’re used to. It’s not a general view/list of records – it’s a static screen, with a grid of rules on the right:

The two settings on the left hand of the screen are really quite important. They are:

  • ‘Mask private agent data from the customer’. What this does is mask data that the agent is sending, for both the agent and the customer (ie the agent won’t be able to see the data either, even though they’re typing it). This applies for both live chat and async channel messages.
  • ‘Mask private customer data from the agent’. This will mask data that the customer is sending to the agent, for both the customer and the agent. This is only masked for live chats for both; when using async channels, it’s only masked for the agent interface (ie the customer will still be able to see the data)

Note: If only the first option is enabled, then if the customer types in data matching the masking rule, it will not be hidden, and vice-versa for the second option. It’s therefore vitally important to consider all of the relevant scenarios for the company, and apply these settings appropriately.

Note: Data masking isn’t just through the chat interfaces – it’s also how the data is stored. So if you open up a transcript, or get to check the data within the database, it’s also masked there, which means that it’ll allow you to be fully compliant with everything! A side effect of this is that the sentiment analysis

There are 3 rules provided for you out of the box (in an inactive state). They are:

  • Credit Card: Masks the credit card number, if provided in a message.
  • Email: Masks the email address, if provided in a message.
  • SSN: Masks the SSN, if provided in a message.

Opening up one of these shows us how Microsoft is implementing these:

There’s the name (which you can put whatever you want to), and a description (which is always helpful and useful!). Then there’s the regular expression (I’m not going to go into details as to how to actually put these together – there are plenty of resources out there. Take a look at https://docs.microsoft.com/en-us/dotnet/standard/base-types/regular-expression-language-quick-reference as a starting point). The character used for masking can’t be changed (at this point in time – perhaps in the future they’ll allow this to be changed).

The really great thing from my perspective is the testing area on the right hand side of the form. Here you can input your text, and see if it actually matches the conditions for the regex (or not, as the case may be). This will allow tweaking of the regex etc:

Just please be aware that you need to click or tab out of the ‘enter test data’ field in order for the ‘masked test data’ value to update

Once you have a rule in place, save it, and click ‘Activate’! Otherwise the rule will be saved, but won’t actually work!

Now, remember that I mentioned above about the limit of only TEN data masking rules? Well, here’s a great little tip as to how to work around this, as there are many legitimate examples of needing many more than ten!

So how do you go about doing this? Well, it goes something like this:

  1. The ‘Regular Expression’ field is actually of type ‘Text’ (with it being ‘Single line of text’). This means that it can actually hold up to 4000 characters in it (according to https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/customize/types-of-fields )
  2. There’s a cute little character that exists – this is the ‘|’ character (also known as the pipe character)
  3. Using the pipe character, you can separate regex expressions, which will be evaluated separately
  4. Therefore you can ACTUALLY have multiple regex expressions in a single data masking rule!

Let’s see this is set up!

And now to see it through the actual interface:

So with this, though it might take a bit of time and testing (and double-checking, to make sure it’s working absolutely correctly, of course), it’s possible to have quite a lot of regex expressions for you to use!