Dynamics AX Retail has an intense Architecture behind the scenes, so some issues could take much more time to troubleshoot and fix the problem. So I am just writing down about this issue I faced today working with AX Retail Channel Integration.
When trying to perform a Full sync for the Job-1070 for a Channel data Group, I encountered the below error.
Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessTargetRequestHeaderException: ProcessTargetRequestHeader failed to execute all delete requests ---> Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessDeleteRequestException: Delete request on table:[ax].[ACCOUNTANT_BR] failed to execute. ---> Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.RunException: Query: DELETE FROM [ax].[ACCOUNTANT_BR] ;; filterNameList: ;filterValueList: ; ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
Also later when I checked the eventvwr, I could see that this error was existing from the time I have set up the Channel Database.
Usually we won't face this kind of issues if we are working on a Single-box setup (or) Microsoft provided Virtual machines, as the permissions need not be taken care separately. However such issues are more frequent if you setup multiple-box environments and one main reason is that not every minute detail are documented.
However, the solution for this issue in my scenario was that "to provide the user (Async client service account) the permissions to run 'xp_cmdshell stored procedure'."
This can be done in two ways,
When trying to perform a Full sync for the Job-1070 for a Channel data Group, I encountered the below error.
Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessTargetRequestHeaderException: ProcessTargetRequestHeader failed to execute all delete requests ---> Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessDeleteRequestException: Delete request on table:[ax].[ACCOUNTANT_BR] failed to execute. ---> Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.RunException: Query: DELETE FROM [ax].[ACCOUNTANT_BR] ;; filterNameList: ;filterValueList: ; ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
Also later when I checked the eventvwr, I could see that this error was existing from the time I have set up the Channel Database.
Usually we won't face this kind of issues if we are working on a Single-box setup (or) Microsoft provided Virtual machines, as the permissions need not be taken care separately. However such issues are more frequent if you setup multiple-box environments and one main reason is that not every minute detail are documented.
However, the solution for this issue in my scenario was that "to provide the user (Async client service account) the permissions to run 'xp_cmdshell stored procedure'."
This can be done in two ways,
- Identify the service account used for running Async client windows service. And add that particular account as a SysAdmin in the Channel database. More details about CDX Async client in here.
- Grant access to that particular account to run 'xp_cmdshell' extended stored procedure. More details in here.
Hope this helps. Please do leave your comments, observations and any other solutions. Thanks.