Dynamics 365 Data Export Service with Azure SQL Database

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.

E. Choose the entity and relationship, as per your requirement need to synchronize data.

F. Active the export profile.

Data export service is created, now you can create a record and it will sync to Azure SQL Database.

Example Record and Validate the Sync:

Create an account record.

Check in Azure Database using Query Editor tool  

Thanks !!!

 

Advertisements

3 thoughts on “Dynamics 365 Data Export Service with Azure SQL Database

  1. In our company we are using sync facility for move data between ms crm 2016 & azure sql database. But the requirement we don’t to trigger this integration when records get deleted in ms crm 2016.
    How can we do it?

    Like

  2. You forgot to write that the Key Vault needs to be created in the same mini version of Azure where Dynamics/O365 stores its Active Directory. Then you would not have the error in red that you published with script execution screen. And to create Key Vault you actually need to convert the mini Azure to a full subscription.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s