Hello,

I'm calling a DTS package from my asp.net application.Apparently because of
permission issue I canot run the package within the sql server ,because when
I set it to call a package from my local host it can execute te package.one
step before calling the package I get the IDENTITY of the current security
context and it is my Domian user name and I'm using IntegratedSecurity to
call the DTS package.I'm also memeber of sysadmin rols in Database.Do I have
to give it extra permissions?

Thasnk

does SQL server is on the same server as your ASP.net application?
do you use NTLM or Basic authentication?
have you setup the impersonation in the web.config file?

"Ray5531" <Ray5531 (AT) microsoft (DOT) com> wrote

Thanks Jeje,

the security context of my account "Domain\My UserName".



Thanks again


"Jéjé" <willgart (AT) BBBhotmailAAA (DOT) com> wrote

ok
the problem is a Kerberos issue!

are you using the active directory?
trust your webserver in the active directory, this allow the computer to
delegates the end user access. (look at the properties of the computer
account in the AD)
if your ASP.Net application run under a specific user account (your app pool
run under a domain user), then you have to download the setspn utility.
then use it like this:

setspn -a http/webservername domain\apppooluser

this command allow the domain\apppooluser user to delegates the end user
login during impersonation.

but...
1. your end user must be in the Active Directory too
2. the client computer must be on the same network withtout firewall between
the station and the web server (the kerberos delegation dislike firewalls)

or use the basic authentication for the web server, because the basic
authentication "display" the password, then the ASP.net application use the
login / password when there is a back end access.

"J-T" <JT (AT) nospam (DOT) com> wrote

Thanks for your nice reply.
I cannot implement kerbreros.How about this?

I will impersonate under a local account of web server and then I duplicate
that account in sql server as SQL SERVER Authentication and then I will give
that account all the right permission sets to run the packages .

Thanks

"Jéjé" <willgart (AT) BBBhotmailAAA (DOT) com> wrote

if using the logged user account is optional and if you can impersonate
using another user account, then try it. (provide a spcific username /
password in the impersonate tag of the web.config file)

or... you can open a DTS package by providing a specific user / password
instead-of the integrated security. also use SQL Server accounts for the
connections in your package.


"J-T" <JT (AT) nospam (DOT) com> wrote

I did this one and I gave the sql server account all the administative
permissions then in my code I load the package using the following line:
package.LoadFromSQLServer("MyServer","username","p assword",
DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, null,
null, null, packageName, ref pVarPersistStgOfHost);

It loads perfectly (same as before) but it dosen;t execute the package !!!!
it thorws no exception ,nothing!! If I enable event sink it invokes
OnQueryCancle twice and then nothing happens and it goes to the next line
which is uninitialization of the package!!! I'm so disappointed at this,here
is my code

Package2Class package = new Package2Class();
UCOMIConnectionPointContainer CnnctPtCont =
(UCOMIConnectionPointContainer) package;
UCOMIConnectionPoint CnnctPt;
Guid guid = new Guid("10020605-EB1C-11CF-AE6E-00AA004A34D5"); // UUID
of PackageEvents Interface
CnnctPtCont.FindConnectionPoint(ref guid, out CnnctPt);
int iCookie;


//PackageEventsSink PES=new PackageEventsSink();
CnnctPt.Advise(PES, out iCookie);
object pVarPersistStgOfHost = null;


package.LoadFromSQLServer("MyServer","username","p assword",
DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, null,
null, null, packageName, ref pVarPersistStgOfHost);

package.Execute();
package.UnInitialize();
package = null;
CnnctPt.Unadvise(iCookie); //a connection that is created by
IConnectionPoint.Advise must be closed by calling IConnectionPoint.Unadvise
to avoid a memory leak



*** Package Event Sink***

public class PackageEventsSink :
CollectionBase,Microsoft.SQLServer.DTSPkg80.Packag eEvents
{
public PackageEventsSink()
{
}

public void Add(DTSEvent item)
{
List.Add(item);
}

public DTSEvent this[int index]
{
get {return List[index] as DTSEvent;}
set {List[index] = value;}
}


#region PackageEvents Members

public void OnQueryCancel(string EventSource, ref bool pbCancel)
{
this.Add(new DTSEvent(EventSource, "OnQueryCancel", 0, 0, 0));
pbCancel=false;
}

public void OnStart(string EventSource)
{
this.Add(new DTSEvent(EventSource, "started", 0, 0, 0));
WriteLog(string.Format("{0} has started at time: {1}", EventSource,
System.DateTime.Now.ToString()));
}

public void OnProgress(string EventSource, string ProgressDescription, int
PercentComplete, int ProgressCountLow, int ProgressCountHigh)
{
try
{
this.Add(new DTSEvent(EventSource, ProgressDescription, PercentComplete,
ProgressCountLow, ProgressCountHigh));
WriteLog(string.Format("{0} has just raised an On Progress event with
{1} as the description of the event.",EventSource,ProgressDescription));
}
catch(System.Threading.ThreadStateException tEx)
{
throw tEx;
}
catch(System.Exception sExc)
{
throw sExc;
}

}

public void OnError(string EventSource, int ErrorCode, string Source,
string Description, string HelpFile, int HelpContext, string
IDofInterfaceWithError, ref bool pbCancel)
{
this.Add(new DTSEvent(EventSource, "Error", 0, 0, 0));
WriteLog(string.Format("{0} has just raised an On Error event",Source));
}

public void OnFinish(string EventSource)
{
this.Add(new DTSEvent(EventSource, "Finished", 0, 0, 0));
WriteLog(string.Format("{0} has Finished at time: {1}", EventSource,
System.DateTime.Now.ToString()));
}

private void WriteLog(string text)
{
System.IO.StreamWriter stW;

if (!File.Exists(Constants.CONFIG_AUDIT_FILE))
{
stW = File.CreateText(Constants.CONFIG_AUDIT_FILE);

}
else
{
stW = new StreamWriter(Constants.CONFIG_AUDIT_FILE,true);
}

stW.WriteLine(string.Format(@"{0} : {1}", DateTime.Now.ToString(),text));
stW.Close();
}


#endregion

}

"Jéjé" <willgart (AT) BBBhotmailAAA (DOT) com> wrote

I think I found the problem.
The reason my DTS packages didn;t get excuted was that I used trusted
connections inside my packages and because my ASP.NET application can not
delegate callers credential to the pakcgaes (or in general to a remote sql
server box) the package got executed with null and then my connection tasks
inside the package (which were using trusted connection) were unable to be
connected and tasks based on them were not executed.

"J-T" <JT (AT) nospam (DOT) com> wrote