Getting to Know Dynamics 365 Data Export Service

Getting to Know Dynamics 365 Data Export Service

Dynamics 365 has an exciting new engagement online feature for customers with in-house SQL Server experience called the Data Export Service. Formerly known as ‘Microsoft Dynamics CRM’, this service is applicable to the following applications:

• Microsoft Dynamics 365 for Sales
• Microsoft Dynamics 365 for Customer Service
• Microsoft Dynamics 365 for Project Automation
• Microsoft Dynamics 365 for Field Service
• Microsoft Dynamics 365 Customer Engagement

For simplicity, throughout the remainder of this article, I will be referring the above collectively as “CRM” or “CRM Online”.

Ultimately, the Data Export Service can be used to replicate CRM data into a SQL server for further querying. Not all CRM data has to be replicated, so to save on costs, only the desired entities may be exported, for example.

Why the New Feature?

One of the selling points of moving to the cloud is the reduction in time and technical resources needed to maintain a system. CRM Online doesn’t require a SQL Server anywhere to run, it uses a database behind the scenes. This reduces the burden on customers to maintain those servers. Microsoft’s data center teams will make sure both the web app and database servers stay updated, tuned up, and backed up– All the things that in-house IT would normally have to maintain.

The downside to not having a SQL Server to maintain is that you don’t have a SQL Server at all! Many customers who have in-house SQL expertise would be unable to leverage that expertise to make advanced reporting scenarios a breeze. CRM Online does, of course, have other ways of querying and analyzing and reporting on data (such as the REST API, or FetchXML), but CRM Online does not permit direct SQL access. Even though SSRS reports can be uploaded, they cannot have SQL data sources, they must instead rely on FetchXML queries, which introduces a new learning curve instead of leveraging that existing expertise.

The Data Export Service essentially allows replicating the CRM data into an Azure SQL Server instance for querying, thereby alleviating the issue of having to learn a new technology just to perform the same old queries.

(Note for customers already familiar with the On-Premises CRM SQL Database schema, the table schema implemented by the Data Export Service is similar, but different. The “Filtered” Views are also unavailable. Existing queries and SSRS reports would have to be adjusted to compensate for these differences.)

How it Works?

This section of the article will explain in detail how to start using the Data Export Service, some steps will be quite technical so we recommend this post for the more advanced users and administrators of CRM/SQL.

General Prerequisites

To start using the Data Export Service there are a few prerequisites to check first. The complete list can be found on MSDN, but essentially the following things should be verified:

To summarize:

1. The CRM instance must be using Dynamics 365 (online) with at least the December 2016 update.
2. The Data Export Service must have a SQL Server instance either from Azure SQL or an Azure Virtual Machine with SQL provisioned. (Setting that up is outside the scope of this article.)
3. The setup user must be a Dynamics 365 System Administrator.
4. The entities to export must have the Data Service “Change Tracking” enabled. Out-of-the-box entities already have this enabled by default, custom entities must have that feature enabled.
5. Global / Tenant Admin access, or an Azure Key Vault must be provisioned and the setup user must have permissions on Secrets.

What is the KeyVault?

Essentially, the Azure KeyVault will be used to store the connection string for the SQL database. This is plain text, and will contain the username and password of the database user. Because this is very sensitive information, and could allow anyone access to the data, a secure online storage solution (the Azure KeyVault) is used to secure the information. The permission model will essentially be as follows: The service principal user (the Data Export Service, essentially) will have access to the “Secret” in the KeyVault, and will be able to use the “Secret” to connect to the database in order to replicate the CRM data.

Prerequisites for configuring the KeyVault

Azure PowerShell

First make sure Azure PowerShell is installed on the local system. To install and verify the Azure PowerShell cmdlets, follow the instructions here.

To successfully run “Login-AzureRmAccount” the Execution Policy may have to be relaxed via the PowerShell cmdlet “Set-ExecutionPolicy RemoteSigned”, then the AzureRM Module must be imported via the PowerShell cmdlet “Import-Module AzureRM”.

Register the Microsoft.KeyVault Namespace

Secondly, make sure the Azure subscription is registered to use the Microsoft.KeyVault namespace by following the instructions found here.

Provisioning Script for the Key Vault

The Script

The following script is used to provision and configure an Azure Key Vault. See the next section for steps on how to identify the requisite IDs and names.

Another, similar, version (in case there is difficulty copying and pasting from here) is available from the Data Export solution in CRM (skip ahead to section 1.4), simply click the blue “i” icon next to the “Key Vault URL”, as shown below:

#The below PowerShell script enables you to provision a secret in KeyVault. Please execute this from your PowerShell command line after replacing the placeholders below.

# ----PLACEHOLDER------------------------------------------------------------------ #
$subscriptionId = '00000000-0000-0000-0000-000000000000'
$keyvaultName = '<Unique Key Vault Name>'
$secretName = 'DESDatabaseConnectionString'
$resourceGroupName = '<resource group name>'
$location = '<resource group location>'
$connectionString = 'Server=<database uri>;Database=<database name>;User Id=<username>;Password=<password>;'
$organizationIdList = '00000000-0000-0000-0000-000000000000'
$tenantId = '00000000-0000-0000-0000-000000000000'
# -------------------------------------------------------------------------------- #

# Login to Azure account, select Subscription and tenant Id
Login-AzureRmAccount
Set-AzureRmCntext -TenantId $tenantId -SubscriptionId $subscriptionId
 
# Create new resource group if not exists.
$rgAvail = Get-AzureRmResourceGroup -Name $resourceGroupName -Location $location -ErrorAction SilentlyContinue
if(!$rgAvail){
New-AzureRmResourceGroup -Name $resourceGroupName -Location $location
}

# Create new key vault if not exists.
$kvAvail = Get-AzureRmKeyVault -VaultName $keyvaultName -ResourceGroupName $resourceGroupName -ErrorAction SilentlyContinue
if(!$kvAvail){
New-AzureRmKeyVault -VaultName $keyvaultName -ResourceGroupName $resourceGroupName -Location $location
# Wait few seconds for DNS entry to propagate
Start-Sleep -Seconds 15
}

# Create tags to store allowed set of Organizations.
$secretTags = @{}
foreach ($orgId in $organizationIdList.Split(',')) {
$secretTags.Add($orgId.Trim(), $tenantId)
}

# Add or update a secret to key vault.
$secretVaule = ConvertTo-SecureString $connectionString -AsPlainText -Force
$secret = Set-AzureKeyVaultSecret -VaultName $keyvaultName -Name $secretName -SecretValue $secretVaule -Tags $secretTags
# Authorize application to access key vault.
$servicePrincipal = 'b861dbcc-a7ef-4219-a005-0e4de4ea7dcf'
Set-AzureRmKeyVaultAccessPolicy -VaultName $keyvaultName -ServicePrincipalName $servicePrincipal -PermissionsToSecrets get

# Display secret url.
Write-Host "Connection Key Vault URL is " $secret.id

The Variables

$subscriptionId

The subscription id is that of your Azure subscription. This information can be found by visiting the Azure Portal and clicking on “Resource Groups”, select the resource group containing the Azure SQL Server or Azure VM with SQL Server and look at the overview to find the Subscription ID of that Resource Group, as shown below:

$keyvaultName

The name of the Key Vault service must be globally unique. To ensure this is the case, use the organization’s abbreviated name or acronym as a prefix, for example we might choose to use “CRGDESKeyVault”, to be short for “Corporate Renaissance Group’s Data Export Service Key Vault”.

If the chosen name is not unique, then the script will throw an error such as “this name is already in use”. Simply try using a different name and run the script again.

$secretName

The KeyVault stores Secrets and Keys and needs unique names to act as identifiers to reference the specific secret. It is recommended to use a descriptive name such as “DESDatabaseConnectionString”. The reason this is secret is that the connection string will contain the database username and password and must be kept confidential.

$resourceGroupName

Refer to the steps used to determine the Subscription ID, simply note the name of the Resource Group.

$location

Refer to the steps used to determine the Subscription ID or Resource Group name. Note which location is set for most of the resources, as shown below:

$connectionString

The connection string will be in the format:

“Server=<database uri>;Database=<database name>;User Id=<username>;Password=<password>;”

Replace the “<data base uri>” with the URL of the database, the “<username>” and “<password>” with the credentials for the database user.

$organizationIdList

This is the ID of the Dynamics 365 Organization. This can be found by going to Settings > Customizations, as shown below:

Then click on the “Developer Resources” tile, as shown below:

The ID can be found in the “Instance Reference” section, as shown below:

$tenantId

The Tenant ID is the ID of the tenant used to host the Dynamics 365 instance. This ID will be printed as the result of successfully logging into Azure via the Azure PowerShell cmdlet “Login-AzureRmAccount”.

Running the Script and Note the Secret URL

Once all the variables have been entered into the “PLACEHOLDER” section of the script, copy and paste it into PowerShell to execute it. Make sure there is an extra newline at the end, or simply press “Enter” once the Script reaches the final line if it does not execute automatically.

The final line “Write-Host “Connection Key Vault URL is ” $secret.id” will print out a URL in the form: “https://<key vault name>.vault.azure.net:443/secrets/<secret name>/<version id>”.

Copy and paste this somewhere for safe keeping. It will be needed in the next steps.

Installing the Data Export Service Solution

Unlike many of the Dynamics 365 add-ins, the Data Export Service solution is deployed through the Dynamics 365 Marketplace, also known as AppSource. To open the marketplace, click on the navigation tile shown below:

AppSource appears as an “app” repository for Dynamics 365 apps, as shown below:

To find the Data Export Service, simply type “Data Export Service” into the search box, then click “Get it now” on the app, as shown below:

Accept the terms of use and follow the on-screen prompts to install the solution.

Configuring the Export Profile

Once the solution is installed, reload Dynamics 365 (F5 or Ctrl + R) to refresh the Navigation panel. Then go to Settings > Data Export as shown below:

This is the view of Data Export Profiles, we will need to create one now by clicking “Add” as shown below:

In the pop-up wizard, as shown below, enter the information requested.

• The “Name” is simply to identify the Export Profile, any will suffice.
• The “Key Vault URL” should be the value the Key Vault provisioning script returned.
• The “Schema” can be left as default”
• The “Prefix” will be used to prepend the table names in SQL. Leave it blank for no prefix, or enter something such as “crm” to make sure it’s 100% clear where the data is coming from in SQL.

The rest can be left as-is. Click “Validate” to make sure the Secret is working. If there is an issue, the error message will indicate what could be the matter:

• If it says there are no tags, go into Azure and manually re-configure the Secret.
• If it says the server in the URI cannot be connected to, the Connection String may be incorrect. The Secret must be updated once the correct Connection String has been created.

Once the Validation is successful, click “Next” to select which entities to export. It is possible to choose only a small subset of entities to keep SQL clean and small (e.g. if not all the Activities are needed, do not export them and bloat the SQL database).

On the next page, select any M:N relationships to export. 1:N relationships will not appear here as they are simply GUIDs in columns. M:N relationships will be exported as new bridging tables.

Once the desired entities and relationships have been selected, click “Save & Activate” to create the profile and begin synchronization.

Connect to the SQL database and start querying your data!

Manually Configuring the Secret

In the event that validating the Key Vault URL fails, the Secret may need to be updated manually.

Navigate to https://portal.azure.com. Click on “All Resources”, then on the Key Vault resource then on “Secrets”, as shown below.

If the setup user does not have access to the “Secrets”, the setup user may be able to give themselves permission through the “Access Policies” interface. Simply enter the setup user’s own username in the Principal field then use the “Secret Management” permission template.

Secrets cannot be modified directly, instead, select the secret created by the script and click “New Version”, as shown below:

Then change the Secret from “certificate” to “manual”, enter the full Connection String as the “Value” and click Save at the bottom.

Once saved, refresh the left pane to refresh the Secrets, open the latest version by clicking on it. Then, click on the “0 Tags” panel, as shown below, to tag this secret with information needed by the Data Export Service.

On the right, there will be a grid of empty text fields. In the left-hand side, enter the Organization ID, on the right-hand side, enter the Tenant ID. This Tag will be necessary for the validation to succeed.

Next to the “Secret Identifier” field in the Secret Version panel, click the “Copy” icon to copy the URL to the clipboard.

Then, open the previous version of the secret and change it to disabled, then click Save.

Finally, back in CRM, use the new URL to try validating the Key Vault URL.

Setting up additional Data Export Profiles

If there are multiple CRM organizations, for example a production instance and a sandbox instance, a second Database in SQL and a second Secret in the Key Vault must be configured.

Alternatively, it could be configured to use the same Database in SQL, but the existing Key Vault Secret would still have to be configured and a separate prefix used. This option is not recommended as it would clutter the existing database, making it harder to take backups, as well as confusing the scope of the tables as “prod_accounts” and “test_accounts” would be in the same database.

New Database

In the SQL instance, create a new Database and create a new (or similar) database user for the connection String. The permissions script can be found in section “1.1.1 SQL Database User”.

New Secret

Create a new Connection String, similar to the one in section 1.3.2.6, except replace the “Initial Catalog” with the name of the new database and the new database user (if different).

Start by creating a new Secret in the Azure Key Vault, as shown below:

First change the Secret type from “Certificate” to “Manual” then give it a name, and enter the connection string as the “Value”. Then click “Create”, as shown below:

If the new Secret does not immediately appear in the list of Secret, click Refresh, as shown below:

Then click the new Secret to open it. A Tag must be configured on this secret to allow the Data Export Service to properly find and validate it.

Once the Secret is open, click on the latest (and only) version, as shown below, to Tag it:

Click on the “Tags” panel to open the list of Tags, as shown below:

In the Tags list, enter the CRM Organization Unique Identifier in the left box, as the “Tag Name”, as shown below. You can find this ID from CRM. Refer to section 1.3.2.7.

Press “Tab” or click on the right box to enter the value, as shown below. The value will be the Tenant ID. Refer to section 1.3.2.8 for how to find this ID.

Then press Tab again, or click outside the field, then click “OK” in the bottom, as shown below.

Finally, configure the Data Export Profile in the CRM organization per section 1.4 through 1.5.

---

About the Author:

Matthew Foy is a .NET developer working on CRGroup’s SharePoint and CRM consultancy team. He has a passion for robust solutions and a keen interest in solving problems.