Reporting Services & Windows Integrated Security Not Working

Question

Reporting Services & Windows Integrated Security Not Working

dave-dj

SSChampion

Points: 11297
More actions
March 12, 2010 at 4:03 am

#386487

Hi all,
I have setup Reporting services including a SP3 update. My Reporting Services is running under a services account. The Windows Service Identity ASP .Net Service Account has been left as the default "NT Authority\NetworkService".
For my published report set I am using a data source that point to another server, and I have set my data source to use Windows Integrated Security.
This appears to work for the initial users logon to Reporting Services - Report Manager.
When I run the report however, I get an error saying: can't connect to data source ??......
On the SQL Server Side the log shows:
Date12/03/2010 10:29:58
LogWindows NT (Application)
SourceMSSQLSERVER
CategoryLogon
Event3221243928
UserNT AUTHORITY\ANONYMOUS LOGON
ComputerSQLTEST01
Message
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 192.168.17.152]
If I change the data source to prompt to use:
Connect using:
Credentials supplied by the user running the report
Display the following text to prompt user for a user name and password:
Use as Windows credentials when connecting to the data source
then the report run ok, as it seems to authenticate against the Database ok.
So it seems, for the initial connection to Reports Manager - it authenticates the user using windows integrated security. However data source connects to the remote database, its seems to lose the windows credentials, and trys to connect as anonymous.
My collegue believes this may be a Kerberos issue, but looking at how this is setup (http://support.microsoft.com/kb/319723) it's very long winded.
Is this anything else I can do to resolve this issue?
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

vishalmehta78 SSC Veteran Points: 293 More actions · Answer 1

the service Identity is used to connect the report manager to its DB & fetch catalogs for display. but when you run the report the Data Sources uses the identity of the user who is running the report and checks with the source database if the user is a member of the security in that db server.

In your situation u are asking the DB server to authenticate the RS service account of the report server which is not a good practice. So what you need to do is create a separate service account that is meant to run reports only & not the services. add that as a credential to be used in the datasource now add the same account in the db server with datareader permission. This way the report will run for any user who is a member of your ad group.

dave-dj SSChampion Points: 11297 More actions · Answer 2

Thanks Vishal.

I've been working on it for the last few days, and my it working, by using the impersonate windows account, having set up the required Service Principle Names against my RS service account.

(The RS Service account doesn't have any access rights to SQL server).

I since found that what it's doing is effectively a 'double-hop'. So I'm looking at trusted delegation for the participating servers in order to get it working end-to-end using kerberos authentication with the clients credentials.

I'm getting there slowly. 🙂

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

seth.goldman Valued Member Points: 67 More actions · Answer 3

Can you elaborate on how you set up the SPNs? We're working through the same issue and I think we set up the SPNs incorrectly. A sample SetSPN command line would be helpful.

Thanks,

Seth

dave-dj SSChampion Points: 11297 More actions · Answer 4

Have a read through the attached troubleshooting document.

Very useful document, which covers everthing you need to know, including some common example scenarios and also set SPN commands.

😉

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

Mauve SSChampion Points: 11316 More actions · Answer 5

The same problem can occur in a standard web application where the Reporting Server is separate from the Database Server. Which it should be for performance, etc. I.e., Client -> Web Server -> SSRS -> Database Server.

See:

http://www.databasejournal.com/features/mssql/article.php/3696506/Setting-Up-Delegation-for-Linked-Servers.htm

Kerberos Troubleshooting

http://blogs.msdn.com/b/robertbruckner/archive/2010/06/24/kerberos-troubleshooting.aspx

And see the picture in http://blogs.msdn.com/b/psssql/archive/2010/06/23/my-kerberos-checklist.aspx

Another solution, if you don't want to go down the Kerberos route, is to use Stored Credentials (Credentials stored securely in the report server) with the report to access the data source. These credentials can be different than what is used to access the Report. So the user accesses the report under one set of credentials and a different set of credentials is used to access the data source when the report is run.

This is what we did for our SaaS-based web application. Kerberos can get quite nasty.

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]