I'm looking into using Forms authenticaiton for SSRS. I got it working using Microsoft's sample found here: http://msftrsprodsamples.codeplex.com/wikipage?title=SS2008!Security%20Extension%20Sample
However, I'm wondering what authentication to use to the SQL Server data source. When I use Windows Authentication I get this error:
An error has occurred during report processing. (rsProcessingAborted) The execution failed for the shared data set 'ActualYears'. (rsDataSetExecutionError) Cannot impersonate user for data source ' Data source for shared dataset'. (rsErrorImpersonatingUser) This data source is configured to use Windows integrated security. Windows integrated security is either disabled for this report server or your report server is using Trusted Account mode. (rsWindowsIntegratedSecurityDisabled)
When I use SQL Server Authentication it prompts me for the credentials when I run a report, even though my data source has the user and password specified in the connection string. So when I run the built-in Report Manager I have to log in for forms authentication. Then when I run a report I have to log in again for SQL authentication.
If using Forms authentication do you have to use SQL Server Authentication or can Windows Authentication somehow work? Also, is there a way to get SQL Server Authentication to not prompt you for the credentials, but to use those already supplied in the connection string?