caching

In order for caching to work do you need to use SQL authentication
connecting to the sql server datasource. I know you need stored credentials
Thanks in advanceDo you need to have kerebos turned on. Because if the datasource is on
another server you get double hop and it only works using sql authentication
for us
"bobhug" <bobhug@.discussions.microsoft.com> wrote in message
news:6E6120C2-380C-4082-BE5F-FE2EAEA07834@.microsoft.com...
> Caching works fine using stored Windows credentials.
> Bob
> "Will Byron" wrote:
> > In order for caching to work do you need to use SQL authentication
> > connecting to the sql server datasource. I know you need stored
credentials
> > Thanks in advance
> >
> >
> >

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..

Cached Report and Windows Authentication

I want to use cached reports with Reporting Services 2005. But when I edit the execution properties of a report, an error message answser that I can't use cache because it has a connexion to Analysis Services with Windows Authentication.

I understand why I can't do that, but I still want to do so, because the reports take too much time to load. How can I do that ?

Thanks.

You can configure the datasource in the report to run using stored credentials. In report manager, go the report and then click on the DataSource tab on the left side, you can store the credentials using that UI.|||

Thanks. I tried this and it works, but I don't see better performance...

I thought that I would not see "Generating Report, please wait" any more...

|||

Query the ExecutionLog table in the report server catalog database to see if we are submitting the queries. You can tell by looking at the "rowcount" column. You will also see how much time we are spending in processing and rendering respectively. Processing is the time that it takes for us to create the snapshot, and should be minimized by rendering from the execution cache.

|||

TimeDataRetrieval 645 0
TimeProcessing 4855 347
TimeRendering 23518 1090
Source 1 2
ByteCount 10249 10249
RowCount 183 21

Thank you, it seems that the cache is used. The first column is created the first time when the cache is generated, and the second column is created when I open the report a second time. I still got a RowCount > 0, I don't know why... Isn't there a way to tell the ReportServer the generate a .html file as cache, I think it would be much faster.

But then again, it works, so I thank you for your help.

|||We will automatically cache whatever we think is safe to cache. HTML caching can be problematic because there is some session specific information which is embedded in the HTML, so we cannot simply serve up the same HTML to multiple users.

Cached Report and Windows Authentication

I want to use cached reports with Reporting Services 2005. But when I edit the execution properties of a report, an error message answser that I can't use cache because it has a connexion to Analysis Services with Windows Authentication.

I understand why I can't do that, but I still want to do so, because the reports take too much time to load. How can I do that ?

Thanks.

You can configure the datasource in the report to run using stored credentials. In report manager, go the report and then click on the DataSource tab on the left side, you can store the credentials using that UI.|||

Thanks. I tried this and it works, but I don't see better performance...

I thought that I would not see "Generating Report, please wait" any more...

|||

Query the ExecutionLog table in the report server catalog database to see if we are submitting the queries. You can tell by looking at the "rowcount" column. You will also see how much time we are spending in processing and rendering respectively. Processing is the time that it takes for us to create the snapshot, and should be minimized by rendering from the execution cache.

|||

TimeDataRetrieval 645 0
TimeProcessing 4855 347
TimeRendering 23518 1090
Source 1 2
ByteCount 10249 10249
RowCount 183 21

Thank you, it seems that the cache is used. The first column is created the first time when the cache is generated, and the second column is created when I open the report a second time. I still got a RowCount > 0, I don't know why... Isn't there a way to tell the ReportServer the generate a .html file as cache, I think it would be much faster.

But then again, it works, so I thank you for your help.

|||We will automatically cache whatever we think is safe to cache. HTML caching can be problematic because there is some session specific information which is embedded in the HTML, so we cannot simply serve up the same HTML to multiple users.