I recently worked with a customer to implement and roll-out Data Export Service for their Dynamics 365 – an add-on service for replicating Dynamics 365 Online data to an Azure SQL database to help enable analytics and reporting scenarios with Azure analytics services. You can read more about Data Export Service here at https://msdn.microsoft.com/en-us/library/mt788315.aspx.
Like most other enterprise customers, this customer was new to Azure SQL database and its associated services and elements required for Data Export Service (including SQL logins, firewall rules and Key Vaults), and would like some documentation for a review by the internal (enterprise, solution, security, network and data) architects to bridge the gap in their understanding and conform with the internal IT governance and by the BAU team to ensure the supportability of the solution.
The document contains detailed steps required to setup and configure the Azure SQL Databases required for all Dynamics 365 environments, which typically consist of DEV, TEST, UAT and PRD. In this blog post, I cover the document content structure at a high-level, and will use Contoso as an example.
SQL Azure Databases
The first step it to provision Azure SQL databases via the Azure Portal to match the number of Dynamics 365 instances, in this case DEV, TEST, UAT, and PRD. Please note that it is possible for multiple Dynamics 365 instances to point to a single SQL Azure database, or to use elastic pools.
+) Standard S3 (100 DTU, 250GB Storage), approx. 200 AUD monthly. Please refer to https://azure.microsoft.com/en-au/pricing/details/sql-database/ for more details.
As a security measure, access to the database server is controlled via a firewall and is separate to any setup as part of Azure virtual networks.
Add known IP addresses that would require access to these databases.
Connect via SQL Management Studio to these databases using the respective admin accounts from a computer with client IP address listed in the firewall settings above. Create additional service accounts for other integration requiring access to these databases (e.g. MuleSoft, Data Migration, etc.).
Use the following script to grant access to the SQL databases. It is a best practice to create a new role with minimum privileges as required by the Data Export Service or other integration service users.
====================================================================================== -- Create SQL Login template for Azure SQL Database and Azure SQL Data Warehouse Database -- ====================================================================================== -- Create in the 'master' database CREATE LOGIN svc_sql_crm-dev WITH PASSWORD = '<<Password>>' GO -- ======================================================================================== -- Create User as DBO template for Azure SQL Database and Azure SQL Data Warehouse Database -- ======================================================================================== -- For login <login_name, sysname, login_name>, create a user in the database CREATE USER svc_sql_crm-dev FOR LOGIN svc_sql_crm-dev WITH DEFAULT_SCHEMA = dbo GO -- Add user to the database owner role EXEC sp_addrolemember N'db_owner', N'svc_sql_crm-dev' GO
Prior to setting up Key Vaults, you will need to gather the following information:
- Tenant ID, by going to App registration > Endpoints. Extract the GUID from any of the listed endpoints. Tenant ID in this example: 63deea34-e415-4339-872c-441efbceff00
- Dynamics 365 Instance IDs, by going to Settings > Customizations > Developer Resources.
Now, setup via the Azure Portal the following Key Vaults for storing encrypted connection strings to the Azure SQL databases:
For each Key Vault, create a new Access Policies:
- Principal: Data Export Service for Microsoft Dynamics 365
- Secret Permissions: Get
Create the following Secrets, and Tags in their respective Key Vaults. Capture the Secret Identifiers.
- Upload options: Manual
- Name: See Secret Name below
- Value: Azure SQL Database Connection String – Server=tcp:URLNAME.database.windows.net,1433;Initial Catalog=<<Database Name>>;Persist Security Info=False;User ID=<<svc_sql_username>>;Password=<<Password>>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
- Tag Format: D365 Instance ID as Tag Name, Tenant ID as Value
*) Key Vault URL = https://<<KeyVaultName>>.vault.azure.net/secrets/<<SecretName>>/<<SecretIdentifier>>
Now you are ready to configure Data Export Service in Dynamics 365. Enter the Secret Identifier URL into the Key Vault URL in the export profile, and click Validate.