I am attempting to execute a DTS package from Query Analyzer using the DTSrun
utility under xp_cmdshell. When I execute the package, I receive the
following error:

"DTSRun OnError: Copy Data from Results to
J:\IDEAS40\TRISQL44\MJE\Data\MJE065d.txt Step, Error = -2147467259 (80004005)
Error string: Error opening datafile: Logon failure: unknown user name
or bad password."

However, when I excute the same package under a DOS prompt, the package
executes successfully. The same is true when the package is executed via
Enterprise Manager. Can someone help?

The error 80004005 would suggest either permission problems or that the
thing for which you look does not exist.

This is from BOL.

xp_cmdshell operates synchronously. Control is not returned until the
command shell command completes.

When you grant execute permissions to users, the users can execute any
operating-system command at the Microsoft Windows NTR command shell that
the account running Microsoft SQL ServerT has the needed privileges to
execute.

By default, only members of the sysadmin fixed server role can execute
this extended stored procedure. You may, however, grant other users
permission to execute this stored procedure.

When xp_cmdshell is invoked by a user who is a member of the sysadmin
fixed server role, xp_cmdshell will be executed under the security
context in which the SQL Server service is running. When the user is not
a member of the sysadmin group, xp_cmdshell will impersonate the SQL
Server Agent proxy account, which is specified using
xp_sqlagent_proxy_account. If the proxy account is not available,
xp_cmdshell will fail. This is true only for MicrosoftR Windows NTR 4.0
and Windows 2000. On Windows 9.x, there is no impersonation and
xp_cmdshell is always executed under the security context of the Windows
9.x user who started SQL Server




"rallbee" <rallbee (AT) discussions (DOT) microsoft.com> wrote

Thanks Allan, but the Userid I'm using is the "SA" account with a password.
if hwat you and BOL are saying is true then shouldn't the SA account have all
the permissions necessary. Also, I'm not sure what you are referring to in
the latter part of your statement: "thing for which you look does not
exist". Can you explain?

I have attahced a complete copy of the output that I get when I execute the
packge:

------- BEGIN OUTPUT -------
DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: Copy Data from Results to
J:\IDEAS40\TRISQL44\MJE\Data\MJE065d.txt Step

DTSRun OnError: Copy Data from Results to
J:\IDEAS40\TRISQL44\MJE\Data\MJE065d.txt Step, Error = -2147467259 (80004005)

Error string: Error opening datafile: Logon failure: unknown user name
or bad password.

Error source: Microsoft Data Transformation Services Flat File Rowset
Provider

Help file: DTSFFile.hlp

Help context: 0

Error Detail Records:

Error: 1326 (52E); Provider Error: 1326 (52E)

Error string: Error opening datafile: Logon failure: unknown user name
or bad password.

Error source: Microsoft Data Transformation Services Flat File Rowset
Provider

Help file: DTSFFile.hlp

Help context: 0

DTSRun OnFinish: Copy Data from Results to
J:\IDEAS40\TRISQL44\MJE\Data\MJE065d.txt Step

DTSRun: Package execution complete.

------- END OUTPUT -------

Thanks!

"Allan Mitchell" wrote:

You are using a J drive.


Does the SQL Server Agent Service Account know about the J drive?

Can you change to UNC?



"rallbee" <rallbee (AT) discussions (DOT) microsoft.com> wrote

Your SA account is for SQL level permissions only. Access to the OS is
performed with the Domain/Local Login Account that the SQL Server Service
is running under.

xp_cmdshell access the operating system via the SQL Server Service account.
Think of it this way... you cannot log into the server as "sa", you are
logging into SQL with "sa" completely different authentication types. SQL
supports aliasing domain/local accounts via Mixed-mode security.

Check out the Books on Line (BOL)

-Mike Gercevich

--
Message posted via http://www.sqlmonster.com

Mike

Xp_cmdshell does not necessarily access the operating system as the
service account, please see my mail earlier in the thread.

Job ownership is very important in determining who will be the user
running the job and it is not always the service account either.

http://support.microsoft.com/?kbid=269074



"Mike Gercevich via SQLMonster.com" <forum (AT) SQLMonster (DOT) com> wrote in
message news:forum (AT) SQLMonster (DOT) com:

Allan,

I was under the assumption that your post was only for when the job
scheduler executed the job. I was not aware of if he was executing the
package direct from Enterprise Manager; on the server or remotely, or from
the Job Scheduler.

-Mike Gercevich

--
Message posted via http://www.sqlmonster.com

Gentlemen,

Thanks for the input. But my problem is more complex. Allan, yes, the
agent does know abolut the J drive.

The DTS package runs successfully under EM as a job or executed manually.
It also runs successfully when I execute it via a command prompt. The
problem begins whe I try to execute it using the XP_CMDSHELL via QA or OSQL.
The frustrating part is that I had it working at one point, but now I can't
get passed this error. I've checked permissions for the SA account and the
windows account (from which I am executing the package using QA) and the
account can read, write, and delete files on the J drive. I've checked
permissions and compared the sa account with other SQL Server 2000 boxes and
they match o the "T".

Where else do I need to look?




"Mike Gercevich via SQLMonster.com" wrote:

So everything works for you except xp_cmdshell?

Then this has to be a perms problem I think

Your account is a sysadmin?

Yes: The command runs as the SQL Server Service account
No: The command runs as the Agent proxy account
(xp_sqlagent_proxy_account)
No and no proxy account available: Fail.





"rallbee" <rallbee (AT) discussions (DOT) microsoft.com> wrote