Data Export Service and Versioning of Azure Key Vault URLs

The Microsoft Data Export Service (DES) is a well-documented integration used to replicate CRM data to an external Azure SQL database. Even with its easy-to-use design, there are some limitations about what can be modified after the DES profile is created. One such example is the key vault secret URL.

When the initial profile is created and the key vault string has been input, it can no longer be changed without recreating the profile (which, by relation, requires the connected SQL tables to be dropped in order to start the sync again). If the key vault URL is copied directly, this will leave a static un-editable connection string. However, one small change to the DES profile set up will allow for the secret to be updated without needing to recreate the profile. Here’s how it works:

A standard key vault secret URL is formatted as such:

Once a key vault secret is created, the secret is locked and is unable to be edited – instead, it must be versioned. Azure Key Vault allows for secret versioning with the ability to have multiple versions enabled for the same vault at one time. The primary issue with simply versioning the secret is the new version changes the GUID, which means the URL that was entered in the profile is still pointing at the old secret. In the example below, the GUID in the secret (A) directly corresponds to the GUID on the end of the secret (B). This prevents standard operational updates to the connection string, such as password renewals.

To account for this, simply remove the GUID at the end of the secret URL. This will continue to point at the newest version of the secret, no matter how many times the secret is changed. This little trick can save several hours of work in case a change to the secret is ever needed in the future. Be sure to subscribe to our blog for more tips and tricks.

Happy Dynamics 365'ing!

Data Export Service Installation and Configuration

Are you as excited as we are about the Data Export Service (DES) capabilities available when working with Azure with Dynamics 365 for Customer Engagement (D365 CE) Online? In this blog we’ll look at installing and configuring the DES to export data from D365 CE Online to a SQL database. Let’s get started!

Install and Configure Data Export Service

Gather Credentials

Install Data Export Service

Verify that DES Export is installed on the D365 instance containing the data that you need to export.

1. Select Get more apps

2. Search for “Data Export Service”

3. Select Get it now

data export service

4. Fill in your information when prompted:

5. Input the name of the organization/instance to add it to and select Agree

Nice work! While that’s running, let’s move on to the Azure portal.

Create/Identify Azure Key Vault & Key

1. Login to the Azure Portal

2. Type “key vault” in the top search box

3. Select Key vaults

4. If this is the first Key Vault, your screen will look similar to this:

5. Select Add > Create key vault:

6. Enter the required information:

7. Click Create

8. Open the key vault

9. Select Keys:

10. Select Generate/Import:

11. Name the new key and click Create.

12. View the newly generated key:

13. Open the new key and select the record shown in the CURRENT VERSION area:

14. Copy the Key Identifier for reference later in this blogpost:

Create a Secret in the Azure Key Vault

If you’re a fan of PowerShell, check out this article: Quickstart: Set and retrieve a secret from Azure Key Vault using PowerShell. Or, follow these steps to work through the Azure GUI.

1. Log into the Azure portal with organization user credentials and navigate to your key vault.

2. Select Generate/Import and enter the information required:

In our example connecting to a SQL database, the secret Value looked something like this:

Depending on what you are connecting to, your solution may require a different Value format than the example shown here. With the multitude of options to choose from, the connection string specifics for each connection type is out of scope for this blogpost. See https://docs.microsoft.com/en-us/azure/key-vault/quick-create-template or search for ‘How To Configure Data Export Service with WhatYouAreConnectingTo’ to help you along your way for a use case other than SQL.

3. Select Create.

4. Open the newly generated secret and select Current Version to view the details.

5. Add a Tag to the secret

6. Copy the secret identifier value for reference later in this blog. In our example the secret identifier value looks something like this:

Register the Data Export Service Application

1. Navigate to Azure Active Directory > App Registrations

2. Locate and select the application Data Export Service for Microsoft Dynamics 365 (If it hasn’t been installed into D365 CE yet, go back and do that now.)

3. Enter a sign-on URL within your organization’s domain

4. Next, open the Data Export Service for Microsoft Dynamics 365 app

5. Go to Settings > Required Permissions > Add

6. Select an API

7. Select Azure Key Vault > Permissions

8. Select Delegated Permissions and Have Full Access

Configure the Access Policy for the D365 Organization in Azure

1. Log into the Azure portal with organization user credentials and navigate to your key vault.

2. Open the Key Vault to create an Access Policy for it. Select Access Policies > Add New

3. Search or select Data Export Service for Microsoft Dynamics 365.

Enable Change Tracking on Entities in D365 CE

Ensure that change tracking has been turned on in D365 CE for the entities that will be exported via DES. Keep in mind it must be done manually in each instance, as it does not migrate with solutions.

Tip! Keep the amount of entities with change tracking enabled to a minimum to avoid unnecessary overhead.

1. Inside of the D365 CE Customization area, navigate to the Entity Definition area to select “Change Tracking”

2. Save and Publish the changes

Create the D365 CE Data Export Profile

1. Log in D365 CE with administrator credentials

2. Navigate to Settings > Data Export. (This is the DES that was installed from step one solution import.)

3. Create a new data profile

4. Enter the parameters requested

Name: ReportingExport (Or, another name that makes sense for your purpose)

Key Vault URL: This is the Secret Identifier Value copied during the steps above or provided by your Azure administrator. It looks something like this:

https://organizationdes-keyvault01.vault.azure.net:443/secrets/OrganizaitonDESDatabaseConnectionString/12345c75935464645555a0d22bcf7369c7a4

(Reminder: Don’t use what you see here. This sample includes an invalid GUID and is solely for reference purposes. You’ll need the value from your Azure tenant.)

Schema: yourdataschemaname

Prefix: yourpreferredprefix

Retry Count: Default 12

Retry Interval (in sec.): Default 5

Write Delete Log: True

5. Select Validate

6. Select the entities to be included/excluded for this profile

7. Select any relationships to be included/excluded for this profile

8. Review the Summary to ensure everything is as you wish

9. Select Create & Activate

10. Click REFRESH.

11. View the status of the profile components

Congratulations! You did it!

Reminders

Additional Information

Happy Dynamics 365’ing!

Data Export Service: How to Handle Lookup Columns

In this blog, we will uncover how primary fields and look up fields are created and synchronized in the Microsoft Azure SQL Database, in a customer-owned Microsoft Azure subscription using Data Export Service.

Unlike Dynamics 365 On-Premise installations where developers and administrators have some level of control on backend databases and can facilitate an organization's reporting with ETL teams to query data directly from a SQL server for reporting, a Dynamics 365 Online implementation does not allow users to query a CRM database directly in online environments. To overcome this challenge, Microsoft has come up with a Data Export add-on service available on Microsoft AppSource that adds the ability replicate Dynamics 365 data to an Azure SQL Database. This helps facilitate reporting and allows ETL teams to consume data for reporting and analytic purposes.

The Data Export Service add-on transforms data columns while replicating data from Dynamics 365 to an Azure SQL database. Here are a few points that need to be considered:

Get Lookup Values from the Data Export Service Database

The Data Export Service synchronizes lookup fields slightly differently in the Azure SQL database tables than in the same columns created by the Dynamics 365 on-premises database.

Data Export Service creates two columns for each lookup in Dynamics 365 as opposed to three columns in the on-premises database.

  1. <Lookupfield>ID - This column is similar to the id column created by Dynamics 365 in on-premises databases. It holds the GUID of the primary id of the referenced table
  2. <Lookupfield>_entitytype - This column holds the entity schema name of the referenced entity. This is different from Dynamics 365 on-premises database, where the database is used to store the referenced entity's ObjectTypecode in <Lookupfield>Type column.
  3. <Lookupfield>name: Data Export Service never copies the 'name' column in the Azure SQL Database; so it is always empty for every lookup field.

Example: Refer to the screenshot below to compare the differences between the same CustomerId look up field created by Data Export Service in Azure SQL Database vs. the on-premises Database created by Dynamics 365.

Dynamics 365 On-Premises Database:

Data Export Service

Azure SQL Database Look up field created by Data Export Service:

Data Export Service

TargetMetadata Reference Tables for Lookup

The Data Export Service creates a TargetMetadata master table to hold all the referenced entities and attributes for lookup fields. Each row represents the lookup between the Primary (Referencing) entity and the referencing attribute to Referenced entity and attribute.

Example: The screenshot below shows all the referenced look ups for the contact entity. Here the CreatedBy on contact table is referencing the system user table's systemuserid attribute.

Data Export Service

The Data Export Service is only available for Microsoft Dynamics 365 Online, version 8.0 and onwards.

Learn more about Microsoft Dynamics 365 in the PowerObjects blog!

Happy Dynamics 365'ing!