SQL Server Job - The system cannot find the file specified

Question

I am running a SQL Server Agent job which zips up my database backups. The application I run is a C# Windows Application which takes in the path of the database as an argument. When I run the application form a command prompt all works well. However when I run it from within a sql server job using the Operating System (CmdExec) as the type I get the following error.

“The system cannot find the file specified”

Within the job I have the following as the command:

D:\DatabaseBackups\DatabaseBackupManager.exe D:\DatabaseBackups\SubDit\Database.bak

I have tried setting specific directory permissions for the SQL Server Agent account on the DatabaseBackups folder but still get the same error.

Can anybody throw any light onto this one as Google did not throw up anything that worked and is starting to drive me a crazy.

Thanks

Answer 1

First, make sure your .exe is being actually found by SQL. If so, next thing I would do is to put some logic in DatabaseBackupManager.exe to gather the ACL's the thread has on that file (e.g. DirectorySecurity DirSec = di.GetAccessControl(AccessControlSections.All);). Most probably, SQL is trimming ACL's when launching your process. If so, then you may need to impersonate that thread in order to access that file, or alternatively, find out where that process is being granted r/w access to e.g. %temp%.

Answer 2

The D: drive has to exist as a local drive, not a mapped drive. (Unless there's some way to force that to be mapped for other users who aren't logged on of which I am unaware)

Also, as far as permissions, it could be just about anything related to the account that the agent is running under.

You might need to put some logging into your C# process assuming it's starting your process at all. If it's not starting your process, it could be some permissions on that account related to the directory or even the .NET framework settings.

Also, you can use Windows to audit the failures to see which file it's attempting to open.

If you add the agent account temporarily to the administrators group and it works, then this points to permissions being the source of the not terribly helpful “The system cannot find the file specified” error.

Answer 3

Thanks for your help guys. I tried everything you suggested and found out that it was looking in the C:\Windows\System32 folder for the 7za.exe that I was using to zip up my backups. I had referenced the exe as if it was being called from the same directory that my application was running in.

Thanks again