I have a SQL 2008 server (SQL2008S1) with Excel installed on it. I had to migrate databases and DTS packages from a SQL 2000 server (SQL2000S2) to this server. 1 DTS package is trying to access an Excel file on a remote server like \\SQL2000S2\C$\directory\someExcelFile.xls. The SQL server and agent on SQL2008S1 are both running under same service account (Domain\SQL2008S1_service) and has full permissions on \\SQL2000S2\C$\directory. I even granted Domain\SQL2008S1$ (machine name) full access to \\SQL2000S2\C$\directory\
When I open the DTS package on SQL2008S1 and execute it manually, it runs perfectly fine. But when I schedule it as job, the job reports:
Executed as user:
Domain\SQL2008S1_service.
…p_DTSExecutePackageTask_5 DTSRun
OnError:
DTSStep_DTSExecutePackageTask_5, Error
= -2147467259 (80004005) Error string: DTSStep_DTSExecuteSQLTask_2:
The Microsoft Jet database engine
cannot open the file
'\\SQL2000S2\C$\directory\someExcelFile.xls'.
It is already opened exclusively by
another user, or you need permission
to view its data.
I am 100% sure that the files are not open and I thought I have granted enough rights to the SQL agent/SQL service account.
Any help please?
Best Answer
Have you granted the account Domain\SQL2008S1_service admin rights to the server SQL2000S2? You will need to do so in order to use the C$ network share.