While SQL Server Reporting Services (SSRS) platform is not difficult to learn and work with, it is still a fairly complex technology. Successful utilization of SSRS requires a combination of database, administration, report building and data analysis skills. Such a combination of expertise is often hard to put together, especially in smaller companies where one person might wear many hats.
As a consultant, I have seen several SQL Server Reporting Services deployments that could have benefited from a few simple SSRS best practices. Here are a few.
Back up the encryption key.
SSRS uses encryption to protect sensitive data in its configuration. Things like connection strings and passwords are stored in the back-end ReportServer database and in the configuration files. They are encrypted using an encryption key that’s stored in SSRS. If you move SSRS to another server, you need to use the same encryption key to decrypt all encrypted data. Therefore, proper encryption key management is extremely important.
When you install SSRS, the first thing you should do is use the Reporting Services Configuration Manager and back up the encryption key to a password-protected file. Keep a copy of this key file on the SSRS server and also in a safe spot somewhere on the network. If you ever need to migrate SSRS to another server, you can use the same configuration manager to restore the key from the original server. Otherwise, you will have to manually re-create all your data sources and other encrypted content. That’s not something you want to do, especially if your SSRS server is not functional and you are quickly trying to bring up SSRS on another server. Even though Microsoft has emphasized the importance of keeping a backup of the encryption key, I still sometimes find myself at a client site and discover that the key isn’t backed up.
Use Windows Active Directory groups to control security.
Systems administrators have long been following the practice of creating Windows groups and granting privileges to the group instead of assigning privileges to individual user accounts. This practice makes a lot of sense, since you can easily add or remove users from a group and make your security management much easier. But I don’t see this practice as widely used among developers and database administrators. I’ve seen many SSRS installations where whoever was managing privileges assigned individuals access to reports or report folders instead of creating groups like Marketing or Management to simplify administration.
Use report folders to control security.
Just as it makes sense to utilize Windows groups instead of user accounts, you’ll gain a similar advantage by managing security at the folder level. Group your reports into logical groups, place them in a report folder and then assign privileges to the folder rather than to individual reports. SSRS also allows you to cascade privileges to the subfolders so you can design a hierarchy of privileges in which higher privilege groups can view all folders, while other groups can view only reports closer to the root folder.
Use saved authentication when configuring report data sources.
While using Windows Authentication is often the most recommended option, it doesn’t always work well in SSRS. If you configure a report to use Windows Authentication to connect to a SQL Server database, it only works if the database is on the same server as the SSRS server. But if you need to connect to another physical server, a “double-hop” authentication is needed — one hop between the browser and SSRS and the other hop between SSRS and the database server. I had to troubleshoot this issue when a report was working while the user was using a browser on the SSRS server but stopped working when SSRS was accessed from another machine, resulting in double-hop authentication. Theoretically, double-hop authentication should work if you properly configure the authentication protocol Kerberos on the network, but I haven’t seen much success in that area. You are better off configuring a data source to use a SQL Authentication login, or specify a Windows account that should be used to connect to SQL Server.
Back up the SSRS back-end databases.
SSRS uses ReportServer and ReportServerTempDB databases, and you should back those up to a location other than the SQL Server machine they run on. You will need them if your server dies and you need to re-create the SSRS environment; otherwise you will have to redeploy all your reports and redo all configurations. I’ve seen companies making backups to a local drive, but if you lose the whole machine, those will do you no good.
Practice SSRS migration to another server.
Migrating SSRS to another server is relatively simple: Back up the ReportServer and ReportServerTempDB databases and the encryption keys. Next, restore them on another SQL Server and configure the new SSRS server to use them. Once you restore the encryption key, your new SSRS environment should be identical. This is a good exercise, because if your SSRS server ever dies, you will be able to bring a new server online much faster.
Keep all reports under source control.
Very often, a company has several people developing reports and deploying them to the server without having a central location to store the files and keep them versioned. Developers are used to working with source control software such as SourceSafe or SVN, but business users are not used to them. Since they often build and deploy reports, they should use the same procedure and discipline to check new reports into a source control and check them out if they need to make modifications. Aside from having your reports in a central place, where they are versioned and backed up, you’ll find it much easier to build a new SSRS environment, pulling the reports from source control as opposed to collecting the report definition files from the individuals who developed each report.
While the SSRS best practices in this article are intuitive and easy to implement, not every company has them in place. I highly recommend that you check your SSRS configuration and make the recommended configurations. In addition, remember to back up the keys, the databases and practice migrating to another server. After all that work, your SSRS administration will require less time, and you will be better prepared to deal with an unexpected migration to a new SSRS server.
Windows Management and Scripting 