Re: SQL Agent cannot run DTS packages that contain Analysis Services tasks

From: Darren Green (darren.green_at_reply-to-newsgroup-sqldts.com)
Date: 08/06/04 

Date: Fri, 6 Aug 2004 21:09:51 +0100

In message <e3dYhK7eEHA.1764@TK2MSFTNGP10.phx.gbl>, Laurence Neville
<laurenceneville@hotmail.com> writes
>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 ?
>
>

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

Relevant Pages