FTP doesn’t work with proxy accounts in SQL Agent – calloc error
Not sure this is a windows server 2008 R2 feature or more wide spread, I would guess that it applies to all windows operating systems.
If you try and user a proxy account for a sql agent job that does an FTP using the windows FTP.exe, whether through SSIS or through a CMDExec job step it might fail.
What’s infuriating is that the error doesn’t get reported on the stdout stream but on the stderr output so you need to make sure you are capturing the stderr output as weel you get the error.
The error is something like
GetTempFileName calloc:I/O Error
What is odd is that even though the SQL Agent is impersonating the relevant proxy the environment variables are that of the base SQL Agent service account. This means that when the FTP application tries to get an temp file it tries to get it in the temp folder specified in the environment variables which is that of the SQL Agent service account. unless your proxy is running with Admin privileges it won’t have access to this folder as its in someone else’s user space.
Solution
There are two solutions and I don’t like either.
1. The first is to give the proxy user read and write access to the temp folder of the SQL Agent service account.
Or
2. You change the temp location of the proxy account to something that it will have access to. You could do this through GPOs but its still a pain.
So if you get odd errors with FTP, like files not getting downloaded check the users has access to the temp folder. To find the temp folder, setup a SQL Agent job with the specified proxy with 1 step that is an "Operating System (CmdExec) step” with the command as “set”
This will show you the environment variables and importantly the temp folder location.