Scheduled DTS Db->excel Pgk under proxy acct fails

Question

Scheduled DTS Db->excel Pgk under proxy acct fails

ajaychriscoe

SSC Rookie

Points: 32
More actions
August 8, 2002 at 3:01 pm

#57946
The following scheduled DTS package fails when running under the proxy account.
MS SQL Version: 8.00.608
OS: Windows 2000 - SP3
MDAC: 2.7
DTS Package:
- Source DB using SQL ID (dbo).
- Destination is MS 97-2000 Excel file located on a share on the local server \\server\share\file.xls.
- Transformation - dumps a single table to excel file.
Results:
Executed as user: pdbatest2k\mssqlproxy. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Unspecified error Error source: Microsoft JET Database Engine Help file: Help context: 5000000 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (0) Error string: Unspecified error Error source: Microsoft JET Database Engine Help file: Help context: 5000000 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
Have Tried / Confirmed the following
- The above package runs successfully under SA account (the SQL Agent is running under NT Admin account)
- The proxy account has only public access to SQL DB - the above job also fails when the proxy account is a member of System Administrators role
- The proxy account has NTFS permissions to it's temp directory AND both NTFS and File permissions to the NT Share where the excel file is being written
- Granted NT proxy ID FULL access to system registry - same error as above
- Tested job after elevating the user rights on the local server. (the proxy account is a member of the local users group with the default permissions). Still receive the same error as above.
- Proxy account has FULL permissions to MS SQL program files
- This problem is also on production server MS SQL 8.00.608 on Win2K SP2 using MDAC from SQL SP2 - again with same error as above
- However - by changing the above DTS package to dump a table to a text file (.csv), the scheduled package runs successfully as a NON-SA account and executing as the proxy account
- Can also successfully execute scheduled DTS package to import data from Oracle ODBC connection into a SQL DB using the proxy account. However, a similar scheduled DTS package to dump a table to excel fails with the same error as above.
Any Suggestions on how to fix / determine the problem?
Thanks!!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Brendon Ten Centuries Points: 1252 More actions · Answer 1

I've done a bit of work with DTS and exporting data to Excel, and I get similar error messages that aren't very helpful. Personally I think theres some bugs relating to the Excel source/destination connection.

First, are you executing directly from the Enterprise Manager or through a scheduled job? Being able to run it successfully as 'sa' points to permission problems.

Second, are you exporting to a new or existing Excel document?

If you believe the account you're using is accurate, the best way to check is to login to Enterprise Manager under that account, then manually try to execute the package from there. If you still get failure, try manually re-creating the DTS package (Database -Tasks - Export) whilst logged in under the same account - you may find that you get a more specific error message than that which you get with a scheduled job.

ajaychriscoe SSC Rookie Points: 32 More actions · Answer 2

Can execute the DTS package directly from EM as the non-sa account with an existing Excel document. However, when this same DTS package is scheduled, it fails. I agree there are permission problems - but not within MS SQL. We are using a proxy account to run all non-sa cmdexec jobs and this account initially had only public access to SQL. The same scheduled DTS job still fails when executed under the proxy account when is a member of the System_Administrator role. This package works when scheduled as SA, because the SQL Agent is running under and that ID has Administrator privilege on the Windows 2000 server. We have been systematically trying to increase the proxy accounts NT permissions/level to find the exact cause of the problem (no luck yet).

Thanks!

Brendon Ten Centuries Points: 1252 More actions · Answer 3

hmmm...give this a try.

You've setup your NT domain account under the SQL Server agent (and stopped and restarted the service).

Add the NT domain account to SQL Login security, and add it to administrator role within.

Also add the NT domain account to the administrator group on the local server (not through the domain controller).

Hopefully this will help determine if its a permission problem. Your domain account needs to have the right to create files on the local system, and the right to execute DTS packages from within SQL server.