Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Making Adjustments to SQL Server Reporting Services Configuration Files


By:   |   Read Comments (6)   |   Related Tips: > Reporting Services Configuration

Problem

What configuration files are included with SSRS and how should I modify them?

Solution

Most often when using Report Server, the Report Services Configuration Manager is used to make setting changes; however there are times when making direct modification to the configuration files is either the only way or the most efficient way to persist values across multiple servers on a Report Server farm. Of course, with any such privilege comes a very big responsibility; as such, please be warned that directly modifying the configuration files can result in either settings resorting to default values or the Report Server erroring out completely. In order to prevent such issues, it is imperative to check the range of valid values for any item being changed.

For Report Server, three distinct configuration files exists which interact directly with the report server:  

  • RSReportServer.config, 
  • ReportingServicesService.exe.config, and 
  • RSReportDesigner.config.

RSReportServer.config

The RSReportServer.config handles the bulk of the configuration settings used by the Report Server and Report Manager. The screen print in the below illustration shows the first few lines of the RSReportServer.config configuration file. 

Config File

You will quickly note this file (and the other two files we will discuss) are in XML format. Be sure to use a plain text editor to make any changes to the files as other tools may add or introduce extraneous characters into the XML file. Also, even though you can make changes to most any field, there are some changes that will immediately cause issues and require the field to be updated via specific tool; these type of fields include those that are encrypted such as the DSN is in the above screen print.  

Other fields, such as the MaxActiveReqForOneUse is not encrypted and is available in plain text. This value could be changed with care. Once you do make a change and depending on the value changed, the change will take effect most likely the next time the application domain recycles or when the SSRS Service restarts.  Also be sure to note that the values used in the config file are case sensitive and in some instances, in order to impact some settings, several lines of keys, values, or attributes may need to be added. 

One common change that is often made to the RSReportServer.config file is to add or modify the rendering information; these changes will allow for alternate rendering extensions to be added, such as, a pdf in landscape format. Adding the below XML to the Render section of the config file adds a second pdf export option to SSRS. 

<Extension Name="PDF-Landscape" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRenderer,Microsoft.ReportingServices.ImageRendering">
    <OverrideNames>
        <Name Language="en-US">PDF in Landscape Mode</Name>
    </OverrideNames>
    <Configuration>
        <DeviceInfo>
            <OutputFormat>PDF</OutputFormat>
            <PageHeight>8.5in</PageHeight>
            <PageWidth>11in</PageWidth>
        </DeviceInfo>
    </Configuration>
</Extension>

The  below screen prints shows the results before adding this new rendering option on the left side and then the result after adding this new extension on the right side. In order for the changes to take effect, the SSRS service must be restarted.

pdf

pdf new

However, if the updates made to the file are done incorrectly, the following screen print displays one of the errors which could result when attempting to restart the Report Server Service (more on this item later in the tip). In some instances, when an incorrect value is used, the report server will actually function, however, a "default" value is used. Unfortunately, other times the Report Server will not start at all. Either situation is not beneficial and as such care must be taken when making these manual updates.

Restart Error

Another normal adjustment made manually pertains to the adjusting the various memory and recycle thresholds used by Reporting Service.

Memory Settings

The following link covers the details about making setting changes to the memory:  http://technet.microsoft.com/en-us/library/ms159206.aspx This second link, http://www.mssqltips.com/sqlservertip/2735/prevent-sql-server-reporting-services-slow-startup/ explains the RecycleTime setting. This second setting can alleviate slow start up time after the report server has been idle for a long period of time such as overnight.

ReportingServicesService.exe.config

The RSReportServer.config file is the most likely configuration file to be changed by an SSRS administrator; however nearly as likely to be updated is the ReportingServicesService.exe.config, especially when troubleshooting.

This file controls the settings for the log and trace files generated by Reporting Services. You can set the name prefixes used by log files along with the level of tracing wanted. In particular, the DefaultTraceSwitch property, as seen in the below screen print, allows you to adjust the level of logging that SSRS uses; 0 is no tracing whereas 4 is full verbose tracing. By default, the trace level is set to 3; care should be taken when altering this setting to 4, due to the amount of data which potentially could be written which would in turn slow SSRS.

Service Configuration

Furthermore, reviewing the remaining parts of the above screen print, the RSTrace section allows you to manipulate the:

  • FileName - trace file name prefix--In the above screen print, the file name prefix was changed to: ReportServerServiceLOG_.
  • FileSizeLimitMB - trace file size limit
  • KeepFilesForDays - number of days to keep trace file
  • TraceListeners - output target for tracing content
The remaining RSTrace tags should not be changed. As you can see in the below screen print, after making the change for the log file name prefix and restarting the SSRS service, we now have a new set of files. The screen print also displays the first several lines of the trace file.

Log Files

Log File Details

As an example of using the ReportingServicesService.exe.config settings as a troubleshooting tool, I purposely added an extra "/" to the RSReportServer.config file and then attempted to restart the SSRS service. The Windows services displays the restart error illustrated earlier and repeated below. Of course this message is not the least bit helpful other than knowing that the service will not restart. On the other hand, by turning on logging in the ReportingServicesService.exe.config file, we can see in the second illustration, that we have an extra "/" in the configuration file. Thus, if you are having trouble starting SSRS or are getting an error when navigating to the SSRS site, turning on tracing would be an excellent first step in trouble shooting the error. 

restart error

Error in Config File

RSReportDesigner.config

The third configuration file, RSReportDesigner.config, has a more distinct purpose than the previous two discussed config files. This file maintains the setting for processing and rendering of SSRS extensions while designing and previewing a report in the designer; thus, as opposed to the other two files which are integral parts of the actual report server applications, this file affects only how the report designer (SQL Server Data Tool or SSDT for SQL 2012) runs, processes, and renders a report request. Furthermore, in contrast to the other two configuration files discussed, whose location is within the SQL Server file directories, this configuration file resides within the Visual Studio directory structure. In the below screen print, first, an additional pdf render option is added, and second, the Word export visibility option is set to false. 

RS Designer Change

This change is illustrated in below results.

RS Designer Change

One other helpful setting in the designer config file that is often changes center around the CacheDatafForPreview option; this value determines whether during report preview the design tool (SSDT) caches data files for later use or does not cache these files.

Cache Data

Adjusting this setting to false means that that no SSRS data cache files (xxx.rdl.data files) will be generated. Depending on the size of your datasets, this change may be advantageous; of course the advantage of modifying this setting is also dependent on whether you are updating the data and / or changing your datasets and whether you want to see the updated data or not. 

Conclusion

The configuration files used for reporting services allow you to make changes to several settings within a report server. Some of the settings can only be updated by manually altering the values in the configuration files; furthermore, in a large scale multi server environment, making changes to the configuration files directly can ease the administrative burden of posting changes to multiple servers. However, the utmost care must be yielded to altering the files as one stray character can cause your report server to revert back to a default value at the best or completely not function at its worst.

Next Steps


Last Update:






About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, May 18, 2016 - 10:06:43 AM - Scott Murray Back To Top

Alex... what is SSI?


Wednesday, May 18, 2016 - 9:43:07 AM - Alex Back To Top

Hi Scott,

is it possible to configure SSl for SSRS 2012 only in the Config Files.

I have to automate it without using the GUI.

 

regards Alex


Thursday, February 05, 2015 - 7:48:04 AM - Scott Back To Top

Sony....

normally...

C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer
Unless you changed the install path.



Thursday, February 05, 2015 - 7:27:56 AM - Sony Back To Top

Where the config files are placed ?


Tuesday, July 22, 2014 - 12:51:18 PM - Scott Back To Top

SharePoint Integrated mode uses different configurations.

 


Tuesday, July 22, 2014 - 12:27:18 PM - Rick Fitch Back To Top

Are these same config files used in a Sharepoint Integrated environment?


Learn more about SQL Server tools