Azure SQL Data Source Configuration

When is it necessary to follow these setup steps?

Once initial setup has been completed in a Production environment, you typically will not need to do this more than once in that environment. A notable exception to this is if you make changes to your external Azure SQL server.

Outside of your initial Production setup, these steps will also need to be taken to configure other D365FO environments (e.g. QA/DEV, UAT, or STAGE) that you want to test or develop reports from.

Step 1 – Finding the connection string to your previously deployed Azure SQL Server

1. Navigate to portal.azure.com and sign on using your domain’s credentials and select from the menu on the left side of the screen.
2. Select the Azure SQL Server that you wish to connect D365FO to for data exports and note the name of the database.

3. Find and note the server name from this screen.

*Data Entity Publishing is also known as BYODB or BYOD setup, which stands for Bring Your Own Database and is Microsoft’s solution for allowing access to copies of AxDB (D365FO Master Database) tables for reporting.

You will need Azure Portal privileges to access the connection string this way. If you do not have the appropriate privileges, you may need an Azure Administrator for assistance in obtaining the connection string.

Step 2 – Adding the connection string to Dynamics 365 Finance and Operations

1. Navigate to the Data Management workspace in D365FO and select the button for “Configure Entity export to database”.

2. Click “New” at the top of the screen.
3. Fill out the “Source data formats” section of the screen.

*Once you set a name for the source you will not be able to change it later without deleting the record from D365FO and following the steps in this section again.

4. In the “Configure” you will need to add a connection string to the Azure SQL Server.
   • The connection string will be created by replacing [ServerName] and [DatabaseName] with the information collected in Step One of this document, as well as the [SQLUsername] and [SQLPassword] into the following template:
     • Data Source=[ServerName]; Initial Catalog=[DatabaseName]; Integrated Security=False; User ID=[SQLUsername]; Password=[SQLPassword]

*If you were not the user who created the Azure SQL Server, you may need to contact the user who did or an Azure Administrator to obtain the [SQLUsername] and [SQLPassword].

5. Once entered correctly, click on the validate button. You should see the following notification if everything worked properly.