William (Bill) Vaughn's Musings

December 24, 2004 • Vol.26 Issue 52
Page(s) 22 in print issue

I spent the middle part of November traveling and speaking, first in Las Vegas at the SQL Server Magazine-sponsored Connections conference and then at the VBUG conference held in Reading, United Kingdom. Peter Blackburn and his wife were kind enough to host me for a few days, and they treated me to a trip to the Lake District. Except for a few (dozen) scary moments on some (very) narrow icy roads at night (at breakneck speeds), the trip was fun, and we were able to catch up on a number of issues regarding our new book.

Peter was pretty frustrated as he has been fighting (almost literally) with Acer over his still-in-warranty Tablet PC. It went to smoke city several weeks ago, and trying to get support out of the local (U.K.) office was like Osama bin Laden trying to get a pardon from George Bush. When Peter called, he was kept interminably on hold—until they said they were "closed for lunch." He repeatedly tried email, but they suggested calling the support line (on his dime) or simply ignored him. He has tried to escalate the issue with management but to no avail. I expect Acer has lost a loyal customer and is likely to meet his solicitors.

The IBM Approach

When I got back to the United States, I also had some issues to resolve with my IBM laptop's hard drive. I went to the IBM site and discovered that they wanted me to fill out a service request form online. Within an hour, I revisited the site and noticed that the hard drive repair request had been "closed." I added a comment to the request asking why it was closed and waited. In the next day or so, I received a new hard drive but no instructions as to what to do with the old drive. I spent a few hours moving the data over and voila, my system started working better. To determine what to do with the old drive, I called IBM and got an American tech who assured me that the return instructions would arrive in the next few days. They did. Cool.

I also had to talk to another IBM tech support person last month. In this case, the experience was not as good. The person (clearly in India) was relatively clueless and simply wasted my time. Apparently, even IBM has felt the need to farm out some of its support to the third world.

Symantec & Its "Support" Staff

Because I was already distracted trying to get my laptop back up to fighting speed, I decided to buy a 7,500rpm drive for it. The problem arose when I tried to use PartitionMagic to resize the system image. It would not activate. For the next four days I spent too many hours on the phone with people at the Symantec support center somewhere in the hinterlands of India.

The folks there were courteous but clueless. They read questions from a script and each time I had to phonetically spell almost every answer. Yankee Echo Sierra: I have the current version. The biggest problem is that they lied to get rid of me. They lied when they told me they knew what was wrong and that they would email me a known solution to my problem. (No mail arrived.) They lied when they said they would send me a replacement CD. (Nothing came.) They lied when they said they would transfer me to their supervisor. (They hung up.)

I finally got frustrated and dug up the corporate office numbers and was connected (after several hours) with a Utah-ite who was as frustrated with the Indian support team as I was. It seems his staff has been decimated by exporting the support role overseas. He said he would send me a new CD. Should I believe him? At least I have his direct number (and he's only a long day's drive away) if he does not follow through.

Remember Your Customers

Every employee in your organization could be facing the same problems if they have to fend for themselves to support their own systems. Sure, the best IT organizations hire, train, and retrain their own support staff to minimize the countless wasted hours spent talking across the ocean to people who don't really care if your systems ever work. However, there are millions of people like me who do need to call the people who sold them the broken or defective hardware and software. These calls are expensive for both parties, but when it comes to choosing which company is going to supply my next piece of software or hardware, I'll vividly remember how hard (or easy) it was to get my problems solved. If you face these same issues, make sure the corporate office knows about it. Don't bother telling the folks in Bangalore. They aren't listening.

Posted by William Vaughn at 07:02 PM | Permalink | Comments (0)

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).

Posted by William Vaughn at 07:06 PM | Permalink | Comments (0)