Tag Archives: Microsoft SQL Server Management Studio

One of the most common objections clients express against migrating from Microsoft Dynamics 365 on-premise to Microsoft Dynamics 365 online is that they will loose access to their Dynamics 365 SQL database as it will be hosted by Microsoft and they cannot directly access their SQL database. In this post, we will cover how we can duplicate and sync our Dynamics 365 online SQL database in Microsoft Azure, and still access the data in the Dynamics 365 SQL database tables. The Microsoft Dynamic 365 Data Export Service allows us to duplicate and sync the Dynamics 365 online SQL database in Azure.

High Level Steps

To successfully implement the Microsoft Dynamic 365 Data Export Service and replicate what has been done in this blog post, you will need carry out the following steps (explained in detail below):

1. Have an Azure instance and a Dynamics 365/CRM Online 8.1 or later instance
2. User must be a Dynamics 365 System Administrator
3. Link Azure Active Directory to Office 365
4. Create a SQL Database in Azure
5. Access Azure SQL Database using Microsoft SQL Server Management Studio
6. Create a database user with correct permissions
7. Install the Azure PowerShell module
8. Install Data Export Service solution
9. Create an Azure Key Vault
10. Enable tracking on custom entities you want to sync
11. Create a Dynamics 365 Export Profile
12. Microsoft SQL Server Management Studio Validation

Detailed Steps

1. Have an Azure instance and a Dynamics CRM Online 8.1 or later instance

If you do not have a Dynamics 365 and an Azure instance, you can go Office 365 and get an Office 365 subscriptions with a Dynamics 365 instance and an Azure instance. If you would like to try out the technology using trial licences, you can get a trial Office 365 license, which has a trial Dynamics 365 instance and a trial Azure instance by following the following instructions: Microsoft Dynamics 365: How to Set Up a Free Online Trial Version.

2. User must be a Dynamics 365 System Administrator

To create Export Profiles in Dynamics 365 and implement the steps in this blog post, the user must be assigned a System Administrator security role. The Export Profiles are configurations, in Dynamics 365, that allow you to define Dynamics 365 SQL database duplicating and syncing relationship to Azure i.e. the entities and relationships to be be synced and duplicated.

3. Link Azure Active Directory to Office 365

If your Azure instance and Dynamics 365 are in the same Office 365 organization or if you used the trial license option provided in Step 1 above, the Azure Active Directory will already be linked to Office 365. Microsoft also offers you the option of linking an Azure Active Directory, of another Office Organization, to your Microsoft Office organization.

After the Azure Active Directory has been linked to Office 365, you can see the Office 365 users (that you can find under Active Users in the Microsoft Office 365 Admin Center) in Azure Active Directory. In Office 365:


In Azure Active Directory:

4. Create a SQL Database in Azure

Go to Azure Portal for the account described in Step 3. Click on “SQL databases” option and you get a window similar to the one below. Click on “+ Add” as shown by the arrow below.

You will be presented with a window similar to the one below. You have to provide:

• Subscription: the Azure subscription under which you will be creating the database.
• Resource Group: If you do not have one or prefer to create another, you can can create one from the window below.
• Database Name: The name of the database you will be creating. You cannot have 2 databases under the same name.
• Server: Azure server name must be unique. If you do not have one or prefer to create another, you can can create one from the window below. There is a cost associated with the creation of a server.
• SQL Elastic Pool: If you choose Yes, the storage resources will be adjusted based on need.
• Compute + Storage: You can choose the SQL service tier and performance according to your needs.

After you click on “Review + create”, you will be taken to a Review page where you see the options you have selected before you finally create your SQL database. After you click on “Create”, Azure will proceed with the creation an SQL database immediately.

After the process is done, you will be able to see your new SQL database under the list of SQL Databases in Azure. At the time of writing, the process takes less than 2 minutes.

5. Access Azure SQL Database using Microsoft SQL Server Management Studio

If you do not have Microsoft SQL Server Management Studio already installed, download and install it. It is free. Connect to the Azure SQL Database created in step 4, using your credentials (login/username and password) of the Azure Server hosting your SQL database:

You may also be prompted to enter your Office 365 credentials. After entering the required information, you will able to see the access Azure SQL Database created in step 4 in Microsoft SQL Server Management Studio.

6. Create a database user with correct permissions

The user created in this step is a service account that will be used in Microsoft Dynamic 365 Data Export Service, integrating Dynamics 365 with Azure. The expanded view of Microsoft SQL Server Management Studio, shown in Step 5 will look similar to the image below:

(1 in image) is the Azure database server we connected to in Step 5 above, containing the SQL database we created in Step 4. (2 in image) is the Azure database we created in Step 4. To create the a user in a specific database, e.g. “adventure” in the image above, you need to create the user in the master database and then proceed to add that user to a specific database. Here are the specific steps:

1. Run the following script against the master database (3 in image):
   
   CREATE USER [<userName>] WITH password='<userPassword>';
   
   Example:
   
   CREATE USER [d365exporter] WITH password='ItsFascinating2020';
   
   
2. Refresh the Azure database server connection (1 in image ).
3. Navigate to master database’s Users (4 in image) and Logins (5 in image). You should see the user you created in created in part (b), in both places.
4. If you see the user under master database’s Users (4 in image) but not under the Logins (5), run the following script against the Logins (5 in image):
   
   CREATE LOGIN <userName> WITH PASSWORD = '<userPassword>'
   
   Example:
   
   CREATE LOGIN d365exporter WITH PASSWORD = ' ItsFascinating2020'
   
   Refresh the Logins (5 in image) and you will be able to see the user you created under the Logins (5 in image) as well.
5. Add the user we created in part (d), with the appropriate permissions, to the Azure database (2 in image) by running the following script against the database:
   
   CREATE USER [<userName>] FROM LOGIN [<userName>];
EXEC sp_addrolemember 'db_owner', '<userName>';
   
   Example:
   
   CREATE USER [d365exporter] FROM LOGIN [d365exporter];
EXEC sp_addrolemember 'db_owner', 'd365exporter';
   
   Refresh the database Users (6 in image) and you will be able to see the user under the list of database users.

7. Install the Azure PowerShell module

If you have not already installed the Azure PowerShell module on your machine, you will need to install it before proceeding to Step 8, where we will create the Azure Key Vault. The step-by-step instructions for installing the Azure PowerShell module can be found on the Microsoft website: Install Azure PowerShell.

8. Install Data Export Service solution

From your Dynamics 365 online instance, download and install the Data Export Service solution from the Microsoft App Source. Here the steps to follow:

1. Login into your Dynamics 365 online instance
2. Navigate to Settings >> Microsoft AppSource
3. In Microsoft AppSource, get the “Microsoft Dynamics 365 – Data Export Service”
4. Provide the required information and agree to the terms and conditions. The Microsoft Dynamics 365 – Data Export Service will be installed into your Dynamics 365 online instance as a managed solution.
5. After the installation, you will be able to access the Data Export Service via the web interface: Settings >> Data Export.
   

9. Create an Azure Key Vault

The Azure key vault is where we will store the credentials of the service account user created in Step 6. Therefore, when the Dynamics 365 Export Service is running, it will securely retrieve these credentials from the key vault to login into the Azure SQL database server and carry out the duplicating and syncing operations. The Azure web interface allows us to create a key vault. However, in this example, we will use the PowerShell script provided by the Dynamics 365 Export Service. The PowerShell script also enables you to provision a secret in the Azure Key Vault. Execute script from the PowerShell command line after replacing the placeholders below. Where to find the information needed to replace the script’s placeholders:

• subscriptionId – From the Azure SQL Database created in step 4.
• keyvaultName – If you do not not have a Key Vault, enter a name. A key vault will be created under that name, e.g. ‘D365Exp”, after you run the script.
• secretName – The key that will be used to look up the secret. You can enter a name of your choice e.g. ‘D365DataExport’.
• resourceGroupName – From the Azure SQL Database created in step 4 e.g. ‘D365’.
• location – From the Azure SQL Database created in step 4 e.g. ‘East US’.
• connectionString – From the Azure SQL Database created in step 4, you view the different formats of the connection string. Take the ADO.NET version of connection string (replace the username and password with credentials of the service account created in step 6).
• organizationIdList – In Dynamics 365, navigate to Settings >> Customizations >> Developer Resources >> ID.
• tenantId – In Azure, navigate to Azure Active Directory >> Tenant ID.

Here is the script to run in PowerShell after replacing the placeholder values:

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

After running the script, you can navigate to your Azure instance Key Vaults and view the key vault as well as the secret that was created by running script above. It will look similar to the image below:

10. Enable tracking on custom entities you want to sync

Out of the box entities are enabled for tracking. However, custom entities have to enabled for Change Tracking, if you would like to add them to the Dynamic 365 Data Export Service and have the the Dynamics 365 SQL database changes to be synced to the Azure automatically. To enable tracking for a Dynamics 365 custom entity:

1. In your Dynamics 365 instance, navigate to Settings >> Solutions
2. Open the solution containing the custom entity. In the solution, navigate to the Entities section. Select the entity’s General tab.
3. Enable Change Tracking
   
4. Save and publish the solution

11. Create a Dynamics 365 Export Profile

Dynamics 365 Export Profiles allow you to make the connection between your Dynamics 365 instance and your Azure instance. You have to provide the Azure SQL Database connection Key Vault URL, and the Dynamics 365 entities and relationships to be added to the Dynamics 365 Export Service, among other details.

To create an Export Profile in your Dynamics 365 instance, navigate to: Settings >> Data Export. Click on “+New” and you will get a window similar to:

To get the “Key Vault URL”, go back to the Key Vault created in Step 9, click on Secrets. Keep drilling down until you find the “Secret Identifier”. Copy that field paste into the “Key Vault URL” field, on the Export Profile.

Click on “Validate”. After a successful validation, click Next to select the entities and relationships you would like to duplicate in your Azure SQL database, and then complete the creation of your Profile. This may take a few minutes depending on the amount of data being duplicated.

12. Microsoft SQL Server Management Studio Validation

After the process is complete in Step 11, you can return to Microsoft SQL Server Management Studio to see the tables containing the entities you selected in Step 11 (refresh your Azure SQL Server if you had Microsoft SQL Server Management Studio open when you were running Step 11). In my case, I selected 4 out of the box entities in Step 11: Account, Incident, Contact and Product. We now run queries against those tables like any other SQL database.

Every time we add a new record or update an existing record, in Dynamics 365, for one the entities added to the Export Profile, within a few seconds those changes will be synced from the Dynamics 365 SQL table into our SQL database in Azure.