In my
scenario we need to export CRM entities data (recurring sync) to Azure SQL database
Requirements:
Should be in same tenant.
1.
Online
CRM D365 - Dynamics 365 Customer Engagement Plan
3.
Azure
SQL Database
4.
Dynamics
365 – Data Export Service
Setup:
Online
D365 Subscription.
Azure
SQL Database.
(a) Navigate to Home > SQL
Databases
(b) + Add Create SQL Database
(c) Select Basic and Create the New
Database, SQL Server, and Resource Group as required.
(d) Select Review + Create
Microsoft
Dynamics 365 - Data Export Service:
Data
Export Service Api’s
Dynamics
365 – Data Export Service:
(a) Navigate to Settings from D365
Advance Settings
(b) Select Data export
(c) Select +New
Fill the
required fields data and Configure Key Vault URL from PowerShell
Key
Vault URL: Run PowerShell as
Administrator and execute
The below
PowerShell script enables you to provision a secret in KeyVault. Please execute
this from your PowerShell command line after replacing the placeholders below.
#
----PLACEHOLDER------------------------------------------------------------------
#
$subscriptionId
= '[Specifies the Azure subscription to which the Key Vault belongs.]'
$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.]'
$organizationIdList
= '[Specifies a comma separated list of all the CRM Organization Id which will
be allowed to export data to the destination database.]'
$tenantId
= '[Specifies the Azure Active Directory Tenant Id to which all the specified
CRM Organizations belong to.]'
#
--------------------------------------------------------------------------------
#
# 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.TrimEnd($secret.Version)
Result
will be
Update the Key Vault URL and click on next
Select Entities: To sync Data from CRM to SQL database
Select
Relationships for the respective entities
Review the configuration and select Create & activate.
Following
screen shot for Sync status:
