I have a SQL Server Job running on server X. Now I have to delete the existing AS(Analysis Services) backup files on server Y and create a fresh backup again. I wrote the required code in a batch file and saved it on hard drive of server X. I am executing this batch file using an execute process task in SSIS.
This SSIS package which is on server X will be run through a SQL Server Job at a particular time in every week.
This batch file executes successfully if I run the package manually, but it's failing if I run through a scheduled job! I tried this many times but in vain.
Error message says that "Access Denied" for deleting the files on server Y.
Any help would be appreciated!
When you manually execute the batch file, your credentials are presented to server Y, they are accepted and the OS deletes the file as requested.
When the SQL Agent run the same file, the SQL Agent service account's credentials are presented to server Y. Either they are being rejected by the server-it's not authorized to even look at the box or it can read and list the files on \ServerY\SomeShare but ultimately it is not able to delete a file.
Option 1) On Server Y, add the account that corresponds to Server X's SQL Agent Service Account with sufficient privileges to see the folder where the AS backup is and delete the backup.
Option 2) Create a credential and a proxy on Server X that has the above permissions on Server Y and then execute the SSIS package as that user, assuming of course the credential has all the other permissions Server X's Agent account does.