I'm struggling with SSRS security. Appreciate any comments/answers to the following.
I have a Windows Server 2008 R2 SP1 server that is a member of an Active Directory domain. I was given a domain account that is a member of the server's Administrators group. I installed SQL Server 2008 R2 on this server, specifying my account for the Log On As values.
On this server, I used BIDS to create a shared datasource, a shared dataset ("Salespersons"), and a fairly complex report that utilizes these objects and a few non-shared datasets. (All of the data for this report is stored on another SQL Server 2008 R2 server that is also a member of the domain.) The reports execute fine from within BIDS. I can deploy the reports without difficulty. I can also use IE9 to log on to Report Manager, using my domain account, on the report server and on a Windows 7 workstation (that is also a member of the domain). When I attempt to execute the reports from within Report Manager, however, I get the following error:
An error has occurred during report processing (rsProcessingAborted)
The execution failed for the shared data set 'Salespersons'. (rsDataSetExecutionError)
Cannot create a connection to data source ' Data source for shared dataset'. (rsErrorOpeningConnection)
Logon failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Why is NT AUTHORITY\ANONYMOUS LOGON attempting to log on to the data source? Since the report executes OK in BIDS, what account is *BIDS* using to successfully log on?
I examined the properties of my data source in Report Manager. It's configured to connect using Windows Integrated Security; yet when I press the Test Connection button, it returns another 'Logon failed for user 'NT AUTHORITY\ANONYMOUS LOGON' error. Why is that? Why wouldn't it using Windows Integrated Security; i.e., my domain account? I tried various other Connect Using options, with no success.
I'm stumped. Any ideas?