Data Export Service : Unable to connect to the destination mentioned in the KeyVault URL
When recently trying to setup the Data Export Service (DES) to replicate the Dynamics 365 data to an Azure SQL database I was facing issues when trying to validate the connection to the SQL database and got the following error.
After some troubleshooting I was able to resolve the issue but if you run into the above error, here are the possible causes and how to fix them
- Bad Connection String : Make sure to check there are no typos and verify the server, database, user id and the password are correct. You could use something like below to verify your connection string quickly if you are familiar with c#.
Use the Connection String option from Azure SQL and replace it with User Id and Password and then test it with similar code below to verify the connection string is working.
It should be in the below format
Server=tcp:{servername here}.database.windows.net,1433;Initial Catalog={db here};Persist Security Info=False;User ID={userid here};Password={pwd here};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
- Firewall : Need to configure your SQL to either set ‘Allow access to Azure Services’ to ON or configure the Client IP (recommended) as provided in the documentation.
When specifying the client IP specify the IPs from different region within your country and strip down based on your testing. In some cases, you might need to add more than the current region you have deployed to.
- Authentication Type : Make sure your connection string provided in the Key Vault uses SQL authentication and not Active Directory Password.
If none of the above solutions worked, try mimicking it on a different SQL database on a Trial/same subscription to identify the possible cause.