SQL Server Reporting Services Authentication

garnett-dickinson
garnett-dickinson used Ask the Experts™
on
Hi,

We've have moved our MIS onto a dedicated server and this includes reports which use SQL Server Reporting Services, at the moment we have setup a generic username and password to access the reports.

We are looking to used our MIS SQL Server database to authenticate instead. So in short, when a user logins into our MIS using their own username and password, and then selects Reports, rather than it then requesting for the generic reports password, we would like it to authenticate using the login details they have already entered to gain access to our MIS.

Just to clarify our MIS system is written in ASP VBScript with a SQL Server 2008 database.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Check your web.config file to add your Authentication.

Author

Commented:
One of my colleagues has suggested using Forms Authentication, is this the best way. If so, how do you go about setting it up.
Have a look at the following web sites it has some instructions for setting up Reporting Services to use Forms Authentication.

Important: Make backup copies of all of your configuration files before making any changes

Reporting Services 2008 and ASP .NET Forms Authentication
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/5b448b22-0cb1-4b9b-b515-603da41d416f

Readme_Security Extension Sample
http://msftrsprodsamples.codeplex.com/wikipage?title=SS2008%21Security%20Extension%20Sample&referringTitle=Home
Here is a web site with some info on configuring your Classic ASP application to use Forms Authentication

Tip/Trick: Integrating ASP.NET Security with Classic ASP and Non-ASP.NET URLs
http://weblogs.asp.net/scottgu/archive/2007/03/04/tip-trick-integrating-asp-net-security-with-classic-asp-and-non-asp-net-urls.aspx
Commented:
Yes, this is easy to do.
If users are using Active Directory domain accounts, then all you need to do is go to the report server, create a local group - call it something like 'report users'. Then add in the domain users that need access to reporting to this local group. Or even better, if they are all members of existing AD domain groups, add those groups into this local group.
Then go to the Reporting Services URL, something like.....http://SERVERNAME/reports
Click on 'Properties' and add the local group as a New Role Assignment with whichever permissions you like the users to have for Reporting.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial