Advertisements
HEADLINES

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”

 

Advertisements

9 Comments on 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.

  1. 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.

    Like

  2. Eric Lim // 02/04/2016 at 5:39 AM //

    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.

    Liked by 1 person

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

    Like

  4. Eric Lim // 01/04/2016 at 11:54 PM //

    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.

    Like

  5. 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

    Like

  6. Eric Lim // 01/04/2016 at 11:33 PM //

    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.

    Liked by 1 person

  7. 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.

    Like

  8. Eric Lim // 01/04/2016 at 11:00 PM //

    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?

    Liked by 1 person

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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s