Calling SQL Server Reporting Services from PHP - authentication problem

Question

We need to use SQL Server Reporting Services in a website with server-side php and php authentication. At the moment we have two separate logins/authentication – the php and the SQL Server Reporting Services one (which I believe uses something similar to ASP.NET authentication). We need to have only the php login and we also need the reports to be secure (i.e. reports can’t be open to anonymous users).

Some of our reports only change at set times of the day, so we can generate pdfs programmatically, store them in a database or somewhere, and have the php retrieve them when the user makes a request. This is pretty straightforward.

The problem arises with one report which is changing all the time, and we’d therefore need to generate the pdf on-the-fly from the database whenever the user makes a request. This would require the php to call the reporting services directly (currently we have the reporting services link embedded in the webpage, the user clicks on this and then has to enter the secondary reporting services/windows-type login).

To get around the problem, I’m thinking we could wrap the call to reporting services in an executable, and have the php call the executable - which then spits out a pdf which php passes back to the user. We may even be able to wrap the reporting services into a CGI that is only accessible from the webserver hosting the php. Has anyone tried anything like this before? Would it work? Would it only require PHP authentication and still be secure???

Reporting Services could be either 2005 or 2008.

Answer 1

I have a similar situation, which for now I just take care of by distributing the second, reporting services password, to people who need it. In my case my web app authenticates against our Novell LDAP data, but there does not seem to be a way to pass that to the reporting services box. I have not got around to it yet, but my thinking was to create a guid or random string on clicking a report generating button, storing that in a table, adding that to the report request (querystring or post), and then putting a conditional in the sproc driving the report so that if the the string doesn't exist in the table, no data is returned. The strings can be deleted periodically. Kind of rube goldberg, but maybe easier than the executable wrap you mention and its the best I could think of short of trying to build my own ldap provider for reporting services, in which case the person would still have to do a second login.