When I try to Execute a SSIS Package, I must give credentials to the Builtin\Users group on our SQL box to get any logs on the SQL box. I want this to run as a specified user because I don't want everyone to have these privileges. How do I set up Visual CRON to do this?

When I give the user I want to authenticate (logonscripts) has the appropriate SQL permissions to execute, but I take them away from Builtin\Users, I get the following error:

Unhandled exception: Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_ssis_getpackage', database 'msdb', schema 'dbo'.). The SQL statement that was issued has failed.
---> System.Runtime.InteropServices.COMException: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_ssis_getpackage', database 'msdb', schema 'dbo'.). The SQL statement that was issued has failed.

at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.LoadFromSQLServer(String bstrPackagePath, String bstrServerName, String bstrServerUserName, String bstrServerPassword, Boolean bLoadNeutral, IDTSEvents100 pEvents)
at Microsoft.SqlServer.Dts.Runtime.Application.LoadFromSqlServer(String packagePath, String serverName, String serverUserName, String serverPassword, IDTSEvents events)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Dts.Runtime.Application.LoadFromSqlServer(String packagePath, String serverName, String serverUserName, String serverPassword, IDTSEvents events)
at SSISExecute2008.SSIS2008.JDWth1a9Giv4FnVA0ZK(Object , Object , Object , Object , Object , Object )
at SSISExecute2008.SSIS2008.ExecutePackage(TaskSSISInfoClass task)

Exception in Task: Non zero exit code


The attached file is how I have VCRON configured.

Make sure that you are running the latest version and the Credential that you use have "Local login" and "Load profile" checked. Also you need to check "Trusted connection" in the Task.

---

Thanks for the quick reply on this! Also, I meant to attach the Execute SSIS package screen instead of Main Settings.

The user was set up with "Local login" and "Load profile" checked when I received this error.

When I check "Trusted connection", and select my credentials for "Credential for trusted connection", I receive the error "Exception in Task: Non zero exit code" when I attempt to run this task. Correct screenshot of how this is setup is attached.

I also tried checking "Trusted connection" and leaving the credentials blank, but still got the original error reported. Also, I checked the SQL Server Security logs, and in both scenarios there was an event for the user "AUTOMATION01$", not the user I want to authenticate (fbroerin).

Also, I'm not sure if it matters, but the SQL users we are using connect through Windows authentication instead of SQL authentication.

Thanks,
Frank

I played with this a little more, and was able to get this to work. Here is how I have it set up:

• Checked "Trusted connection"
  
• Unchecked "Load profile" and "Local user" for the user (this is what got past the "Exception in Task: Non zero exit code" error
  
• SQL permissions to db_ssisoperator in the msdb database

Just curious, what do the "Local login" and "Load user profile" options do when they are selected?

Thanks,
Frank

Local login and Load profile tries to load the profile locally on the machine. That means all settings related to the profile. It is often needed for correct impersonation - it will really "be" that user instead of getting only the rights of that user.

---