August 17, 2010 at 3:40 pm
SSRS Gurus:
Two years ago, fresh out of PASS 2008, I came back to the office and deployed SSRS. I love it, and it works beautifully. Maybe too beautifully, actually. I’ve been tasked with researching the following prototype: a public-facing web app that has SSRS reports embedded in it.
My post here concerns security and authentication for this type of prototype using SSRS.
For simplicity’s sake, I’m going to pretend my web app is for a school. There will be three types of users who might log-in to the system: administrators, teachers, and students. There will be some shared reports that they can all see – the school’s list of rules, for example, but then for the rest of the report library, Administrators should see all reports, teachers should only see teacher reports, and students should only see student reports. Furthermore, when a teacher logs in, they need to retrieve things like their TeacherID, in order to pass it (their TeacherID) to a report as a parameter in order to filter properly. Similarly, when a student logs in, they need to retrieve their StudentID in order to pass it to a report as a parameter.
In my in-house deployment, I leveraged the Windows Security model. This was part of my explanation to higher-ups about why SSRS was so great – we already had the security mechanism in place to control access, the Windows Active Directory.
So now, I am trying to figure an alternative security/authentication model for our web app. Should I implement the custom security model? If I do that, I’ve got to pony-up for a non-free Edition (i.e. Web, Workgroup, Standard, etc.). I’ve also read (in Theo Lachev's great book: 'Applied MSSQL 2008 Reporting Services') about the ‘Trusted Subsystem’ model. Does anyone have any solid experience with implementing either of those models? We’ve also bounced around the idea of somehow creating an Active Directory on the remote server where SQL/SSRS is hosted, in order to facilitate the SSRS authentication, but that makes managing users and groups a huge out-of-database pain.
I’ve got a very good web developer and an equally skilled systems admin working with me, we’re just looking for a roadmap to follow, and/or some concrete examples, so we can move forward with confidence.
I would be very grateful to anyone who could point me in the right direction.
Many, many thanks.
-Simon
August 19, 2010 at 11:31 am
Sorry, I can't help you because I'm trying to answer the same questions.
Have you made any progress outside this forum?
August 19, 2010 at 11:43 am
Hi Bruce,
Unfortunately, we haven't made great progress yet.
We've decided to work toward the 'Trusted Subsystem' model that I read from Chapter 19 of Teo Lachev's book: Applied Microsoft SQL Server 2008 Reporting Services. I highly recommend the book.
Basically, we're going to create generic Windows credentials for the different types of 'Roles' that a user could fit into: Teachers, Students, Administrators, etc. and use those generic accounts to control access to the SSRS reports. Those generic Windows accounts will be more manageable than creating users for every single potential user, because there are far fewer groups than there are users, and all members of a group should be able to see the same reports. Each teacher/student/administrator will also have a unique username and password, which we'll store not at the SQL Server level, but at the Database level. We will store their unique ID (TeacherID, StudentID, AdminID, etc.) in a database table, and we'll need to retrieve it, so that we can pass it as an internal parameter to the report that they are requesting (so that student Johny can't see the report card for student Sally).
We're still moving forward very slowly, and working towards more of a prototype than full implementation, but I'll keep you posted on the progress.
-SD
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply