One of the new things to be done with DynamicsPerf 2.0 is setting up security for both local installs and especially remote installs using Linked Server.  In this blog, I will be covering the security setup that you will need to have for each type of installation.

LOCAL

A local install assumes that the DynamicsPerf database is installed in the same SQL instance as your Dynamics database.  For this scenario, it works like previous versions in that the SQL Server Agent service account will need to give rights to the DynamicsPerf database. A simple setup would be to give the SQL Server Agent account DBO rights on the DynamicsPerf database.

In addition, you will also need to give the AOS Service account permissions to the DynamicsPerf database.  This is necessary for the AOTEXPORT class to work correctly because it now directly inserts data into the DynamicsPerf database. The AOS Service account only needs DataReader and DataWriter permissions on the DynamicsPerf database.

Finally, if you are collecting SQL Server Reporting Services data and it is also local, the SQL Server Agent account will need to have DataReader rights to the ReportServer database.

REMOTE

A remote installation is when you install the DynamicsPerf database on a different SQL Server than where the Dynamics AX database resides.

There are several details to setting up security for this deployment model.  If you are using the same Domain Service Account for both SQL Servers, then it is only a matter of making sure the accounts have rights to the Database you are collecting from, and the DynamicsPerf database.  In addition, this account will need access to the SQL Server hosting the report server database.

If you are using different accounts on all of these servers, then the best option is to setup a Credential / Proxy account in SQL Server that can be used for the appropriate Dynperf SQL Jobs.  (Instructions for setting up a credential are further down this blog).

SQL Reporting Server

• If the ReportServer database is local to the DynamicsPerf database, you only need to make sure the SQL Server Agent service account has permissions to both databases.
• If the ReportServer database you want to collect data from is on another SQL Server, then you will need to setup a Linked Server to that database.
• If the remote server hosting the ReportServer database is running with the same service account as the server hosting DynamicsPerf then you just need to give that account access to both databases.
• If you are using different accounts on both of these servers, then the best option is to setup a Credential / Proxy account in SQL Server that can be used for the DYNPERF_CAPTURE_SSRS SQL Job.  (Instructions for setting up a credential are further down this blog).

Summary of permissions for all databases:

How to Setup a Security Credential in SQL Server

1. In Object Explorer, expand the Security folder.
2. Right-click the Credentials folder and select New Credential….
3. In the New Credential dialog box, in the Credential Name box, type a name for the credential.
4. In the Identity box, type the name of the account used for outgoing connections (when leaving the context of SQL Server). Typically, this will be a Windows user account, but the identity can be an account of another type. Alternately, click the ellipsis (…) to open the Select User or Group dialog box.
5. In the Password and Confirm password boxes, type the password of the account specified in the Identity box. If Identity is a Windows user account, this is the Windows password. The Password can be blank, if no password is required.
6. Select Use Encryption Provider to set the credential to be verified by an Extensible Key Management (EKM) Provider. For more information, see Extensible Key Management (EKM)
7. Click OK.

NOTE:  You will need to Setup one Credential for the DYNPERF_COLLECT_AOS_CONFIG , DYNPERF_CAPTURE_STATS, DYNPERF_CAPTURE_SSRS, for each of the jobs that will be collecting data remotely. You will need one credential for each remote Server you will be collecting data from.

How to Setup a SQL Server Agent Proxy

1. In Object Explorer, expand a server.
2. Expand SQL Server Agent.
3. Right-click Proxies and select New Proxy.
4. On the General page of the New Proxy Account dialog, specify the proxy name, credential name, and description for the new proxy. Note that you must create a credential first before you create a proxy if one is not already available. For more information about creating a credential, see How to: Create a Credential (SQL Server Management Studio) or CREATE CREDENTIAL (Transact-SQL).
5. Check the appropriate subsystem for this proxy: For DYNPERF_COLLECT_AOS_CONFIG job select ActiveX
6. On the Principals page, add or remove logins or roles to grant or remove access to the proxy account.

How to Assign Credential and Proxy setup to appropriate SQL Server Agent Jobs

1. In Object Explorer, expand a server.
2. Expand SQL Server Agent.
3. Expand Jobs
4. Right Click the job to update, DYNPERF_COLLECT_AOS_CONFIG , DYNPERF_CAPTURE_STATS, DYNPERF_CAPTURE_SSRS
5. Select Properties
6. Click Steps on the left side
7. Click the Edit button
8. Change the RUN AS drop down to the credential you setup for this job

How to setup permissions on each server and database per the grid listed previously

1. Open the Performance Analyzer 2.0 Installation solution.
2. Double Click script 8-Setup Security
3. Be sure to connect the session to the server you will be collecting data from
4. Update each of the following parameters with the appropriate values:
   1. SET @SERVER_TO_COLLECT = ‘your_server_here’
      SET @SERVICE_ACCOUNT = ‘DomainAcct’
      SET @DB_TO_COLLECT = ‘DynamicsProdDB’
      SET @REPORT_SERVER_DB = ‘ReportServer’
5. After setting these parameters Execute the script

This produces a script that can be run on each server:

• Server Hosting the database you are collecting performance data on such as Dynamics database.
• Server hosting the DynamicsPerf database
• Server hosting the ReportServer database (not the web service)

The script to update permissions on the server where you are collecting you Dynamics Database data will be noted as follows:

/*************************************************************************************
–RUN THESE SCRIPTS ON THE SERVER YOU ARE COLLECTIING DATA FROM
**************************************************************************************/
The script to update permissions on the server hosting your DynamicsPerf database will be noted as follows:

/***************************************************************************************************
–RUN THESE SCRIPTS ON YOUR DYNAMICSPERF DATABASE SERVER
****************************************************************************************************/

The script to update the permissions on the server hosting the SSRS ReportServer database will be noted as follows:

/********************************************************************
–RUN THESE SCRIPTS ON YOUR ReportServer DATABASE SERVER
********************************************************************/

Run the scripts for each section on the appropriate server.

After completing all of these steps in this blog, remote data collection should work correctly barring any other network on domain security polices that might prevent communication between the servers.