This post describes an interesting scenario that I came across when working with a SSIS package developer. The main issue was that we were unable to run the SSIS package that contains a execute process task calling a batch file stored on a remote server machine (without being prompted by a warning window).
The following warning message window would pop up asking the user to either acknowledge the request or cancel it:
------------------------------------------------------------------------------------------------------
Open File - Security Warning
The publisher could not be verified. Are you sure you want to run this software ?
Name: Test.bat
Publisher: Unknown
Type: Windows Batch file
------------------------------------------------------------------------------------------------------
Because of this, when trying to executing the SSIS package through a SQL agent job, the job was doomed to fail if the user didn’t acknowledge the options shown by the security warning prompt.
Thus began our troubleshooting, where in, at first I verified that the user had the read-write-execute permission to the path where this batch file was saved.
Moving ahead, verified that UAC was turned off on the Server machine. Performing a simple test, we copied the batch file to the local machine and tried to execute the package from the Business Intelligence Studio. The package execution was successful and it didn’t bother to ask for a prompt.
Hence we clearly isolated the problem to be a permission specific issue. We also tried to set the command line parameter for the batch file with the switch " /C " , but it didn’t seem to help in anyway.
So this all trickled down to be a remote application permission launch issue. This is how we fixed the problem:
Launched Internet Explorer -> Internet options -> Security -> choose Internet -> Custom Level ->
Traverse up to Launching applications and unsafe files option and modified the setting (radio button) from the default value of " Prompt " to " Enable ".
Now when the end customer tried to run the SSIS package from Business Intelligence Studio \ SQL agent , there was no warning being prompted when the execute process task tried to run a remote batch file. Thus the SSIS was package was now able to complete successfully.
Author : Ajay(MSFT), SQL Developer Engineer, Microsoft
Reviewed by : Jason(MSFT), SQL Escalation Services, Microsoft