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.

Published Monday, November 8, 2010 1:42 PM by simonsabin

Comments

Wednesday, December 1, 2010 11:08 AM by RealTime - Questions: "Sql wont return anything with "="?"

# RealTime - Questions: "Sql wont return anything with "="?"

Pingback from  RealTime - Questions: "Sql wont return anything with "="?"