Configuring Report Services for Large Active Directory Environments

Configuration issues can significantly affect the performance of synchronizing Active Directory information and report queries and generation. This section describes additional considerations for deploying Delinea Report Services successfully in a large Active Directory environment.

Memory Recommendations and Requirements for Large Active Directory Environments

Domain Controller Memory

Symptoms

The domain controller runs slower or stops responding.

You can use the Performance monitor tool to evaluate if the system is operating within adequate capacity thresholds.

For details, see: http://social.technet.microsoft.com/wiki/contents/articles/14355.capacity-planning-for-active-directory-domain-services.aspx#Monitoring_For_Compliance_With_Capacity_Planning_Goals

Resolution

Ensure the system has a sufficient amount of RAM. The minimum amount of RAM should be the sum of:

• Active Directory database size (such as the size of the C:\Windows\NTDS\ folder)
• Total SYSVOL size (such as the size of the C:\Windows\SYSVOL folder)
• Operating system recommended amount of RAM
• Vendor recommendations for the agents (antivirus, monitoring, backup, and so on)
• Additional amount of RAM to accommodate growth over the lifetime of the server.

For details, see: http://social.technet.microsoft.com/wiki/contents/articles/14355.capacity-planning-for-active-directory-domain-services.aspx

Windows Memory Requirements

Here are the memory requirements for different versions of Windows:

Windows version	Minimum memory required
Windows 2008, 2008 R2	512 MB minimum 2 GB or more is recommended
Windows 2012, 2012 R2	512 MB minimum
Windows 7, 8, 8.1, 10	2 GB minimum for 64-bit systems

References

http://windows.microsoft.com/en-us/windows7/products/system-requirements

http://windows.microsoft.com/en-US/windows-8/system-requirements

http://www.microsoft.com/en-us/windows/windows-10-specifications

https://technet.microsoft.com/en-us/windowsserver/bb414778.aspx

https://technet.microsoft.com/en-us/library/dn303418.aspx

Sql Server Memory

Symptoms

• Delinea Report Services fails to rebuild or refresh a snapshot because of insufficient system memory or an out of memory error.
• You cannot open reports in SSRS because of insufficient system memory or an out of memory error.

Resolution

Ensure that your SQL Server deployment has sufficient memory. Different versions of SQL Server have different memory requirements. For details, please see:

https://msdn.microsoft.com/en-us/library/ms143506.aspx

In addition to Microsoft’s recommended memory requirement for SQL Server, an additional amount of memory is required for SQL Server in order to rebuild/refresh snapshot data and render the report successfully.

For more information, see Configuration Recommendations for Large Active Directory Environments.

Configuration Recommendations for Large Active Directory Environments

The major factor of evaluating the configuration requirements for SQL Server is the total number of effective users who can access the computers that are joined to zone in the Active Directory environment. You can estimate the total number of effective users by multiplying the number of computers joined to the zone by the average number of users who can access the computer.

Below lists the recommended configurations for SQL Server for some sample Active Directory environments.

Active Directory environment Sample #1:

Number of computers joined to a zone	1000
Average number of users who can access the computer	500
Total number of effective users	500 * 1000= 500,000
90% of user profiles and role assignments are explicitly defined at the zone level

Active Directory environment Sample #1 configuration recommendations :

SQL Server edition	SQL Server Express Edition with Advanced Services
SQL Server memory	8 GB
SQL Server disk space	30 GB

Active Directory environment Sample #2:

Number of computers joined to a zone	5,000
Average number of users who can access the computer	3,000
Total number of effective users	3,000 * 5,000 = 15,000,000
90% of user profiles and role assignments are explicitly defined at the zone level

Active Directory environment Sample #2 configuration recommendations :

SQL Server edition	SQL Server Standard Edition or above
SQL Server memory	64 GB
SQL Server disk space	80 GB

Setting the Maximum Server Memory for SQL Server

To prevent Microsoft SQL Server from consuming too much memory, you can use the following formula to determine the recommended maximum server memory:

• Reserve 4GB from the first 16GB of RAM and then 1GB from each additional 8GB of RAM for the operating system and other applications.
• Configure the remaining memory as the maximum server memory allocated for the Microsoft SQL Server buffer pool.

For example, if the computer hosting the Microsoft SQL Server instance has 32GB of total physical memory, you would reserve 4GB (from first 16 GB) + 1GB (from next 8 GB) + 1 GB (from next 8 GB) for the operating system, then set the Maximum server memory for Microsoft SQL Server to 26GB (32GB – 4GB – 1GB – 1GB = 26).

Reference:

https://msdn.microsoft.com/en-us/library/ms178067(v=sql.105).aspx

To set the maximum server memory for SQL Server:

1. Open the SQL Server Management Studio, enter the SQL Server properties:

2. Set the maximum server memory (in MB).

   

Using Report Filters to Limit the Output Data of a Report

Symptoms

In large Active Directory environments, the following reports can take too long to render because they generate a huge volume of output:

• Authorization Report
• Classic Zone – User Privileged Command Rights Report
• Classic Zone – User Role Assignment Report
• Hierarchical Zone - Computer Role Effective Assignments Report (UNIX)
• Hierarchical Zone - Computer Role Effective Assignments Report (Windows)
• Hierarchical Zone - Effective Audit Level Report
• Hierarchical Zone - Effective Rights Report
• Hierarchical Zone - Effective Role Report
• Hierarchical Zone - Users Report
• Hierarchical Zone - Zone Effective Assignments Report (UNIX)
• Hierarchical Zone - Zone Effective Assignments Report (Windows)
• All PCI reports
• All SOX reports

Resolution

You can use report filters to limit the report to only list data for specific zone types and zones in a specific domain. This can reduce the amount of data output from the report and the report will take less time to render.

If you are opening the PCI and SOX reports, you can use the Zone Type filter to limit the reports to only list data for Classic zones or Hierarchical zones.

For all reports, you can use the Zone Domain filter to limit the reports to only list data for zones in a specific domain. By default, the Zone Domain filter of all the reports is set to the first zone domain.

By default, reports are set to run automatically when you open the report. If you prefer to set the reports to not run automatically upon opening, do the following. You must have manage report permission in order to configure the report.

To configure a report to not run automatically when you open the report:

1. In the list of reports in the web browser, locate the desired report.

2. Move your mouse pointer over the report to open the report context menu.

3. From the context menu, select Manage.

   

4. Select the Parameters page.

   Notice that ‘Has Default’ is selected for all parameters.

   

5. Deselect the ‘Has Default’ setting for any one parameter.

6. Click Apply to save the changes.

   

7. Open the report.

   The report does not run automatically. You can specify the filter values and click “View Report” button to run the report.

   

Increasing the Time-Out Value for Rebuild/Refresh Data Operations

Delinea Report Services invokes multiple database operations when it refreshes and rebuilds its cache of information stored in Active Directory. These database operations can be time-consuming in a large Active Directory environment. If any such database operation cannot be completed within a certain time period, the Delinea Report Services control panel will show that the Refresh/Rebuild process failed.

Symptom

When Delinea Report Services perform a snapshot rebuilding or refreshing and the amount of the monitored data is too large to be processed within the time-out period, this error will occur:

A database operation error occurred. Please contact your administrator to make sure the remote database is accessible and working properly. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Resolution

You can change the time-out value (3,600 seconds by default) for that time period by performing the following steps:

1. Open the registry editor and then locate the key ‘SQLCmdTimeout’ under HKLM\Software\Centrify\Report Services\Service. If you cannot find it under the path, create one with the same name and as 'DWORD' type.

2. Set to 'SQLCmdTimeout' to a large enough value (unit in second) so that the rebuild/refresh/computing can be finished within the time period.

   Set the SQLCmdTimeout to 0 (ZERO) mean no time-out. Customer should contact Delinea Technical Support first before changing SQLCmdTimeout to 0.

Increasing the Time-Out Values for Microsoft SQL Server Reporting Services

Consider increasing the following SSRS configuration parameter values so that the large reports can be opened successfully.

Report Execution Time-out

A report execution time-out value is the maximum number of seconds that report processing can continue before it is stopped. This value is defined at the system level. You can vary this setting for individual reports.

Symptoms

For example, you can run a report that has underlying queries that cannot be completed within the time-out period. The following error will be shown on the Report Manager like this:

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand)
A severe error occurred on the current command. The results, if any, should be discarded. Operation cancelled by user.

Resolution

Increase the Report execution time-out value. For details, see https://msdn.microsoft.com/en-us/library/ms155782.aspx.

HTTP Runtime Execution Timeout

Symptoms

You cannot open the report and you get the following error instead. This error generally occurs when the HTTP runtime execution timeout is too short.

The remote server returned an error: (500) Internal Server Error.

Resolution

1. Open the Report Server’s Web.config file, which is usually in this location:

   <Drive>:\Program Files\Microsoft SQL Server\MSRS<version number>.<instance name>\Reporting Services\ReportServer

2. Locate the HttpRuntime parameter and alter the value. If it doesn't exist, you will have to create it within the section.

   

   The default value is 9000, and the value is in the seconds. The maximum value is 922337203685.

3. Increase the executionTimeout value to allow the report to be rendered.

Increasing the ReceiveTimeOut Value for Internet Explorer

Symptoms

The following error is shown when you try to open a report:

An unknown error occurred while processing the request on the server. The status code returned from the server was: 12002

Resolution

You can change the ReceiveTimeout setting for Internet Explorer using the following steps:

1. Start the Windows Registry Editor.

2. Locate the following subkey:

   HKEY_CURRENT_USER\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings

3. In this subkey, add a ReceiveTimeout DWORD entry that has a value of (<number of seconds>)*1000.

   For example, if you want the time-out duration to be 120 minutes, set the value of the ReceiveTimeout entry to 7200000 (<120*60>*1000).

4. Restart the computer.

Using a URL to Export Report Data to CSV

Symptoms

The underlying queries in some reports take a long time to execute and you may get the following errors when opening reports:

The remote server returned an error: (500) Internal Server Error.

Resolution

Besides using the report filters to make the report take less time to execute as described in earlier section, you can export the report to CSV by using a URL. In addition, you can skip exporting the chart data for the following reports:

• PCI – Login Summary Report
• PCI – Right Summary Report
• SOX – Login Summary Report
• SOX – Right Summary Report

To configure the report URL to export to CSV and skip the chart data in the exported file:

1. Compose the URL in the following format:

   http://\<hostname\>:\<port\>/ReportServer_\<instancename\>?\<report path\>&rs:Command=Render&rs:Format=CSV&pZoneDomainId=-1&SkipChartData=True

   For example:

   This is a URL to export the PCI – Login Summary report:

   http://win2012r2/ReportServer_CENTRIFYSUITE?%2fcentrify+Report+Services%2fAttestation+Reports%2fPCI+Reports%2fPCI+-+Login+Summary+Report&rs:Command=Render&rs:Format=CSV&SkipChartData=True&pZoneDomainId=-1

   This is a URL to export the PCI – Right Summary report:

   http://win2012r2/ReportServer_CENTRIFYSUITE?%2fcentrify+Report+Services%2fAttestation+Reports%2fPCI+Reports%2fPCI+-+Right+Summary+Report&rs:Command=Render&rs:Format=CSV&SkipChartData=True&pZoneDomainId=-1

2. Access the URL in Internet Explorer.

3. Save the exported CSV file.

References

https://msdn.microsoft.com/en-us//library/ms153586.aspx

https://msdn.microsoft.com/en-us/library/ms159261.aspx

Creating the Report Subscription for CSV Export

This section shows how to use the SQL Server Reporting Services (SSRS) subscription feature to export report data to CSV regularly.

Prerequisites

• Please check whether your SQL Server edition supports the reporting subscription feature.

  https://msdn.microsoft.com/en-us/library/cc645993(v=sql.100).aspx

• SQL Server Agent is already installed and running.

Configuring The Report Data Source For Subscriptions

To configure a report subscription in SSRS for CSV export and skip the chart data in the export:

1. Open Delinea Report Services.

2. Click ReportDataSource to open the report data source properties page.

3. Configure the report data source to store connection credentials in the report server:

   1. For the connection method, select Credentials stored securely in the report server.

   2. Enter the login user name and password.

   3. Select Use as Windows credentials when connecting to the data source.

   4. The following screenshot is an example of the connection settings configuration:

      

4. Secure access to the reports and the report data by adding or editing role assignments for the report folder.

   1. Open the Security page for the report folder ‘Access Manager Reports’ and ‘Attestation Reports’.

      1. Here you can view, add, edit, or delete role assignments for the report folder.

      2. The data source uses stored credentials, which means that users who are able to view the reports would be able to read the report data. To avoid this potential risk, you can define role-based security for reports in the Security page, as shown below.

         

   2. Delete the default role assignment that assigns the Browser role to NT AUTHORITY\Authenticated Users to remove report read access to all authenticated users.

   3. In the report folder’s Security page, click New Role Assignment.

   4. Enter the users or groups who can access the reports.

   5. Select one or more roles to assign to the specified user(s).

      For example, if you want the specified users to only view the report, select the Browser role.

   6. Click OK to save the changes.

      

Creating A CSV Report Subscription

To configure a report subscription in SSRS for CSV export and skip the chart data in the export:

1. In the list of reports, select the report that you want to export to CSV.

2. Click the context menu and click Subscribe.

   

3. In the Subscription page, set the options according to the following screenshot.

   1. To specify when the scheduled report runs, click Select Schedule.

   2. When you specify the file path, the path must conform to the Uniform Naming Convention format.

      

   3. In the lower area of the subscription page, set the Zone domain parameter to ALL in order to export report data for all zone domains.

      

4. After setting the options, click OK to create this subscription.

Skipping Chart Data From CSV Report Subscriptions

You can skip exporting the chart data to CSV for the following reports:

• PCI – Login Summary Report
• PCI – Right Summary Report
• SOX – Login Summary Report
• SOX – Right Summary Report

To configure a report subscription in SSRS for CSV export and skip the chart data in the export:

1. Open the report subscription. (From the report’s context menu, click Manage, and then click the Subscription page.)

2. In the lower area of the subscription page, set the SkipChartData parameter to True.

   

3. After setting the options, click OK to save the subscription.