For security reason, the SQL service on the production db server is decided to run on local system account instead of a domain account. I have a DTS package on production db server that execute a stored procedure which retrieve data from a linked server.
On the production server, I set up an ODBC connection that connect to the linked server using domain account and the stored procedure will select data from this linked server.
When I scheduled the DTS package as job, the job failed. But if I reset the SQL Service to run as the same domain account which I used to setup the ODBC, it worked.
The idea is the SQL service on production has to run using local system account.
Please let me know how to work around this issue.