How to configure the Dynamics 365 Online Data Export Service with an Azure SQL Database

Dynamics 365 (online) CRM data can be easily exported to Azure SQL database using Data Export Service. This will allow use of Power Bi based reporting based on entire Dynamics 365 organisation data. Data Export Service can export Dynamics 365 (online) delta changes continuously to SQL Database.

Prerequisites for configuring Data Export Service:

>>Microsoft Dynamics 365 (online) and Azure Key Vault services must operate under the same tenant and within the same Microsoft Azure Active Directory. More Info- Azure integration with Office 365

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

>>Azure Key Vault Service

>> Microsoft Dynamics 365 (online) 2016 or later.

>>Enable pop-ups for Web Browser.

>>User that is assigned Microsoft Dynamics 365 System Administrator security role.

STEPS:

Create Azure SQL Database and configure:

>>Follow this article to Create Azure SQL Database.

>>After creating the DB, you have to add the correct client IP in Server firewall which is used by “Data Export Service”. The list of those IP addresses is given here. For my article, the firewall looks like below after adding the IP-

>>Now we have to create database user. Connect to the database using SSMS and run the below command to create user-

Open a New Query windows and run the commands-

CREATE LOGIN dynamics365 WITH PASSWORD = <set your password here>;
GO

USE master

CREATE USER [dynamics365] FROM LOGIN [dynamics365]
Go

Change the DB connection as highlighted and run these commands –

USE [dynamicssqldb]

CREATE USER [dynamics365] FROM LOGIN [dynamics365]
EXEC sp_addrolemember N’db_owner’,’dynamics365′

>>You can see the DB user dynamics365 is made db_owner of the “dynamicssqldb“. Also it have to be part of master DB. Now all configuration for Azure SQL Database are completed.

Create and configure Key Vault:

>>Get the following information related to your Azure Subscription before creating the Key Vault.

# ----PLACEHOLDER------------------------------------------------------------------ #
$subscriptionId = '[Specifies the Azure subscription to which the Key Vault belongs.]' You can get this value from "Cost Management + Billing - Subscription" page in Azure portal.
$keyvaultName = '[Specifies the name of the Key Vault. If the Key Vault does not exist, the script will create one]'
$secretName = '[Specifies the name of the secret that is put into the Key Vault. The secret holds the destination database connection string.]'
$resourceGroupName = '[Specifies the Resource Group for the Key Vault.]'
$location = '[Specifies the Azure region where the Resource Group and Key Vault is placed.]'
$connectionString = '[Specifies the destination database connection string that would be placed as a secret in the Key Vault.]' This is noted while creating the Azure SQL Database.
$organizationIdList = '[Specifies a comma separated list of all the CRM Organization Id which will be allowed to export data to the destination database.]' This you can get from Dynamics 365-->Settings--> Customizations-->Developer Resources-->ID (Under Instance Reference Information)
$tenantId = '[Specifies the Azure Active Directory Tenant Id to which all the specified CRM Organizations belong to.]' This you can get from Azure portal-->Azure Active Directory-->Properties-->Directory ID
# -------------------------------------------------------------------------------- #

Run the following script using PowerShell with the values updated in it-

# ----PLACEHOLDER------------------------------------------------------------------ #
$subscriptionId = 'ae2251-f2b0-4asdse-3458-6asddsdsf6'
$keyvaultName = 'testdynamickey'
$secretName = 'secretnew'
$resourceGroupName = 'sqldbresourcegroup'
$location = 'australiaeast'
$connectionString = 'Server=tcp:dynamicsqlserver.database.windows.net,1433;Initial Catalog=dynamicssqldb;Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;'
$organizationIdList = 'd5465676-15432-44553-8565-878e543541'
$tenantId = 'sare4tg4-r5450-t54c8-ayt654gf-fd45fdstg72'
# -------------------------------------------------------------------------------- #

# Login to Azure account, select Subscription and tenant Id
Login-AzureRmAccount
Set-AzureRmContext -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 30
}

# 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 "Secret: " $secret.id.TrimEnd($secret.Version)
Write-Host "Connection Key Vault URL is:   " $secret.id