We have a CR 8.5 report that requires a login to a sql server database when executed. The database objects referenced in the report are owned by user dbo. The login used to execute a report has select privileges on the objects owned by dbo. When the report is executed the reports runs without problems. When the report is executed by the scheduler using the login option we get the error message access denied. When we run a similar report but this time pointing the location of the database objects to objects owned by the login user the report works. Do the database objects referenced in a report have to be owned by the user set in the login option of the report in CRD?

I assume that the problem is happenng when you use the NT service. The NT service user (the user that you set up the service with) must have the same rights to the database objects, shared folders, source files and folders, destination files and folders etc as a normal user. This is actually a windows/network requirement rather than a CRD limitation.

So, yes, you need to make sure that all the required rights that would normally be required for a normal user are granted to the NT service user. That way, the NT services "environment" has the right security to mimic a normal user.

Also, the CRD login options are for if you are using login credentials in the report itself. If your report conects using an ODBC DSN and using NT authentication, then there is no requirement for the report to ask CRD for credentials. It doesn't therefore ask for credentials and it will try and log into your database using the NT Service User's credentials. So we are back to ensuring that the NT Service user has the correct security rights to access the database.

If I understand you correctly the NT service account have to be a trusted account to the SQL server database.

Interesting to note that the NT service account have supervisor rights on the sql server server. Also when the login account user name matches the database object owner we don't have problems.

How does this work when the connection to the database cannot be set to trusted? Like when the DBA will not allow trusted connections or you are working with an Oracle or DB2 database.

The NT services user has to have the same rights as the normal user that you trust to work. ALL rights.

Quite clearly, having supervisor rights is still not high enough for access to your database. Perhaps you need to give your supervisor group profile some higher permissions.

If your security policy does not allow you to do this, then you have to use the background application scheduler instead of the NT service. What permissions you give to the NT service is entirely up to you, but access to the database can only happen if you give the user such access.

If your database requires that the user be given dbo rights before it can access the data, then why not go ahead and do that? After all, CRD does not actually modify, add or delete anything from the database. So no harm is done realy, is there?

No dice with granting dbo to a reporting user. Reporting users must be non intrusive to applications and databases. Besides it opens up a huge security hole. I don't believe it is an NT SQL server limitation. I ran the scheduled report login from the report options with the the dbo user id and password and the report ran. I think it has to do with the way the application is passing user credentials between the scheduler the report and the database. There was a similar issue with the database connections between .NET and Oracle 9i. I do have a work around for this specific implementation but the application code should be corrected to let the database handle the permissions to the objects.

OK, I see what the issue is.

CRD passes the credentials to the Crystal runtime components. The Crystal components do the logging in, not CRD. So the problem is how the Crystal component logs in if you are using an ODBC to Oracle.

Things should be different if you write your reports using native crystal connectivity - crystals own dlls - to connect to the database instead of ODBC (or at least that is what Crystal say).

What work-around are you using (just out of interest)?

Well. My work around was to create a set of synonyms in Oracle so the connection thinks is accessing it's owned objects. In SQL server I created a set of views under the login user ownership that created a 1 to 1 map to the source tables owned by dbo. I had an excel formatting problem that forced me to install CR10. I'm back to square 1 with this. I will try the CR ODBC to see if that solves my problem, again.

Success again. I was able to make the reports work again. This is really odd. If I schedule a report to run in a remote server (I'm creating the schedule by login to a repository database and letting the remote server running CRD as a service execute the schedule) the report work as long as I don't preview the report from the client. Somehow previewing a report causes CRD to pass incorrect credentials to the database connection or, CRD is messing around with the report object references stored in the schedule. Somehow the application is caching a lot of garbage in the client. This caching is creatin noise for the NT service acting as the scheduler. Is there a way to make sure the schedule gets clean to the scheduler. This is quite anoying.

I have come accross this before. This is what I discovered:

The crystal viewer dll caches the information in memory for a period of time - roughly 30 mins to an hour. During that period, the information that is passed to the report an application e.g. CRD is ignored by the CR export components. This is a CR thing, really.

As far as I can see at the moment, the thing to do is to wait about 30 mins and then execute and then all is OK. Or alternatively, don't preview your reports on that PC.

Or set up test destination to local folder or your email address. Then, instead you previewing, right-click >> Tools >> test schedule. Then open test result to see if things behaved like you wish.

I use this method all the time cos I want to see what final person will get. Preview only show what report look like in CR. This way you see what report look when get to final destination.

Hope above you helpful.

That sound reasonable. There's one thing that does not add up. I preview the report in my local machine but the scheduler is running as a service in the remote. So who's doing the caching CR in the local (which should not affect the scheduling machine) or the CRD service. Any ideas on how to flush the caching done by CR?

I only ever had the problem when I previewed the report using the preview button in CRD and theredore on the same machine as CRD. I never had the problem if I previewed it on a CR installation on a different machine. I can't see how one can affect the other if they are on totally different machines.

Do you get the same problem if you preview a copy of the rpt? Might this be a problem with the report object's security credentials being cached by the database rather than the report? Is your report set to have processing done on the database instead of local? Are the ODBC logon credentials stored in the report, or (more hopefully) have you checked the option for "trusted connection" so that the ODBC driver does the work? Are you getting the same results if you use the CR drivers to connect directly to the Oracle dB instead of the "workaround" or ODBC?

I never found a way of flushing the CR's memory cache either. I just previewed on one PC and scheduled on another for the two reports I had that had unconventional logon requirements.

Sorry I'm a data architect so I have a problem with trusted connection. Unless you have a bullet proof network (few organizations have) you should never allow trusted connections to your databases. The problem is not the access to the database. The local machine options housekeeping system paths cached reports path is the same as the scheduling server cahed reports path. I noticed that every time you open CRD and preview a report it creates a cached report instance in the cached reports subdirectory. The cached instances of the reports remain in the subdirectory and they are locked by the client session unless you exit from your local CRD session.

Can this be causing the problem. Report being cached by CRD and locked by the user session while the client session is out there? Therefore the access denied error is not access denied to the database but access denied to the cached report instance or the cache report instance parameters. Do the cache report subdirectory needs to be set for each client install regardless of whio's scheduling to avoid conflicts?

You might have hit the nail on the head for your circumstances. When a schedule is created, a cached copy of the report is stored in the cache folder. This happens at time of schedule creation or every time you refresh the schedule.

CRD uses the cached copy of the report, not the actual source report, when it is exporting scheduled reports and previewing. So yes, you cannot expect the scheduler to be able to access the cached report if you are holding it open in an editor session on another machine.

If you set the cache folder on the client to another path, then schedules that you set up will not be accessible to the scheduler as they will not be cached in the scheduler's cache folder.

I would imagine that the easiest thing to do is to accept that you should not preview the report using CRD at a time when you expect the scheduler to schedule it out. Preview the source report in CRD.

Alternatively, and this is what I do, I run two sessions of CRD which are totally independent of each other. I set up my schedules etc on my test machine which houses a scheduler and an editor.

In the latest build of CRD, it is possible to export a schedule from one installation to another, so I simply use that facility to migrate my schedule accross to the production server once I am happy with it.