Caching a report with parameters

I have two reports one with no parameters and one with two parameters that
uses a data source with credentials stored.
When I try and setup caching for these reports I cannot for the one with
parameters. I get an error:"Credentials used to run this report are not
stored" even though they are.
Is this by design or do I need to do soimething else.
ThanksIt is by design. See my post
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/1efc856d389b549b/80177e1ebec33cd0?lnk=st&q=%22+snapshot+question%2C+support+for+a+parameter...%22&rnum=1&hl=en#80177e1ebec33cd0
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Niall" <Niall@.discussions.microsoft.com> wrote in message
news:3C8A43E6-5BC9-4380-A9DD-8F78C8F39DA8@.microsoft.com...
>I have two reports one with no parameters and one with two parameters that
> uses a data source with credentials stored.
> When I try and setup caching for these reports I cannot for the one with
> parameters. I get an error:"Credentials used to run this report are not
> stored" even though they are.
> Is this by design or do I need to do soimething else.
> Thanks
>

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

C2 SQL auditing

Hi,
I setup my SQL 2000 Server to use C2 auditing. It is working.
My only problem is that my trace file does not get populated while I am
inserting/updating/select data or anything.
However, when I stop SQL server then my trace file gets populated.
I cannot stop a production SQL server on and off just to collect data and
the trace file is only limited to 200MB so I can wait till the end of day.
I am trying to do a audit report every 2hrs.
Am I doing something wrong? Or this is how C2 works?
I would appreciate all the help!Don't use the C2 auditing. Just create a custom trace that collects the data
you want and you can start and stop it as you need to. Check out
sp_trace_Create in BooksOnLine.
Andrew J. Kelly SQL MVP
"SQL apprentice" <mssqlworld@.yahoo.com> wrote in message
news:OYlONfeVHHA.3500@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I setup my SQL 2000 Server to use C2 auditing. It is working.
> My only problem is that my trace file does not get populated while I am
> inserting/updating/select data or anything.
> However, when I stop SQL server then my trace file gets populated.
> I cannot stop a production SQL server on and off just to collect data and
> the trace file is only limited to 200MB so I can wait till the end of day.
> I am trying to do a audit report every 2hrs.
> Am I doing something wrong? Or this is how C2 works?
> I would appreciate all the help!
>|||Hi Andrew,
I had to setup C2 for my company just so they can see it in their own eyes.
I recommended them to use Server Side trace, it is more efficiency and
customizable to what they want to audit.
It would make my case even better when I tell them 3 SQL MVPs suggest not to
use C2.
Do you suggest any third party tools for SOX compliance? I am testing Idera
CM right now.
Eventually, I would like to off load the server side trace to the SOX team
since there are over 100 SQL Servers.
I would like to setup and manage so many traces.
Thanks again for the input...I greatly appreciated.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OqOIdLiVHHA.3592@.TK2MSFTNGP03.phx.gbl...
> Don't use the C2 auditing. Just create a custom trace that collects the
data
> you want and you can start and stop it as you need to. Check out
> sp_trace_Create in BooksOnLine.
> --
> Andrew J. Kelly SQL MVP
> "SQL apprentice" <mssqlworld@.yahoo.com> wrote in message
> news:OYlONfeVHHA.3500@.TK2MSFTNGP05.phx.gbl...
and[vbcol=seagreen]
day.[vbcol=seagreen]
>|||I don't have a specific recomendation as the SOX requirements are very loose
and open to interpitation. There are a number of 3rd party tools that
monitor the logs.
http://sqlserver2000.databases.aspf...t
a.html
http://sqlserver2000.databases.aspf...
log-files.html
Andrew J. Kelly SQL MVP
"SQL apprentice" <mssqlworld@.yahoo.com> wrote in message
news:ufl7fZpVHHA.1636@.TK2MSFTNGP02.phx.gbl...
> Hi Andrew,
> I had to setup C2 for my company just so they can see it in their own
> eyes.
> I recommended them to use Server Side trace, it is more efficiency and
> customizable to what they want to audit.
> It would make my case even better when I tell them 3 SQL MVPs suggest not
> to
> use C2.
> Do you suggest any third party tools for SOX compliance? I am testing
> Idera
> CM right now.
> Eventually, I would like to off load the server side trace to the SOX team
> since there are over 100 SQL Servers.
> I would like to setup and manage so many traces.
> Thanks again for the input...I greatly appreciated.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OqOIdLiVHHA.3592@.TK2MSFTNGP03.phx.gbl...
> data
> and
> day.
>

bypass mode

i have setup replication that works fine, after adding some article while
applying changes, my db went to suspect mode,
by running following script
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = 'criticaldevices'
go
Sp_configure "allow updates", 0
go
Reconfigure with override
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 0 where name = 'criticaldevices'
go
Sp_configure "allow updates", 0
go
Reconfigure with override
while starting snopshot agent, i got following error
Could not run BEGIN TRANSACTION in database 'CriticalDevices' because the
database is in bypass recovery mode.
Can anyone help me to solve this issue
thanks
Kalyan
did you follow all of the steps listed here?
http://groups.google.com/group/micro...e=source&hl=en
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kalyan" <Kalyan@.discussions.microsoft.com> wrote in message
news:FD9CB3DF-315A-4AA7-81B5-88503B9181FF@.microsoft.com...
> i have setup replication that works fine, after adding some article while
> applying changes, my db went to suspect mode,
> by running following script
> Sp_configure "allow updates", 1
> go
> Reconfigure with override
> GO
> Update sysdatabases set status = 32768 where name = 'criticaldevices'
> go
> Sp_configure "allow updates", 0
> go
> Reconfigure with override
> Sp_configure "allow updates", 1
> go
> Reconfigure with override
> GO
> Update sysdatabases set status = 0 where name = 'criticaldevices'
> go
> Sp_configure "allow updates", 0
> go
> Reconfigure with override
>
> while starting snopshot agent, i got following error
> Could not run BEGIN TRANSACTION in database 'CriticalDevices' because the
> database is in bypass recovery mode.
> Can anyone help me to solve this issue
> thanks
> Kalyan