SSIS Permission Problem

[vuyiswam]

Good Day All

I have a Package created and hosted in a Machine(Theresa) that has SQL 2008 and i have my Development Machine(Vuyiswa) that has IIS and am Debugging from the my machine. i have a ASP.NET 2.0 App and am executing a Package that in another machine. The First thing i did was to share a directory where the Packages are stored when i install the packages and supplied "Everyone","ASP.NET","Vuyiswa" Accounts permissions to to access this share. i can go into the share from the another machine without any problem. i have a code like this in my ASP.NET App

try
{
//Start the SSIS Here
Application app = new Application();
Package package = null;
package = app.LoadPackage(@"D:\Program Files\Microsoft SQL Server\100\DTS\Packages\OMEGA\OMEGA.dtsx", null);
//@"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Packages\OMEGA\OMEGA.dtsx", null);

Variables vars = package.Variables;

vars["Time1"].Value = time;

vars["Time2"].Value = time;

vars["Time3"].Value = time;

vars["TTBLTYPE"].Value = THREAD_DATA[1].ToString();

//package.Connections["SQLNCLI10.1"].ConnectionString = obj.GetConnectionString(THREAD_DATA[0].ToString());
//Excute Package
Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();

if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
{
foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in package.Errors)
{

Console.WriteLine("Package Execution results: {0}", local_DtsError.Description.ToString());
Console.WriteLine();
}
}

}
catch (DtsException ex)
{
Exception = ex.Message;
}

This execute Fine until the

Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();

it does not stop here , but i trap it here

if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)



and the Error am Getting is


"SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.\r\nAn OLE DB record is available. Source:\"Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'sa'

On my Package i use the "sa" credentials and my Protection Level is "Dont Save Sensative"

Thank you


Vuyiswa Maseko

[toddmcdermid]

A couple things to change/check:

1. Don' t use mapped drives. Mapped drives aren't restored in a user-profile-less session, regardless of who you're "running as" in ASP.Net. Use UNCs instead. Notes on running SSIS packages remotely: http://msdn.microsoft.com/en-us/library/ms403355.aspx
2. Make sure you have SSIS tools installed on the ASP.Net machine - because that's where you're attempting to execute your packages. Calling Execute is NOT like running a T-SQL statement - it executes locally. (It does appear from the error message that you do have the tools installed.)
3. The above should be fixed/checked - but the root of your problem is likely the ProtectionLevel of the package is still at the default of EncryptSensitiveWithUserKey. Unless you have the impersonation for executing the package set to YOU, it will not load any saved passwords in Connection Managers. I recommend using EncryptSensitiveWithPassword, and providing the password in your application. Read up on this setting here: http://msdn.microsoft.com/en-us/library/ms141747.aspx

If you continue to have difficulties, post on the MSDN SSIS Forums, they are extremely active: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/threads

[vuyiswam]

Good Day toddmcdermid

Thank you for your Reply.

toddmcdermid Wrote :
1. Don' t use mapped drives. Mapped drives aren't restored in a user-profile-less session, regardless of who you're "running as" in ASP.Net. Use UNCs instead. Notes on running SSIS packages remotely: http://msdn.microsoft.com/en-us/library/ms403355.aspx

The Approach in the Link you gave me is for SQL Job. Am Creating a Package and running it from the code. i don't create a Job First. i wanted to do it that way but now this led me to enable some features that will lead me to security vulnerability. "xp_cmdshell" so i have resolved the problem by using dts.config file. i had to supply the sa password to that file and it worked.

toddmcdermid Wrote :
Make sure you have SSIS tools installed on the ASP.Net machine - because that's where you're attempting to execute your packages. Calling Execute is NOT like running a T-SQL statement - it executes locally. (It does appear from the error message that you do have the tools installed.)

To my understanding i only need to install SSIS in the database server not the SSIS. Because these SSIS packages are executed on the Database Server. After the resolution of my problem, still i dont have SSIS tools installed in the IIS machine. The Problem was that the password was not saved in the Connection Manager of the Package.

toddmcdermid Wrote :
3. The above should be fixed/checked - but the root of your problem is likely the ProtectionLevel of the package is still at the default of EncryptSensitiveWithUserKey. Unless you have the impersonation for executing the package set to YOU, it will not load any saved passwords in Connection Managers. I recommend using EncryptSensitiveWithPassword, and providing the password in your application. Read up on this setting here: http://msdn.microsoft.com/en-us/library/ms141747.aspx


In my first post, i stated that the Protection Level is in "Dont Save Sensative" so this means it cant be the Problem


I will like to take this chance and thank you for your reply, you have given very good response and the links are usefull, especially the ado.net approach. i will give you a reputaion because you deserve it. i posted the same question in the msdn and noone could figure it out.

Keep up the Good work

Kind Regards


Vuyiswa Maseko