Multi-user issues with SSRS2000

Hi,

At our company we are looking at implementing SQL Server 2000 Reporting Services into our internet application. Having followed your book, and produced a dozen reports, I have found a problem with calling reports in a multi-user environment.

Some of the reports that our customers want can run to over 100 pages which, whilst a large number, is a requirement. I have found that when two people call one of these large reports at the same time; one of the users gets the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection". Once the worker process begins to render the report on the first user's screen, the report may be refreshed on the second users screen and it renders quite normally. Under normal circumstances the login credentials are valid and identical for both users.

Furthermore, an error 'Server Application Unavailable' is often generated when a number of users are requesting large reports this appears to be caused by the worker process being recycled.

Our production servers are a mixture of Windows 2000 Server (SP4) and Windows 2003 Server Standard Edition (SP1), the version of SQL Server and Report services is 2000 Enterprise Edition, using the latest service packs (SQL Server is SP4, and report services is SP2).

Can you suggest how we can get around these errors.

Regards,

Martin

Because your large report is consuming so much memory during the rendering process, your worker process is probably recycling because it's hitting the max MemoryLimit as defined in machine.config.

The "Login Failed" error message that you mentioned is a symptom of your worker process recycling, coming back up, and then trying to request a report on your behalf...however, because it cycled, it lost your user context (credentials), ergo the "Null" user...

The article below discusses dealing with memory limits / issues with large reports.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;909678

sql

Multi-user errors

Hi,
At our company we are looking at implementing SQL Server 2000 Reporting
Services into our internet application. Having followed your book, and
produced a dozen reports, I have found a problem with calling reports
in a multi-user environment.
Some of the reports that our customers want can run to over 100 pages
which, whilst a large number, is a requirement. I have found that when
two people call one of these large reports at the same time; one of the
users gets the error "Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection". Once the worker
process begins to render the report on the first user's screen, the
report may be refreshed on the second users screen and it renders quite
normally. Under normal circumstances the login credentials are valid
and identical for both users.
Furthermore, an error 'Server Application Unavailable' is often
generated when a number of users are requesting large reports this
appears to be caused by the worker process being recycled.
Our production servers are a mixture of Windows 2000 Server (SP4) and
Windows 2003 Server Standard Edition (SP1), the version of SQL Server
and Report services is 2000 Enterprise Edition, using the latest
service packs (SQL Server is SP4, and report services is SP2).
Can anyone suggest how we can get around these errors.
Regards,
MartinMartin,
It sounds like the problems occur during the processing/collecting of
data and that once that finishes and the reports begin rendering the
conflicts resolve. The first thing that comes to mind is rendering
these reports from snapshot data. Using a snapshot, you schedule the
query that generates your large dataset to run during off peak hours.
The reports can then be rendered immediately from the (already)
collected data rather than both collecting and rendering. This of
course assumes that you don't need up-to-the-moment i.e. realtime data
for the reports otherwise snapshots may not be an option for you.
Another option might be using subscriptions to distribute (push)
multiple copies of the reports rendered from one run of the process.
This might be the way to go if this an instance of one report being
called (pulled) by several users. Again, this assumes relatively
static data.
HTH
toolman
MartinClayton wrote:
> Hi,
> At our company we are looking at implementing SQL Server 2000 Reporting
> Services into our internet application. Having followed your book, and
> produced a dozen reports, I have found a problem with calling reports
> in a multi-user environment.
> Some of the reports that our customers want can run to over 100 pages
> which, whilst a large number, is a requirement. I have found that when
> two people call one of these large reports at the same time; one of the
> users gets the error "Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection". Once the worker
> process begins to render the report on the first user's screen, the
> report may be refreshed on the second users screen and it renders quite
> normally. Under normal circumstances the login credentials are valid
> and identical for both users.
> Furthermore, an error 'Server Application Unavailable' is often
> generated when a number of users are requesting large reports this
> appears to be caused by the worker process being recycled.
> Our production servers are a mixture of Windows 2000 Server (SP4) and
> Windows 2003 Server Standard Edition (SP1), the version of SQL Server
> and Report services is 2000 Enterprise Edition, using the latest
> service packs (SQL Server is SP4, and report services is SP2).
> Can anyone suggest how we can get around these errors.
> Regards,
> Martin

MultiServer SQL 2000 SP3 Administration

Hello,
I am trying to set up multiadministration server, since my company has 2
central cluster servers, with SQL Server 2000 SP3a, and 80 local sql server
2000 SP3a.
I have recently upgraded a central to SP3, and did the same for a local sql
server. Both are in the same domain, started by an special administrator
account for SQL Server services (both SQL Server service and SQL Agent).
I'm thus using Windows authentification. I tried to set up both server for
multiadministration, using SQL queries :
==> EXEC xp_sqlagent_msx_account N'SET', N'', N'', N''
to run multiadministration server under SQL agent account, and then
==>
EXEC sp_grantlogin 'MyDomain\MyUser'
USE msdb
EXEC sp_adduser 'MyDomain\MyUser', MyDomain\MyUser', 'TargetServersRole'
on each server, both central and target (local).
Then I try to set up multiadministration using EM, but the 'Specify MSX
Account' step fails, saying the account I'm using is not a valid MSX Account.
I can't find any more help on forums and BOL.
Your help will be much appreciated...This message was posted a few weeks
ago, but no answer on the SQL server tools forum. Thanks a lot for your help.
Xavier MarcatelCheck the section on "Multi-Server Administration" at this link:
http://www.sqlservercentral.com/columnists/bkelley/sp3coresecurity.asp
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"PetitGosaimass" <PetitGosaimass@.discussions.microsoft.com> wrote in message
news:63E1D6DA-68CB-4EAA-88FC-4462FBF9C5F4@.microsoft.com...
> Hello,
> I am trying to set up multiadministration server, since my company has 2
> central cluster servers, with SQL Server 2000 SP3a, and 80 local sql
> server
> 2000 SP3a.
> I have recently upgraded a central to SP3, and did the same for a local
> sql
> server. Both are in the same domain, started by an special administrator
> account for SQL Server services (both SQL Server service and SQL Agent).
> I'm thus using Windows authentification. I tried to set up both server for
> multiadministration, using SQL queries :
> ==> EXEC xp_sqlagent_msx_account N'SET', N'', N'', N''
> to run multiadministration server under SQL agent account, and then
> ==>
> EXEC sp_grantlogin 'MyDomain\MyUser'
> USE msdb
> EXEC sp_adduser 'MyDomain\MyUser', MyDomain\MyUser', 'TargetServersRole'
> on each server, both central and target (local).
> Then I try to set up multiadministration using EM, but the 'Specify MSX
> Account' step fails, saying the account I'm using is not a valid MSX
> Account.
> I can't find any more help on forums and BOL.
> Your help will be much appreciated...This message was posted a few weeks
> ago, but no answer on the SQL server tools forum. Thanks a lot for your
> help.
> Xavier Marcatel
>

MultiServer SQL 2000 SP3 Administration

Hello,
I am trying to set up multiadministration server, since my company has 2
central cluster servers, with SQL Server 2000 SP3a, and 80 local sql server
2000 SP3a.
I have recently upgraded a central to SP3, and did the same for a local sql
server. Both are in the same domain, started by an special administrator
account for SQL Server services (both SQL Server service and SQL Agent).
I'm thus using Windows authentification. I tried to set up both server for
multiadministration, using SQL queries :
==> EXEC xp_sqlagent_msx_account N'SET', N'', N'', N''
to run multiadministration server under SQL agent account, and then
==>
EXEC sp_grantlogin 'MyDomain\MyUser'
USE msdb
EXEC sp_adduser 'MyDomain\MyUser', MyDomain\MyUser', 'TargetServersRole'
on each server, both central and target (local).
Then I try to set up multiadministration using EM, but the 'Specify MSX
Account' step fails, saying the account I'm using is not a valid MSX Account
.
I can't find any more help on forums and BOL.
Your help will be much appreciated...This message was posted a few weeks
ago, but no answer on the SQL server tools forum. Thanks a lot for your help
.
Xavier MarcatelCheck the section on "Multi-Server Administration" at this link:
http://www.sqlservercentral.com/col...oresecurity.asp
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"PetitGosaimass" <PetitGosaimass@.discussions.microsoft.com> wrote in message
news:63E1D6DA-68CB-4EAA-88FC-4462FBF9C5F4@.microsoft.com...
> Hello,
> I am trying to set up multiadministration server, since my company has 2
> central cluster servers, with SQL Server 2000 SP3a, and 80 local sql
> server
> 2000 SP3a.
> I have recently upgraded a central to SP3, and did the same for a local
> sql
> server. Both are in the same domain, started by an special administrator
> account for SQL Server services (both SQL Server service and SQL Agent).
> I'm thus using Windows authentification. I tried to set up both server for
> multiadministration, using SQL queries :
> ==> EXEC xp_sqlagent_msx_account N'SET', N'', N'', N''
> to run multiadministration server under SQL agent account, and then
> ==>
> EXEC sp_grantlogin 'MyDomain\MyUser'
> USE msdb
> EXEC sp_adduser 'MyDomain\MyUser', MyDomain\MyUser', 'TargetServersRole'
> on each server, both central and target (local).
> Then I try to set up multiadministration using EM, but the 'Specify MSX
> Account' step fails, saying the account I'm using is not a valid MSX
> Account.
> I can't find any more help on forums and BOL.
> Your help will be much appreciated...This message was posted a few weeks
> ago, but no answer on the SQL server tools forum. Thanks a lot for your
> help.
> Xavier Marcatel
>