Azure VM上SQL Server Agent执行SSIS包调用Azure文件存储程序失败问题

Hey there, let's work through why your SSIS packages run fine manually but fail when triggered via SQL Server Agent jobs—this is a super common scenario with a handful of key things to check:

When you run the package manually, it uses your user account's permissions. But SQL Server Agent jobs run under the SQL Server Agent service account (usually NT SERVICE\SQLSERVERAGENT by default, or a custom account you set up). This account needs two critical permissions:

• Access to your Azure File Storage share: It must be able to reach the UNC path \\[storage name].file.core.windows.net\[storage name]. If your VM is domain-joined, you can add the Agent account to the Azure File share's RBAC permissions. If not, ensure the account can authenticate using the storage account key when accessing the share.
• Read & Execute rights on the EXE/batch files and their parent folder in the Azure File share. Double-check the share's NTFS permissions (if you've set them) to confirm the Agent account has these rights.

If you're using a mapped drive (like Z:) in your Execute Process Task, SQL Server Agent won't recognize it—drive mappings are tied to specific user sessions. Instead, update the task to use the full UNC path directly, e.g., \\[storage name].file.core.windows.net\[storage name]\YourScript.bat or \\[storage name].file.core.windows.net\[storage name]\YourApp.exe.

If you absolutely need a mapped drive, add a preliminary Command Prompt step to your Agent job to mount it dynamically, using your storage account credentials:

net use Z: \\[storage name].file.core.windows.net\[storage name] /user:[storage name] [your-storage-account-key] /persistent:no

Run this step before executing the SSIS package to ensure the drive is available for the job session.

• Confirm the Executable field uses the correct UNC path—no typos in the storage account name or share name.
• Set the WorkingDirectory to the UNC path of the Azure File share (not a local folder) if your batch/EXE relies on files in its current directory.
• Enable logging for the Execute Process Task: Turn on the StandardOutputVariable and StandardErrorVariable to capture error messages. This will show you exactly why the process failed (e.g., "file not found", "access denied").

Add logging to your SSIS package using the SQL Server Log Provider (or another provider of your choice). Configure it to log events from the Execute Process Task, including return codes and error details. When the Agent job fails, you can query this log to get granular info about what went wrong, instead of just a generic "job failed" message.

• Check the Run as account for the job step: If you're using a proxy account instead of the default Agent account, make sure that proxy has the same permissions we covered earlier.
• Verify the package path in the job step: Ensure you've selected the correct catalog, folder, and package version from the Integration Services Catalog.
• Confirm any package parameters related to file paths are set correctly—no hardcoded local paths that don't apply to the Agent's context.

To rule out permission issues, log into your Azure VM and test the Agent account's ability to run the EXE/batch file:

• If using a custom domain account, just log in with that account and try accessing the Azure File share and running the files.
• If using the default NT SERVICE\SQLSERVERAGENT account, use a tool like psexec to open a command prompt as that system account:

  psexec -s -i cmd.exe
  

  Then try navigating to the UNC path and running the EXE/batch file. If this fails, you've found your permission issue.

内容的提问来源于stack exchange，提问作者Justin Birmingham