D365 FO : SQL Connection & Access of production environment Using X++

  • Introduction – In earlier version of Microsoft Dynamics AX, Customer and partner had full control and access of Microsoft SQL Server and they were able to connect to Microsoft SQL Server of production server.Customer and partner were also able to use SSMS (SQL Server Management Server) in Microsoft Dynamics AX Production for querying and executing direct SQL Statements in certain scenarios.
  • But In Microsoft Dynamics 365 finance and operation Cloud & SaaS (Software as a service application) model , you cannot connect to Microsoft SQL server of production and you don’t have any access of production server SQL.
  • Only Microsoft has full control of production servers and responsible for tasks like maintenance, backups etc.
  • Customer is using LCS (Life cycle services) for deployments, service upgrades etc.
  • It is not recommend to execute direct SQL statement in SQL server of production but still in certain scenarios we need it.
  • Steps to connect SQL server of Microsoft Dynamics finance and operations production environment and executing SQL statements directly using X++
  • In this example, login to visual studio and create a runnable class in Microsoft D365 finance & operations.

  • Write the below code, for SQL update and delete command execution in production server SQL database.
  • Above piece of code works just fine when you are executing SQL update or delete statements or commands. But in some scenarios, we have to use SQL select commands or statements directly in production SQL.
  • In below screenshot and code i am showing how can we execute direct SQL statement and command.
  • The difference bewtween update/delete & select sql statement is that in select i am using method statement.executeQuery(sqlStr) whereas in update/delete SQL statement i am using statement.executeUpdate(sqlStr).
  • In this way we can execute SQL update, delete, select etc statements and commands in the production SQL database of Microsoft dynamics 365 finance and operations using X++.
  • Also read my post on D365/AX7:Connect To An External SQL Database Using X++.

One thought on “D365 FO : SQL Connection & Access of production environment Using X++”

Leave a Reply