Debugging Test/UAT Data from Development VM in D365FO

 JIT access to TEST/UAT

Just-in-time access which is required for various troubleshooting efforts, running unplanned queries, or data upgrade problem solving.

Go to LCS and select the environment for which you wanted to connect the database. In my case, I am using TEST.

Enter the firewall by adding the IP address of the Dev VM from where the database will be connected.

Upon clicking Confirm, there will be a message which says about the expiry time of this firewall. Usually 8 hours.

The next step is accessing the database and the mode needed.

In the LCS page, under Database Accounts section, 4 options will be displayed for the accessing reason. As we are troubleshooting, we need write access and thus selecting ‘Troubleshooting tuning for AX”.

Select the reason and enter the details.

Click ‘Request Access’ and refresh the page. User will be presented with the login details .

Note down the details of SQL server, database name , user name and password.

These are needed when we connect the database from dev VM.

Refer Microsoft link for further information

Connect to Test Database

Now with the above credentials, connect to the TEST database and create a new login which will be used for debugging.

Use SQL Server authentication login(not Windows) to connect the database

Execute the below query which creates the new user used for debugging .

CREATE USER [axdevdebugadmin] WITH PASSWORD=N'Pass@word1', DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember N'db_owner', 'axdevdebugadmin'
GO

Web.Config changes:

Connect to the VM and navigate to the folder having ‘AOS Service’ -> WebRoot and select web.config file.

Take a copy of this file as we are going to modify some of the details in the file to connect the dev VM to TEST database.

Stop the following services :

IIS

World wide publishing

MS Dynamics 365 Batch

MS Dynamics 365 DIXF

Close the VS

Any active connection to local SQL db.

Right click on ‘Web.config’ file and open in Notepad. Make changes to the following tags.

<add key="DataAccess.Database" value="LCS JIT database name" />
<add key="DataAccess.DbServer" value="LCS JIT database server />
<add key="DataAccess.SqlUser" value="LCS JIT user name" />
<add key="DataAccess.SqlPwd" value="LCS JIT password" />
<add key="DataAccess.AxAdminSqlPwd" value="pass@word1" />
<add key="DataAccess.AxAdminSqlUser" value="axdevdebugadmin" />

Start the IIS and World wide publishing service.

Connect the Dev VM URL . You can check if it is accessing the test database by navigating to

System Administration -> Inquiries -> Database -> Database Information

Now the system is ready to start the debugging.

Tips and precautions while doing the process

1. Make sure the VM and the higher environment are in the same version .
2. Latest code in the VM where the debugging is triggered. Remove unwanted pending changes and do a buikd+sync in the VM before initiating the connection.
3. It is always recommended to have the code base same in both the environments.
4. Do not trigger database sync after connecting to TEST/UAT database .
5. Take a copy of the web.config file before making the changes.
6. Revert the changes once the debugging is finished. If left with the same web.config file , the URL will be inaccessible after 8 hours. Please note the JIT is alive only for 8 hours.
7. If the database is refreshed within 8 hours, we need to generate a new JIT access.