Microsoft.SqlServer.Dts.Runtime ASP.NET Security Issue

I am struggling in calling an SSIS package programatically using the Microsoft.SqlServer.Dts.Runtime namespace.

I am succesfuly connecting to the package insofar as I am able to retrieve the package ID (GUID), but when I call package.Execute I get a 'login failed for user' error, which indicates a security problem.

My ASP.NET app is running as a domain user which has temporary 'SA' rights on the server where the package is hosted. In addition, I have set the protection level on the package to 'DontSaveSensitive'.

What am I missing to be able to execute the package remotely?

TIA,

Rick

This depends on how you deployed your packages -- I've encountere this problem a few times myself.

If you've deployed your package on the server, one of the things that you may want to check are the SQL security settings e.g., Windows authentication will go kaput if you have a firewall between your web and db servers, etc.

If you deployed it through file system you might want to check your package's ProtectionLevel property. The default EncriptSensitiveWithUserKey is the way to go (at least in my case) for this.