BinaryFunctions.com

While SQL Server Reporting Services (SSRS) platform is not hard to learn and work with, it is still a honestly complex equipment. Successful utilization of SSRS requires a combination of database, administration, report building and data analysis skills. Such a combination of expertise is regularly hard to place 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 insightful 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 go SSRS to another server, you need to use the same encryption key to decrypt all encrypted data. Therefore, proper encryption key management is extremely vital.

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-make 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 promptly 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 learn that the key isn’t backed up.

Use Windows Active Directory groups to control security.

Systems administrators have long been following the practice of making Windows groups and granting privileges to the group as a replacement for of assigning privileges to party user financial statement. This practice makes a lot of significance, since you can easily add or remove users from a group and make your security management much simpler. 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 as a replacement for of making groups like Marketing or Management to simplify administration.

Use report folders to control security.

Just as it makes significance to use Windows groups as a replacement for of user financial statement, you’ll gain a similar advantage by managing security at the folder level. Group your reports into logical groups, house them in a report folder and then assign privileges to the folder rather than to party 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 regularly 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 state 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-make the SSRS environment; otherwise you will have to redeploy all your reports and redo all configurations. I’ve seen companies building backups to a community drive, but if you lose the whole machine, those will do you no excellent.

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 excellent exercise, because if your SSRS server ever dies, you will be able to bring a new server online much quicker.

Keep all reports under source control.

Very regularly, a companionship 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 regularly 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 house, where they are versioned and backed up, you’ll find it much simpler to build a new SSRS environment, pulling the reports from source control as different to collecting the report definition files from the individuals who urban each report.

While the SSRS best practices in this article are intuitive and simple to apply, not every companionship has them in house. I highly recommend that you check your SSRS configuration and make the recommended configurations. In addendum, 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.

Tags: SSRS 2008