How To: Configure Data Export Service with SQL Server on an Azure VM using Azure Resource Manager

This guide is intended to show you how to easily configure the Dynamics 365 Online Data Export Service solution with SQL Server on an Azure VM. 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. This post will be similar to my original post here. I will first list the prerequisites required, then show you step-by-step how to setup Data Export Service with SQL Server on an Azure VM.

Prerequisites for using Data Export Service

First, ensure that you have met the prerequisites here, I have consolidated the list here for reference. These will be the same as in my previous blog post except for the Azure SQL Database portion(this has changed to reflect the SQL Server Database). Do not skip this step!

SQL Server Database

• 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 SQL Server on an Azure VM using Azure Resource Manager

Now that we have met the prerequisites, we can now begin creating the SQL Server on an Azure Virtual Machine. 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'.

Navigate to your new resource group and click 'Add'. This is where we will create the SQL Server Virtual Machine. Search 'sql server 2017 enterprise', select the 'SQL Server 2017 Enterprise Windows Server 2016' option and create it.

Here, we will name the VM, create our user and select the resource group we created previously.

Next, you will select the virtual machine size. Be sure to select the option that fits your needs, I am selecting the cheapest option for this demo only.

For configuring optional features, I am leaving everything default except I am selecting to not allow any inbound public ports under 'Select public inbound ports'. We will be configuring the inbound port on the next step.

Next, we will configure the SQL server settings. This is where we will specify the public port and allow for SQL authentication. Follow my example below as this will be important for connecting to the SQL VM later.  It is your responsibility to keep your VM secure beyond these steps, I am only showing you the minimum requirements to set up the VM with Data Export Service. Select 'OK' and 'Create' to provision your new SQL Server Virtual Machine.

We will need to do one last thing before attempting our connection to the new VM. In order to connect to the VM over the internet, we will need to convert the IP address to a DNS name. Navigate to your newly created VM and select 'Configure DNS'.

Here, we can specify the DNS name and then hit 'Save'.

Now that we have configured the DNS name label, we can now connect to the SQL server VM. For this example, I will be using SQL Server Management Studio(SSMS) in order to connect to the SQL Server and create a new database for our Data Export Service. Note: I am using a completely separate machine with SSMS which is accessible over the internet, in order to prove that our Azure VM is truly accessible over the internet.

First, open up SSMS and input the DNS name and public port that we specified while provisioning the SQL Server VM.

Success! We have successfully connected to our SQL Server VM over the public internet.

Next, we just need to create the database where we want our database exported from CRM to reside. Otherwise, our database will be exported to a system database like master or whichever is specified in the catalog of the connection strings. To do this, right click databases, select 'new database', and create a name with owner. Again, I am just using the SQL login for this demo only. You may want to create different users or create your database differently, just be sure to refer to the prerequisites for the database requirements.

We can now see our newly created database in the list.

Now that we have successfully connected to the SQL Server VM and created the database, let's switch over to Visual Studio in order to collect the connection string we will want to use for the Azure Key Vault. We will connect to the server using the same method we used in SSMS.

Select 'Test Connection' and we can now connect to our 'DES' database.

To get the connection string, all you need to do is right click the data connection and select 'Properties'. It will now be present in the bottom right of your screen (by default).

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.

Configuring the Azure Key Vault using Azure Resource Manager

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.

Note: These will be the same steps from my previous blog post here using the new connection string.

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.