Dynamics 365 Data Export Service with Azure SQL Database

Views (5854)

AbhiCode

492

Like(0)

Report

Dear DynamicsPeople,

In Dynamics 365, we can synchronize Dynamics CRM Database entity wise on Azure SQL Database.Prior Dynamics 365 online version main issue occurred database backup or some BI work or push Dynamics CRM data to the third party application. Thus Microsoft comes with Export Service.It’s easy to use and check all failure records.

Following steps to setup data export service,

Step 1: Azure SQL database setup

A. Go to your Azure portal and create SQL Database with appropriate information

B. Open SQL database in visual studio or Query editor in the Azure portal.

C. Login into the database and create a User with all permission of database.

And execute the following query with your new username and password

Step2: Create key with secret using PoweShell command or manually from GUI

PowerShell script, which needs value for variables like following

A. $subscriptionId = ‘[Specifies the Azure subscription to which the Key Vault belongs.]’
B. $keyvaultName = ‘[Specifies the name of the Key Vault. If the Key Vault does not exist, the script will create one]’ Any name of key value
C. $secretName = ‘[Specifies the name of the secret that is put into the Key Vault. The secret holds the destination database connection string.]’ Any secret name
D. $resourceGroupName = ‘[Specifies the Resource Group for the Key Vault.]’
E. $location = ‘[Specifies the Azure region where the Resource Group and Key Vault is placed.]’
F. $connectionString = ‘[Specifies the destination database connection string that would be placed as a secret in the Key Vault.]’
G. $organizationIdList = ‘[Specifies a comma-separated list of all the CRM Organization Id which will be allowed to export data to the destination database.]’ From Dynamics CRM Setting > Customization > Developer Resources

H. $tenantId = ‘[Specifies the Azure Active Directory Tenant Id to which all the specified CRM Organizations belong to.]’

Extract GUID from the “FEDERATION METADATA DOCUMENT” URL

https://login.windows.net/fab3736b-***-****-814d-a9af412683e0/federationmetadata/2007-06/federationmetadata.xml

Power shell after collecting all value of the variable, Open Power Shell and execute the following script into power shell window. [Replace variable values from your data]

$subscriptionId = ‘3f5011fd-f3ae---4003f2ad99ff’
$keyvaultName = ‘DynamicsPeopleKVN’
$secretName = ‘DynamicsPeopleSecret’
$resourceGroupName = ‘DynamicsPeopleRG’
$location = ‘East US’
$connectionString = ‘Server=tcp:dynamicssynh.database.windows.net,1433;Initial Catalog=DynamicsSyncDB;Persist Security Info=False;User ID= dynamicsPeopleUser ;Password= ;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;’
$organizationIdList = ’89b440e6--4c6d--1e481da1cd89′
$tenantId = ‘fab3736b----a9af412683e0’

# 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 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””

After executing script into power shell you got the Key Value URL, copy this URL and we required later. [ Important Do not forget this URL]

Step 3: Add Data Export service into Dynamics

A. Go to Dynamics CRM Setting > Dynamics MarketPlace > Search “Data Export Service”

B. Click on “Get It Now” and Continue and agree on the condition.

Note: This service is Available at, North America South America Europe Middle East Africa Japan Asia/Pacific Australia

C. Now, click on Setting > Data Export.

D. Create Data Export service record, with fill up all information and validate it. Remember the put Key Vault Url, which we already create in Step 2.