December 10, 2004 • Vol.26 Issue 50
Page(s) 21 in print issue
Peter Blackburn and I gave a seminar recently on Reporting Services to an oil company in Louisiana. No, we didn't have to fly down to the bayou country; we simply set up a VPN and hosted a NetMeeting session. It was a lot cheaper than spending two or three days standing in lines, sitting in cramped seats, and sleeping in strange beds to give a two-hour talk. The company in question was very pleased but shocked as we pointed out some of the security gaps in Reporting Services. Apparently, most people who train them try to sell them something. Peter and I weren't really trying to sell a product (other than a few copies of our book). We were trying to make their experience with Reporting Services successful and, most of all, safe.
Granting & Denying Rights Is Essential
Before I go any further, I want to say that Reporting Services is no less secure than any application that runs using the end user's SSPI credentials. Consider that when a Windows user logs on to a domain, he supplies a username and password. These are validated against a domain controller or the system-used cached credentials if the domain controller can't be reached. This domain account can be (is usually) a member of one or more domain-managed groups that are granted specific rights by the system (domain) administrator.
For example, a junior clerk in the payroll department might be granted rights to view the salary information for everyone in the company but would not be granted rights to alter those values. However, the manager of the payroll department might be granted these rights as well as other "super-user" rights. Of course, the database administrator would have rights that (hopefully) no other user would have, not even the developers.
Is Helen Working For Your Company?
This is where Helen comes in. Ms. Troy, a junior clerk in the payroll department, apparently has an issue with the company (something about not being able to date Mr. Achilles). To get even, when she was given the task to use Reporting Services to create a payroll summary, she inserted a bit of SQL code into the query that runs just after the SELECT executed. Given her credentials, this SQL would not execute, but she knows that when her boss runs it, his credentials will execute the additional code, unbeknownst to him. This extra code will be permitted to do anything that the user running the report has rights to do. In this case, because her boss has rights to change the payroll values, this additional SQL was able to cut the salaries of everyone on her hit list and raise her own. After the report was run (and the damage done), she quietly changed the report DataSet query and removed the extra code. No one was the wiser.
Is this a flaw in Reporting Services? Nope. This same Trojan horse approach can be used by any program that "trusted" connection. Most books (including my own) and the Microsoft documentation suggest this is the best approach to use when establishing a connection to any DBMS. We also think this is a good idea when developing a report. However, before we deploy the report to the server (where others can execute it), we create a parallel DataSource (using the same name in the current scope) that uses specific SQL Server credentials. These credentials permit very limited access to the server and its data.
An Alternative To SSPI Security
So what's the alternative? Well, Peter and I suggest that one approach is to disable SSPI authentication on the Reporting Services database server. This was made a lot easier with Reporting Services SP1, but it's still not simple to do. However, it's essential that you do take this precaution. Peter and I explain the process in Chapter 5 of our Reporting Services book (see www.sqlreportingservices.net), which involves writing an RSS script to set the EnableIntegratedSecurity Boolean flag to False. Another somewhat easier approach is to create a trigger on the DataSource table that would prohibit developers or DBAs from creating DataSources that use integrated security.
Reporting Services is a great new tool to permit developers to build intelligent views of the data stored on your system. However, if you're not careful, you'll expose this data to everyone on the planet. It's not hard to set up the safeguards to prevent unwanted eyes accessing your data, but it does require that you take a few extra steps as you set up the server. Remember that most of the security problems your company will face are from inside the firewall. If you hear hammering and sawing down the hall, check to see if someone is building a big horse (or rabbit).