Cached reports REQUIRE that SQL Server used SQL Server authentication (or mixed)?

I am trying to setup cached reports so that one of my larger reports doesn't have to be re-run every time someone wants to view it (the data source only updates every 24 hours).

Anyway I made a new data source, set the report to use that, and in that data source I said to use "SQLexampleUserName" as the stored credentials.

Now when I go to run the report it says: Login failed for user 'username'. The user is not associated with a trusted SQL Server connection.

This is referenced in the MSKB here:

http://support.microsoft.com/default.aspx/kb/555332

Which makes sense, but now my question is: If I want to used a cached report do I HAVE to allow SQL Server Credentials?

I was using Windows Authentication only up to this point.

Yes, you can still use windows credentials for cached reports. To use windows user credentials, in the report properties, on the data sources tab, select 'credentials stored securely in the report server' then enter yorur windows creds, check the use as windows credentials checkbox, then click apply.

Hope that helps,

-Lukasz

|||

I'm afraid I'm still having trouble.

I followed your steps from the report manage web based interface and now I have:

An error has occurred during report processing.
Cannot create a connection to data source 'My-Server'.
Login failed for user 'DOMAIN\testuser'.

To make the changes I went to the Data Sources tab as you said, then selecteed "A Customer Data Source" and the Microsoft SQL Server. My Connection string is:

Data Source= My-Server; Initial Catalog=MyDatabase

Then "credentials stored securely in the report server" and "use as windows credentials when connecting to the data source"

I tried the user name as DOMAIN\user and just user.

Now it may just be that I don't have things configured properly somehow, but the report runs just fine if I use Windows Authentication and don't try to cache the report.

Can I make these adjustments from inside the design studio instead of the web based report manager?

|||

That config seems like it should work, it matches the one I'm used. Couple things to check:

1- Did you add the correct permissions for the test account to actually access the database?

2- Create a local user account, make it an SysAdmin (just to troubleshoot!!!!) and try it that way. I don't use a AD account because that account doesn't need to access any network resources, and have more control over the password reset time, etc. Creating a local account to troubleshoot should also rule out any strange AD issues you might be having.

Don't check the 'impersonate' box (you didn't mention it, so I'm guessing you haven't checked it)

Otherwise, once it's running, you'll want to harden the system by ensuring the account that's cached has the lowest possible permissions needed to execute the reports.

Geof

|||

Thank you, I will check on that.

As for security, what permissions does an account need to run a basic report?

If I was reporting off TestDatabase on the server I just need to grant the account a login on the server and then read (public) access to TestDatabase, is that correct?

|||

Depends on what security you've added to the basic database, but yes, a login, and then public access as described.

Based on your error message, I think you aren't getting past the login stage. There is no need to grant any access rights to any reportserver system databases.

|||

Thanks.

I agree that it appears my test account is having issues as I tried it with my personal account and it works just fine.

I'll review my security permissions and work it out from there..