Querying Active Directory From SSRS
Thanks to everyone who stopped out to the Tampa SQL BI user’s group last night to hear my presentation on Querying Active Directory From SSRS. Here is the link to download the Power Point. Click Here to download. Be sure to join us next month for Bryan Bennett’s presentation of Service Broker.
If you haven’t already, visit the Tampa SQL BI User’s Group site and register for updates to learn about everything our chapter will be doing in the coming months. Click Here.
Last week I explained how I came to use SSRS Reports as user interface. This week I’ll discuss how I was able to very quickly provide users with an administrative console to adjust the configuration of a web based application using only Reporting Services and SQL Server.
Before I begin, I should probably point out that what I’m about to describe is pretty far off the beaten path for Reporting Services. Security, usability and support are all legitimate concerns that would need to be justified before I would recommend anyone else taking this approach. However, given the right set of circumstances that I don’t believe are all that extraordinary, you might find this useful.
The first question you might have is, “Why would I want to do this? If I have SSRS installed in my environment, wouldn’t I also have IIS, ASP.Net, VB.Net and or C# available to create a proper UI?.” My answer is yes, you most likely would and those are far better tools. However, in my case the company did not allow anyone outside of the development group to make enhancements to the product and without a request from the business group, new features rarely saw the light of day, or even budget consideration.
Your next concern might be, “So you are telling me the connection I use for my reports is going to be inserting and updating data? You are crazy! Security would have my badge and laugh me out of the building.” Again, I would say you are probably correct. If your report users log into the Report Manager and have admin privileges, with this kind of access they have the potential to do some harm. In the cases I am about to describe though, I have exposed only read and execute SQL privileges to my report user connection and have complete control over stored procedures in the database. Yes, if a devious employee had access to upload an RDL AND knew the names and usage of stored procedures in the system, they could alter data in unintended ways.
Shortly after starting to work with SQL Server Reports (SSRS) and getting a feel for what they could do as singular presentations of summary and detailed information, I started to consider more elaborate systems presenting suites of reports and a customized user experience. Much of my experience with SSRS has been to present reports through the ASP.Net report viewer control, and that is what sparked my interest in using reports as a user interface.
At the onset of a typical assignment I would be presented with the existing work which would be a list of links to an ASP page that would render the report for a visitor. After proving my worth making some alterations and updates to the existing reports I’d be asked to add new reports to the list. This would go on for some time and became rather dry, creating one stand alone report at a time.