I was asked to provide a quiz question for the Beyond Relational folks so I came up with this:
What issues are exposed when using SSPI authentication? How does one avoid these issues?
There were lots of answers that almost universally said that using SSPI authentication was the way to go. A number of folks cataloged many of the problems associated with SSPI including having to implement Kerberos when using multiple hops. I’m no fan of Kerberos as it can make a fairly simple client/server or Reporting Services site unmanageable. But everyone missed the point. Consider this scenario:
Bob is a low-level report developer working for Acme Enterprises. They have a number of competitors that would love to see their client lists. Management has made sure that Bob, and other folks like him have few rights on the production database and have no rights to access the Customers database. While Bob can execute SELECT statements against the Parts database, he has no rights on Customers.
Bob’s manager Betty is really considering demoting him or letting him go due to past performance issues—and that incident with the pig a few weeks ago. Bob knows his situation with the company is probably hopeless, so he decides to try to get at the customer list—he knows he can sell it to Farkle Industries down the street. When Betty tells him to create another report on the parts inventory he sees his opportunity. He creates the report but adds a few lines of SQL to the SELECT query that the report processor executes—something to the effect of “GRANT BOB rights to access everything (especially the customer database)”. He knows that his domain account does not have rights to make permission changes, but he knows that Betty’s does. When Bob runs the report using SSPI authentication, it throws an exception but returns the rowset needed for the report. However, when Betty runs the report, the report processor uses her SSPI authentication credentials and the GRANT statement goes through. Bob is now able to do all the queries he wants to against the customer list. He leaves Acme fat and happy.
This is called a Trojan attack. It’s very easy to implement anywhere that SSPI authentication credentials are used. Because of this very real threat, we recommend that you never use SSPI authentication for reports—at least not against databases that have sensitive information.