How To: Configure Data Export Service with an Azure SQL Database using Azure Resource Manager

This guide is intended to show you how to easily configure the Dynamics 365 Online Data Export Service solution with an Azure SQL Database using the Azure Resource Manager. This blog post shows you how to setup Data Export Service using the Azure Portal only and does not require you to use the PowerShell script at https://technet.microsoft.com/en-us/library/mt744592.aspx. I will first list the prerequisites required, then show you step-by-step how to setup Data Export Service with an Azure SQL Database.

Prerequisites for using Data Export Service

First, ensure that you have met the prerequisites at https://technet.microsoft.com/en-us/library/mt744592.aspx, I have consolidated the list here for reference. Do not skip this step!

Azure SQL Database service

• A customer owned Azure SQL Database subscription. This subscription must allow the volume of data that is synchronized.

• Firewall settings. We recommend that you turn off Allow access to Azure services  and specify the appropriate client IP addresses listed in this topic. More information: Azure SQL database static IP addresses used by the Data Export ServiceAlternatively, you can turn on Allow access to Azure services to allow all Azure services access.

• The database user must have permissions at the database and schema level according to the following tables. The database user is used in the data export connection string.

Database permissions required.

Schema permissions required.

Azure Key Vault service

• Customer owned Azure Key Vault subscription, which is used to securely maintain the database connection string

Important

• To use the Data Export Service the Microsoft Dynamics 365 (online) and Azure Key Vault services must operate under the same tenant and within the same Microsoft Azure Active Directory. More information:  Azure integration with Office 365

• The Azure SQL Database service can be in the same or a different tenant from the Microsoft Dynamics 365 (online) service.

Microsoft Dynamics 365 (online)

• A Microsoft Dynamics CRM Online 2016 Update or later version instance.

• The Data Export Service solution must be installed. Get it now from Microsoft AppSource.

• The entities that will be added to the Export Profile must be enabled with change tracking. To ensure a standard or custom entity can be synchronized go to Customization > Customize the System, and then click the entity. On the General tab make sure the Change Tracking option under the Data Services section is enabled.

• You must have the System Administrator security role in the instance of Microsoft Dynamics 365 (online).

• You will need to retrieve your Dynamics 365 organization(OrgId) and tenant(TenantId) ids in order to configure Azure Key Vault.

Web browser

• Enable pop-ups for the domain https://discovery.crmreplication.azure.net/ in your web browser. This is required for auto-sign in when you navigate to Settings > Data Export.

Services, credentials, and privileges required

• A Microsoft Dynamics 365 (online) subscription. Only users that are assigned the Microsoft Dynamics 365 System Administrator security role can set up or make changes to an Export Profile.

• Microsoft Azure subscription that includes the following services.

• Azure SQL Database or Azure SQL Server on Azure virtual machines.

• Azure Key Vault.

Configuring the Azure SQL Database and Azure Key Vault using Azure Resource Manager

Now that we have met the prerequisites, we can now begin creating the Azure SQL Database and Azure Key Vault. First, we will need to create a new Azure Resource Group. To do this, navigate to your Dynamics 365 Azure tenant at portal.azure.com and select 'Resource Groups'.

Select 'Add' to add a new resource group, give the resource group a name and select 'Create'.

Next, we can now add our Azure SQL Database by selecting 'Add' under our new resource group. Search 'sql database', select the SQL Database option and create it.

When creating the SQL Database, give it a name, select the resource group that you just created and also create a new server. Keep in mind that your server name must be unique when creating it on Azure.

Next, we will want to select the pricing tier. This will vary greatly based on the amount of data your organization has, however for this example I am going to be using 'Basic'.

For the rest of the options, source, elastic pool and collation, we will leave these at the default but you may change them based on your requirements. Lastly, select 'Create' to start deploying the Azure SQL database/server to your resource group.

Now, we can see that our Azure SQL server and database were created under our resource group.

Let's test our connection to the database. To do so, select the SQL server 'desserver' and in the Overview tab select 'Show firewall settings'. Here, you can create a rule to allow for an IP or IP range to connect to your server, as well as creating or adding an existing virtual network. In this example, I am just going to add my machine's IP address so that I can connect using Visual Studio.

Note: You may also use SQL Server Management Studio to test, just be sure to add the correct IP to the firewall settings.

Next, we can test our database connection in Visual Studio by going to the Server Explorer and selecting 'Connect to a database'. We can now paste in our server name, change the authentication to SQL Server Authentication, and input our server credentials that we created when provisioning our Azure SQL server. Once complete, you will then be able to select the database that we created in Azure.

Now that our connection to the database is successful, we will need one more thing before creating the Azure Key Vault. Right click the database and select 'Properties', this will show us the Connection String to the database which we will need for Azure Key Vault.

Here we have the full connection string:

Note: If the Connection String Password is copied as a '*****' value like above, be sure to change this to the actual password when copying it over to Key Vault.

Now that we have the connection string, let's create the Azure Key Vault. Navigate to your resource group, select 'Add' and search 'key vault'. We will select the first option 'Key Vault' and create it.

When creating the Key Vault, we can select our resource group and leave everything else default. However, we will want to add one more access policy. Select Access policies, '+ Add new', and select the principal by searching for Data Export Service. We will just need the 'get' secret permission for this policy. Finally, select Create to provision the Key Vault.

Note: If you do not see Data Export Service as a principal, you most likely have not installed the Data Export Service solution on your Dynamics org or you are using an Azure tenant which is not your Dynamics Azure tenant.

We can now see our Key Vault added to our resource group:

Next, select the Key Vault and navigate to 'Secrets'. Select Generate/Import to add a new secret.

Here we can create a name for the secret and the value will be our connection string that we captured earlier. Create it. Now, select the secret you just created as we still need to add the tag and value to it. Select 'Current version' and then 'Tags'.

The tag name will be your organization Id and tag value will be your tenant id of your Dynamics 365 Online organization. If you have multiple orgs using Data Export Service, you will need to have a tag for each org id. After inputting the tags, select 'Save' and then copy the 'Secret identifier'. This will be the URL we use to activate our Data Export Service Profile in Dynamics 365.

Full Secret identifier

Now that we have followed all of the steps and generated the Secret Identifier, we can now activate our Data Export Service Profile. To do so, open your Dynamics 365 URL and navigate to the Data Export Service solution. Create a name for the profile, input your secret identifier as the Key Vault URL and select 'Validate'.

After validating, Data Export Service indicates that the validation is successful and gives a warning that the pricing tier is lower than P1. Again, the service tier will determine how your Azure SQL database performs with Data Export Service.

If you would like to know how to Configure Data Export Service with SQL Server on an Azure VM using Azure Resource Manager, I have covered this here.

Thank you for reading!