Skip to main content

Notifications

Announcements

No record found.

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.

 

Permission type code

Permission name

CRTB

CREATE TABLE

CRTY

CREATE TYPE

CRVW

CREATE VIEW

CRPR

CREATE PROCEDURE

ALUS

ALTER ANY USER

VWDS

VIEW DATABASE STATE

 

 

Schema permissions required.

 

Permission type code

Permission name

AL

ALTER

IN

INSERT

DL

DELETE

SL

SELECT

UP

UPDATE

EX

EXECUTE

RF

REFERENCES

 

 

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

 

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'.

 

ss.png

 

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.

 

7624.ss1.png

 

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

 

6708.ss2.png

 

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.

 

6320.ss3.png

 

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.

 

1222.ss4.png

 

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.

 

0743.ss5.png

 

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'.

 

8473.ss6.png

 

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

 

3276.ss7.png

 

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.

 

4075.ss8.png

 

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

 

7608.ss9.png

 

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.

 

1423.ss10.png

 

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

 

0410.ss11.png

 

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.

 

8306.ss12.png

  

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

 

7026.ss13.png

 

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).

 

6557.ss14.png

  

Connection String

Data Source=desonsqlvm.centralus.cloudapp.azure.com,57500;Initial Catalog=DES;User ID=desadmin;Password=***********
 

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.

 

1261.ss15.png

 

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.

 
0702.ss16.png
 

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

 
8322.ss17.png
 

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

 

3225.ss18.png

 
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'.
 
 
3755.ss19.png
 
ss20.png
 
The tag name will be your organization id and tag value will be your tenant id of your Dynamics 365 Online org. 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.
 
ss21.png
  
ss22.png
 
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. Select the '+New' button, create a name for the profile, input your secret identifier as the Key Vault URL and select 'Validate'.
 
      
ss23.png
 
  ss24.png
 
Success! You have now configured Data Export Service with SQL Server on an Azure VM using Azure Resource Manager. You are now able to complete the setup for Data Export Service and can begin exporting your CRM data to Azure. 
 
Thanks for reading!
 

Comments

*This post is locked for comments