.bat file works manually, but does nothing in SQL Server Agent job

Question

I am able to run a .bat file that copies an .exe file into a specific folder, and runs it to extract some files manually.

When I use it as part of a SQL Server Agent job (step 1), I get success for the job called "Daily_Update", but it doesn't actually do anything.

The server that the SQL Server is on is different from where the file location where the files are moved and executed. I am specifying the file location with the UNC Path. \\A10 is the server with the scripts and .dat files that I am manipulating, \\A08 is the one with the SQL Server.

With the disk designator c:\ notation, I get:

The system cannot find the path specified.

When I use the UNC path name, I get:

Access is denied.

When I run the .bat file from the desktop of the server where the SQL Server lives, it works fine manually.

Any ideas why this might be happening?

Answer 1

The SQL Server user account has access to that specific folder?
Try running a simple xp_cmdshell to check that.

EXEC master.dbo.xp_cmdshell 'dir C:\<path>'

Answer 2

By default configuration, SQL Server Agent doesn't run under the same user account as you log in to SSMS. For example, my "SQL Server Agent" system service runs under an sqlagent user account. The "SQL Server" service runs under dataeng. Queries in SSMS run under my own username: e.g. mpag

Make sure the username referred to in "log on as" for SQL Server Agent in your system services has read/execute (or rwx) access to the directory with the bat as well as the bat itself.

To do this

1. Follow @Dan's advice and do a quick check if the SQL Server service has access. But even if SQL Server does, SQL Server Agent - which you are attempting to use in your question - may not
2. Start->Run services.msc. Type Windows (not DB) admin username and password if prompted.
3. If no "Log In As" column appears, add it via View->Add/Remove Columns and switch to Detail view.
4. Scroll down to the SQL Server (MSSQLSERVER) and SQL Server Agent (MSSQLSERVER) entries and note the (domain\)username from Log In As column for each. Close out services.msc
5. Right click on the directory which has the .bat file in it in Windows Explorer (aka (My) Computer) and select Properties
6. Click into Security (tab) -> Edit (button) -> Add (button)
7. Add one of the user names from step #4 above and click "OK"
8. If there's a second username from step #4, click Add again, add the other username and click "OK"
9. Highlight the added username and click "Read & execute" and "List folder contents". If the script also generates an output file, also click "Write". Other permissions are at your discretion. Repeat if needed for the other username. Click OK
10. Verify the permissions on the file itself to make sure that the settings for the folder propagated to your batch file already in the folder. Essentially repeat step #5 (but with the file, rather than the folder) and, if needed, "repeat" steps 6-9. No "Write" permission should be needed this time.
11. If the destination of the copy operation is another folder, you'll need to enable permissions for that folder or directory tree also. Repeat 5-9 again (including "Write" access).