Crystal reports / SQL Server connecting

Hello folks,

I have setup a series of reports that run using Crystal Reports ActiveX Viewer. These reports connect to a SQL Server that I have running.

Sometimes, when a report is accessed by a user (OR a user attempts to launch a particular report) - They get a "LOGON Failed for user [etc]"

This then, causes ALL subsequent report requests for what looks like all users, to fail with the same logon failed error.

Does anyone have a clue as to how this occurs, or why. Im using CR9 and every report is run under a particular username/password (SQL Authentication) and this username has access to all tables within the database.

Eventually, it seems to stop all of a sudden and everyone starts accessing reports again.

I'm really clutching at straws here, but any insight into anything that I could investigate would be useful.

Im using SQL Enterprise 2000, Crystal reports 9, IIS5 and passing parameters to reports via ASP pages. Login credentials for SQL Server are stored against the reports and are valid (e.g. you can view the reports data in Crystal Itself)

Matt

Have you reviewed the MS SQL server logs for any errors? When this occurs have you checked the MS SQL server to see if anything is occurring during this time?

Yeah I examined the logs - nothing appears of any note.

Since posting I 'mused' on a workaround, which works. I created a set of domain level security groups and I have added these as users to SQL Server and used Windows based authentication. This seems to alleviate the problem.

Sounds like an authentication problem. I would suggest running sql_profiler and trap the logins, logoffs and errors as well as whatever else you need. If something happens you should see it there.