This is a problem I have seen mentioned many times in this group and on the
web, but the solutions I have found do not work for our requirements.

If a DTS package contains an Analysis Services task, it will not run when
scheduled to run under SQL Agent (but it does run when run locally through
Enterprise manager by me). The error in the agent log is:

Executed as user: LEC_DOMAIN\SQLServerService. DTSRun: Loading...
Error: -2147024891 (80070005); Provider Error: 0 (0) Error string:
Access is denied. Error source: Microsoft Data Transformation
Services (DTS) Package Help file: sqldts80.hlp Help context:
713. Process Exit Code 1. The step failed.

The source of the problem seems to be the Windows account the agent runs as.
I can get it to work by making this account (LEC_DOMAIN\SQLServerService)
into an administrator on the server where MS Analysis Services is installed.
HOWEVER I don't want to do this ! Our security policy says that service
accounts should be as weak as possible. Currently the SQLServerService
account is a Domain Users account, and both SQL Server and Agent run under
this account (the Analysis Services service runs as System).

FYI, I have tried adding the account to the OLAP Administrators local group
on the server where MS Analysis Services is installed, but this makes no
difference.

Does anyone have a suggestion that does not involve turning the
SQLServerService acount into an administrator ?

In message <e3dYhK7eEHA.1764 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, Laurence Neville
<laurenceneville (AT) hotmail (DOT) com> writes

OLAP Administrators group, this is a must, otherwise you will not be
allowed to process a cube, even if you are an Administrator.

Access to the folders where the cubes are store, AS programs and any log
files. The recommendation is that you processing log file is a UNC path,
so obviously access to this share as well.

You will also need access to read the data sources, but this depends on
the source and security mechanism used in the source configuration.

Once you have set the require permissions, log onto the server as this
user and execute the package via enterprise manager from there. You've
already tried to eliminate user differences, so now try location.

Also worth a read-

269074 - INF: How to Run a DTS Package as a Scheduled Job
(http://support.microsoft.com/?kbid=269074)


Failing all that, the OLAP newsgroup group may be better clued up on the
pure security aspects required.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

I have followed everyone's suggestions and it is not working yet. I noticed
one extra clue: If I am logged in to the server as SQLServerService (i) I
cannot run the OLAP Manager, and (ii) if I run the Enterprise Manager and
create a new DTS package, the moment I click the button to insert an
Analysis Services task I get the error Access is Denied.

My responses to your suggestions below:

The SQLServerService account has full control on all folders and files in
the Microsoft Analysis Services installation folder. It also has access to
the MsOLAPRepository$ share because it is in the OLAP Administrators group.
I also checked it had full control over msmdrep.mdb. I also turned off
logging (the logs were configured to save to an inaccessible location) but
no joy.

All my tests were run logged into the server as the SQLServerService account

Yes, read this already

In message <OxGm$zrfEHA.2908 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, Laurence Neville
<laurenceneville (AT) hotmail (DOT) com> writes
Sorry I give up.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org