Let’s start by configuring Azure SQL Database. You can create an Azure SQL database in the same or another tenant as Dynamics 365.

This section will contain 2 parts:

2.1 Create Azure SQL Database

2.2 Create an Azure Key Vault

2.1 CREATE AZURE SQL DATABASE

Go to the Azure admin portal and open the SQL databases section. Click Add and specify the required parameters.

Dynamics 365 Reports and Data Export Add-Ons - CREATE AZURE SQL DATABASE

You will need a unique server name and other parameters you can adjust to your case. Put in the exact password instead of asterisks, of course.

Dynamics 365 Reports and Data Export Add-Ons - unique server name

Next, you need to select a subscription. This will depend on your Dynamics storage usage. You can get more information on the topic of how to analyze your storage in my other article.

Dynamics 365 Reports and Data Export Add-Ons - selecting subscription

You need to open the created database and click Show connection strings. We will need the Connection string later.

Dynamics 365 Reports and Data Export Add-Ons - Connection string

Then copy the connection string for future use. Don’t forget to replace the password with current data. You can do it in later stages as well for security reasons.

Dynamics 365 Reports and Data Export Add-Ons - coping connection string

2.2 CREATE AZURE KEY VAULT

Next, we need to create an Azure Key Vault to store connection details to the database. It should be under the same Azure as Dynamics 365, this is an important requirement.

Azure Key Vault allows us to save sensitive information and publish it for usage by different users and applications. This is the best way to manage (passwords and access) data.

Before we proceed with creating the actual Key Vault, I’ll explain several things about its content.

We are interested in 3 components of Key Vault:

Key - this is an identification how external applications can access secret data
Secret - actual sensitive data, for example, password or connection string in our case
Access policy - who can access the data. In our case, it will be a Data Export Service add-on

If you got this, the next steps should be pretty easy to understand and execute.

Click Create a Resource and select Key Vault. Specify parameters and click Review and Create.

Dynamics 365 Reports and Data Export Add-Ons - Create Azure Key Vault

Dynamics 365 Reports and Data Export Add-Ons - Create Azure Key Vault

Open created Key Vault and Go to Access policies. Add a new access policy, you need to add Secret permission Get, so the add-on can access the connection string. We don’t need any other permissions here. Click Select Principal and find Data Export Service. Select it and add Access Policy. In case you don’t see Data Export Service in the principal list, the add-on is not yet finished installation, or you skipped the add-on installation step.

Dynamics 365 Reports and Data Export Add-Ons - Add access policy

Next, we need to add a Key. Go to Keys and press Generate/import button. We don’t need any specific parameters here. Just click Create. When the Key is created – you need to open it and click Current version. You need to copy the Key identifier here to use it in the next stage.

Dynamics 365 Reports and Data Export Add-Ons - create a key

Dynamics 365 Reports and Data Export Add-Ons - Key identifier

Finally, we need to add a secret. The secret is the connection string in our case. If you remember you saved it in previous stages, please refer to them if you don’t have it. At this stage, we need to replace the password with the actual password.

Go to Secrets and click Generate/import. Specify the name and put the Connection string with a replaced password in the value field. Click Create.

Dynamics 365 Reports and Data Export Add-Ons - create a secret

Now you need to add additional parameters for Dynamics 365 to understand the Secret we’ve just created. This is the least logical section in the guide, but I tried to make it simple to reproduce.

Log in to your Dynamics 365. Click the Settings icon and then Advanced Settings.

Dynamics 365 Reports and Data Export Add-Ons - Advanced Settings

Expand Settings and click Customizations. Open Developer resources.

Dynamics 365 Reports and Data Export Add-Ons - Developer resources

Here you need to copy the ID for future use. I will reference it as Organization ID later.

Dynamics 365 Reports and Data Export Add-Ons - Organization ID

Now return to Azure Portal and get the tenant id. To get it, Open Azure Active Directory -> App Registrations -> Endpoints and copy the ID highlighted on the screen.

Dynamics 365 Reports and Data Export Add-Ons - Azure Portal

Next, go to the secret we created in the previous stage and specify its tags. An open secret, then click tags and add Organization ID left and Tenant ID right.

Dynamics 365 Reports and Data Export Add-Ons - Organization ID and Tenant ID

Well, the complicated part behind the next sections should be easier.