Configuring MR with a non-sa account

Updated April 24, 2017

To configure the Management Reporter data mart to work with a SQL account other than "sa", you will need to perform the following steps. The following steps are for a new data mart integration. If you wish to change an existing deployment, you would need to export your reports in Management Reporter, create a new ManagementReporter database and then import your reports.

Please read through all the steps and make a backup of the Management Reporter database before proceeding.

1. In Dynamics GP, create a new user that has “Power User” rights to all Company databases. This user will be used to connect to the Dynamics database in the MR Configuration Console.

a.  Log into GP as an administrator and go to Microsoft Dynamics GP | Tools | Setup | System | User.

b.  Enter a User ID and User Name, such as MRGP. Set the User Type to Full.

c.  Go to Microsoft Dynamics GP | Tools | Setup | System | User Access.

d.  Assign the new user access to all companies.

e.  Go to Microsoft Dynamics GP | Tools | Setup | System | User Security.

f.  Select each company from the drop down and assign the POWERUSER role. An Alternative/Modified Forums and Reports ID must also be assigned.

After the user has been created, you must do the following:

a. Log into SQL Management Studio as a SysAdmin user.
b. Expand Security | Login
c. Right-click on the user which was created in Dynamics GP and select Properties.
d. Click User Mapping.
e. Check the Dynamics database and each Company database. Under Database role membership, make sure that db_datareader is checked.
f. Click OK.

2. Create a SQL user. This user will be used to create the data mart database.

a. Log into SQL Management Studio as a SysAdmin user.
b. Expand Security | Login.

c. Right-click Login and select New Login.

d. Enter a Login Name, such as MRSQL.

e. Select SQL Server Authentication and enter a password.

f. Give the user the sysadmin server role.

3. Install the Management Reporter Server components and configure the MR services according to the install guide.

4. In the Management Reporter Configuration Console, click File | Configure. Select Dynamics GP Data Mart and then click Next.

5. Enter the required information to create a new Data Mart database.

In the Dynamics GP connection information section, enter the required information.

Database server - enter the server name where the GP databases resided.

User name and password - enter the user name and password you created in Step 1, for example MRGP.

Database – Select the Dynamics GP system database from the drop-down list.

In the Data mart connection information section, enter the required information.

Database server – enter the database server where you want the data mart database to reside.

User name and password - enter the user name and password you created in Step 2, for example MRSQL.

Database – This will default with ManagementReporterDM.  If you wish to change the name of the data mart database, you can do so.

6. Click Configure. When a message is displayed that the data integration has been successfully configured, click Close.

7. After the integration is configured, you must define which companies should be available in Management Reporter. For information on adding companies, see the Integration Guide for Microsoft Dynamics GP 

8. In the Configuration Console, select the data mart integration.

9. Click Reconfigure Change Tracking. Enter the user name and password for the sa user. The sa user is required to enable SQL Change Tracking. This would not need to be done again unless new companies were added or Analytical Accounting is activated at a later point.

10. Click Enable Integration.