Implementing Windows authentication with Reporting Services

Question

This is an extension of post Reporting Services Authentication advice sought as I am unable to submit a lengthy post on that thread (apologies admin).

Basically, I am configuring my local installation of reporting services to use Windows Authentication (with the ultimate aim of implementing this authentication on the network).

My current setup:

IIS (5.1): I have created an application named "ReportServer" that uses ASP.NET v2.0.50727. The security for this application is set to "Integrated Windows authentication". I have unchecked the "Anonymous access" check box. Am I correct to assume this will prevent anonymous access to IIS (i.e. to the ReportServer application in IIS?) only. Therefore, the user will be prompted for log in details, which in turn will be validated against windows/AD?

Report: I have created a shared data source where the database resides on a different server and configured the data source to use "Windows Authentication" (not SQL Server authentication) and I can test connection successfully. I can also successfully create and test a simple report by running the report in BIDS.

When I deploy the report, I do not get prompted to login (which is fine). I believe this is due to the fact the application directory in IIS is configured to use "Integrated Windows authentication" which is using the logged in PC user account (correct?).

Whenever I load reporting services via a browser, I am correctly being prompted for my windows domain username and password but only when I run the report from my local PC. Once authenticated, I can see all reports applicable to me. If a colleague attempts to connect to the report instance on my PC, he allowed in without having to log in!! Why is this?!

However, when I run the deployed report via a browser, I receive the error "An error has occurred during report processing. Cannot create a connection to data source . Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection." If I change the data source so it uses SQL Server Authentication and I specify a login that exists in SQL Server, the report can be run successfully in a browser.

The ideal solution I would like to implement is as follows:

1. A user loads the report server URL in a browser.
2. The user is prompted for their Windows/AD credentials (including domain prefix).
3. Behind the scenes the user is allowed into the report server.
4. The user will only be able to see the report folders and reports he/she is allowed to see. This will be controlled by adding the allowed AD groups/users to the report folders and reports (I know how to do this bit). I do not wish to have to maintain separate SQL Server user accounts.

I feel as if I am almost there, i.e. connection to the report server is prompting the user for their Windows credentials before allowing them access to the report server.

Please could someone tell me:

a) Why am I being prompted for my windows log in when I access the reports via a browser locally, but a different windows user is not prompted for a log in when they remotely access my report server via a browser window?

b) If a better overall solution should be implemented, please could you advise or point me to relevant resources.

c) Is there anything to be wary of with my current setup that may cause problems in the future.

Many thanks in advance.

JFB

Answer 1

There are 2 security aspects to deal with

1. Authentication for the User Accessing the Report
2. Authentication for the Data Source(s) accessing the database(s)

Typically, we've found that we want to kick unauthorized users out at point #1, and then (assuming that user is authorized), provide a static set of connection credentials for the data source - this has benefits such as allows connection pooling and saves our DBA's a ton of headache figuring which users can access which databases, and run which report SPROC's etc, since they only need to configure for the 'Report Data Source user'.

Point #1 is controlled by the Groups / Users added to the Report / Folder.

Point #2 can be done by setting up a domain credential for your Data Source and then assigning this user appropriate read / execute access on the various databases it needs to access.

Possibly the one point you may be missing is that your data source credential runs as needs 'log on locally' permissions in order to work?

FWIW our settings for the data sources are as follows:

• Credentials stored securely in the report server
• Check : Use as Windows credentials when connecting to the data source

The account running your

Answer 2

The reason you are experiencing this (i.e. user = null) is because you are asking the Report Server to validate Kerberos credentials. There is an extensive post here, but the crux of your issue is that you are asking the Report Server to validate Windows credentials but Active Directory doesn't trust your machine to pass these along. If you get delegation set up correctly, it will satisfy your requirements without a login prompt and you can control visibility by via your point #4.