D365FO Access Prod SQL For Cloud Hosted Environments

Please excuse the basic website, most of my articles are hosted on LinkedIn, however, this article was not allowed on their platform

The following explains how to access your Production SQL server in D365FO for cloud hosted environments directly with SSMS or any other external tool.  Typically, this has been explained as impossible and is not something Microsoft wants you to do.  Please carefully consider the following disclaimer.  Also note I ran this through Microsoft's Security Response Center and sought approval/input before publishing.

Disclaimer ⚠

This introduces a security vulnerability to your production system.

Accessing your Production SQL database goes against all recommendations by Microsoft and violates many core tenants of the D365fo best practices.  This dramatically increases the risk to your implementation's stability and could lead to unexpected behaviors.  Should any resulting change require a Microsoft support ticket, the issue would be considered customer introduced and troubleshooting time increased.

Do not build any architecture or framework around this access.  Microsoft plans on closing this loophole in the future with or without notice.

From personal experience in Ax 2012 (the precursor to D365FO), where Prod access was allowed by default, nearly every customer introduced issues with frequent data manipulation which bypassed business logic.  It makes sense why Microsoft wants to lock this down.

Step 1:

You need to introduce the ability to execute your own custom SQL in production.  There are a few ways to do this, or you may already have added this to your project.

Microsoft already has code in the system which runs SQL on your current database.  You just need to build your customization on top of this.  For example, you can look at base class InventWarehouseOnHandAggregatedViewBuilder where they directly update the SQL view because the desired view isn't possible with the standard AOT objects. While exploring Microsoft's code to make your own code, find references is very useful here.  There is some documentation from Microsoft directly: https://learn.microsoft.com/en-us/dotnet/api/dynamics.ax.application.statement.executeupdate.  You have a few choices on how to approach the direct SQL customization, here are some examples:

a. Build a simple form with a single text box "Sql text" and a button "run".  The button would call code to execute the SQL from your textbox.  I implemented this a few times in the early days of ax7 / FO as it is very quick and easy.

OR

b. Hardcode the SQL from step 2 below in a class.  Optionally, you could take this live with no downtime: https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/deployment/run-custom-scripts

OR

c. Use something more advanced. Denis Trunin has published some code which is more fully featured to run SQL: https://denistrunin.com/xpptools-sqlexecute/

Step 2:

Run the following SQL scripts in Prod.  I do want to give credit to Pawan Deep Singh for the guide on creating users with direct SQL: https://axparadise.com/how-to-debug-the-sandbox-test-environment-in-d365fo/.  Pawan was also credited in the Direct Sql tool above from Denis and has his own version of this tool on his site you may find useful.

Please note you will run these commands from your tool above, rather than JIT access. And the firewall command is slightly different due permissions issues.

Run these SQL statements using your code from step 1:

create user <yourUser> with password = 'yourpassword'

exec sp_addrolemember 'db_owner', '<yourUser>'

exec sp_set_database_firewall_rule N'<yourUser>', '<yourIP>', '<yourIP>'

Note: this is the IP of the computer you want to connect from.  While it is possible to denote a range of IP addresses here, I would strongly caution against this.

The above creates your user with firewall permissions.  Now we need to know the details for our connection string.  Run these commands and save the results:

select SERVERPROPERTY('ServerName') + '.database.windows.net' as ServerName

select DB_NAME() as CurrentDatabase

Now you have the production server and database name, you can connect as you would any other sandbox environment with SSMS: https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/database/database-just-in-time-jit-access

Make sure to place your database name in the options tab, as you would for JIT access.

Now you are connected with db_owner permissions.

Tips:

1. This access is permanent; it does not expire after 8 hours as the JIT access does.  To remove access, run this SQL:

DROP USER <yourUser>

2. This also works on sandbox environments and is useful if you find yourself utilizing the JIT access frequently, you can bypass those steps as this is permanent.

3. Having an open connection, such as in SQL Server Management Studio, may prevent certain LCS updates from occurring.  I did notice code deploys from LCS did not progress until I closed the SQL connection, although, I did not spend much time proving this was the source of the delay.  You should close the connection and the delete the user once your task is completed.

4. This article was published April 2024 and tested with version 10.0.38 PU62.  It may not work in future versions.

5. Production query performance seems to behave differently than Sandbox.  If you find large queries running slow, you may consider adding "option (maxdop 64)" to the end of your query (please research the risks with this beforehand).

Hope this helps!

Mike Schiermeyer