Management Reporter Integration failed with error “The REFERENCES permission was denied on the object ‘DATAAREA’, database ‘XXX_XXX_XXX’, schema ‘dbo’.Rolling back transaction “ in AX2012R3CU10.

MR1

Solution: As per the error, the account which we used to run MR services has no ‘References ’ permission on DATAAREA table, on transaction database. To overcome this error we have to provide required rights as per the below screen.

MR2

Connect to SQL Server –> Open SQL Management Studio –> Expand ‘database’–>Expand ‘Security’ –> Select ‘MR Service account’ –> Click on ‘Securables’ –>Select ‘DATAAREA’ and ‘Reference’ Tick Mark on ‘Grant ’ –> Click ‘Ok’ .

Now go to ‘Configuration console’ stop and Start Integration, integration will finish without any errors.

Thanks for reading my blog posting. If you have any questions/suggestions, please do comments below. Click on follow button and enter your email address to get latest updates from the blog.

“Yours, Ravi Theja Madisetty”

 

9 comments

  1. Quick Workaround: Providing ‘System Administrator’ role in AX for MR integration account, will solve this issue.

  2. Which AD account do we grant the reference on DATAAREA Table in the AX Transaction DB? The AX Integration account or the MR service account running the MR Application and Process services?

    • MR Integration account.
      My suggestion is to assign System Administrator and System user roles to MR intergration account in AX, this will resolve your issue.

      • I am trying to integrate MR 2012 CU14 to DAX 2012 R2 RTM. I’ve granted the MR Integration user in AX both Security Admin and System Administrator AX Role but I am still getting the error. After granting the MR Integration user Reference permission for Table DATAAREA on the AX Transaction DB, the error went away. Another error appeared, requesting reference permission for Table VIRTUALAREADATALIST and DIRPARTYTABLE. I granted them and the error went away. Now it is requesting for create view permission. I am feeling this is not the right way to resolve these issues. What are the actual rights required. I don’t see any such permission requirements in the MR installation and Data integration guides. What are your thoughts on this? Thanks.

        • You can try by giving db_reader rights on tranasaction db and MR database.

          Make sure, MR integration acoount have system administartor and system user roles in AX. Not required Security admin role.

          Please refer below link for more information :

          http://download.microsoft.com/download/7/8/A/78A3710A-9D72-4908-B302-CD54B5130096/DynAXDataProvInstGuide.pdf

          • Referring to User permissions in Microsoft Dynamics AX 2012 on page 9 of DynAXDataProvInstGuide, the requirement is to grant the MR Integration user Security Administrator role in AX but not System Administrator.

            User account permissions in Microsoft Dynamics AX 2012 must be set up before you can configure the data integration. The user account that is used to set up the integration must meet the following requirements:
            – Be assigned to the Security Administrator role in Microsoft Dynamics AX.
            – Be assigned to the db_datareader role in the Microsoft Dynamics AX 2012 database and model database (minimum).
            Note: In Cumulative Update 7 and newer, Management Reporter 2012 automatically grants database permissions to the integration user that is selected during installation.
            – Be granted the View Change Tracking permission to read from the Change Tracking tables.

            I’ve already granted the MR Integration account db_datareader rights to both AX Transaction and model DB before setting up the MR integration.

          • Okay. If the issue still not resolved, delete MR datamart DB, recreate and enable integration.

          • After granting REFERENCES permission to DATAAREA, VIRTUALAREADATALIST and DIRPARTYTABLE Tables and grant View rights to AX Transaction SQL DB on the MR Integration account, the MR integrated was successful.

          • Below content copied from Technet :

            Management Reporter Integration user account (Optional) –

            The account that is used to run integrations between Management Reporter and Microsoft DynamicsAX.

            The account must have read permission and view change tracking permission on the Mircosoft Dynamics AX transaction database and model database.

            Setup will add the account as a user in Microsoft Dynamics AX, and will assign the user to the System administartor security role.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: