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 ?

Look to see if you have an ACLs on the Analysis Services installation
folders (bin, data, etc.). Or ACLs against the repository (if still in MS
Access format).
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Laurence Neville" <laurenceneville (AT) hotmail (DOT) com> wrote

There are 3 problems processing a MS-OLAP-Cube by DTS, I know:

1) the OLAP-tasks are in a sub-package of the main-packing running by
the SQL-Agent -> this will not run at most installations -> the
OLAP-Task-DTS has to be the main-packages, other sub-packages before
or after are possible

2) SQL-Agent is running as 'local system' -> no chance, has to run
under an OLAP-Admin-account (the olap-service should also have this
account for running)

3) access-problem (cause someone else in the company has the
olap-manager-console active ...) -> stop and restart the
Analysis-Serves before re-building the cube


I prefer to use a local admin (only on thie server) for running SQL,
OLAP and Agent.


minimax

ANTARES Informations-Systeme GmbH
http://www.antares-is.de
http://www.dynaSight.net

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:

"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote

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.

"minimax" <minimax (AT) dynasight (DOT) net> wrote

I am not using sub-packages

SQL Agent runs as SQLServerService. The OLAP Service runs as System. I was
wary about changing this as there doesn't seem to be a way of doing it
through the OLAP Manager. I could change it through the Windows Services but
would that leave out permission changes in the registry?

Tried that