Cannot execute DTS package

Discussion in 'ASP .Net Security' started by Peter Afonin, Sep 14, 2004.

  1. Peter Afonin

    Peter Afonin Guest

    Hello,

    Our SQL server used to run under System account, and I had no problems
    executing DTS packages from the ASP.NET:

    Dim oPkg As DTS.Package
    oPkg = CreateObject("DTS.Package")
    oPkg.LoadFromSQLServer("WIN2000", , ,
    DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , ,
    "Import DPCK")
    oPkg.Execute()
    oPkg.UnInitialize()
    oPkg = Nothing

    Then we switched to the different account, and I cannot execute packages
    anymore. I tried all accounts, including thos that have full access and
    rights. If I use

    DTSSQLStgFlag_UseTrustedConnection

    I get this error

    System.Runtime.InteropServices.COMException (0x80040E4D): Login failed for
    user 'NT AUTHORITY\NETWORK SERVICE'. at
    DTS._Package.LoadFromSQLServer(String ServerName, String ServerUserName,
    String ServerPassword, DTSSQLServerStorageFlags Flags, String
    PackagePassword, String PackageGuid, String PackageVersionGuid, String
    PackageName, Object& pVarPersistStgOfHost) at
    MakeXeroxFiles.MakeFiles.btnMake_Click(Object sender, EventArgs e)

    If I use the flag DTSSQLStgFlag_Default and specify user name and password,
    I still get similar message:

    System.Runtime.InteropServices.COMException (0x80040E4D): Login failed for
    user 'SPECIALTY\pafo'. at DTS._Package.LoadFromSQLServer(String ServerName,
    String ServerUserName, String ServerPassword, DTSSQLServerStorageFlags
    Flags, String PackagePassword, String PackageGuid, String
    PackageVersionGuid, String PackageName, Object& pVarPersistStgOfHost) at
    MakeXeroxFiles.MakeFiles.btnMake_Click(Object sender, EventArgs e)

    If I add <identity impersonate="true" /> to the Web.config, I don't get an
    error anymore, but the package doesn't execute either.

    What can I do? I would appreciate your help very much.

    Thank you,


    --
    Peter Afonin
    Peter Afonin, Sep 14, 2004
    #1
    1. Advertising

  2. Peter Afonin

    Hermit Dave Guest

    good old asp.net.
    Whats happening is that it is trying to execute the Query with context of NT
    AUTHORITY\NETWORK SERVICE
    since you are using trusted connection.

    so either set up the above user to access SQL & the database.
    or just use impersonate identity tag to impersonate a fixed user.

    matrix detailing the execution context with asp.net
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetAP05.asp

    code project article which has snippet to allow aspnet account (worker
    process in 2003 runs under Network Server) to the database
    http://www.codeproject.com/cs/database/sql_in_csharp.asp

    --

    Regards,

    Hermit Dave
    (http://hdave.blogspot.com)
    "Peter Afonin" <> wrote in message
    news:...
    > Hello,
    >
    > Our SQL server used to run under System account, and I had no problems
    > executing DTS packages from the ASP.NET:
    >
    > Dim oPkg As DTS.Package
    > oPkg = CreateObject("DTS.Package")
    > oPkg.LoadFromSQLServer("WIN2000", , ,
    > DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , ,
    > "Import DPCK")
    > oPkg.Execute()
    > oPkg.UnInitialize()
    > oPkg = Nothing
    >
    > Then we switched to the different account, and I cannot execute packages
    > anymore. I tried all accounts, including thos that have full access and
    > rights. If I use
    >
    > DTSSQLStgFlag_UseTrustedConnection
    >
    > I get this error
    >
    > System.Runtime.InteropServices.COMException (0x80040E4D): Login failed for
    > user 'NT AUTHORITY\NETWORK SERVICE'. at
    > DTS._Package.LoadFromSQLServer(String ServerName, String ServerUserName,
    > String ServerPassword, DTSSQLServerStorageFlags Flags, String
    > PackagePassword, String PackageGuid, String PackageVersionGuid, String
    > PackageName, Object& pVarPersistStgOfHost) at
    > MakeXeroxFiles.MakeFiles.btnMake_Click(Object sender, EventArgs e)
    >
    > If I use the flag DTSSQLStgFlag_Default and specify user name and

    password,
    > I still get similar message:
    >
    > System.Runtime.InteropServices.COMException (0x80040E4D): Login failed for
    > user 'SPECIALTY\pafo'. at DTS._Package.LoadFromSQLServer(String

    ServerName,
    > String ServerUserName, String ServerPassword, DTSSQLServerStorageFlags
    > Flags, String PackagePassword, String PackageGuid, String
    > PackageVersionGuid, String PackageName, Object& pVarPersistStgOfHost) at
    > MakeXeroxFiles.MakeFiles.btnMake_Click(Object sender, EventArgs e)
    >
    > If I add <identity impersonate="true" /> to the Web.config, I don't get an
    > error anymore, but the package doesn't execute either.
    >
    > What can I do? I would appreciate your help very much.
    >
    > Thank you,
    >
    >
    > --
    > Peter Afonin
    >
    >
    Hermit Dave, Sep 14, 2004
    #2
    1. Advertising

  3. Peter Afonin

    Peter Afonin Guest

    Thank you very much, Hermit, it worked.

    The problem was that when I tried to add 'NT AUTHORITY\NETWORK SERVICE'
    manually in Enterprise Mgr - I couldn't find it (I don't understand why),
    but when I ran
    EXEC sp_grantlogin 'NT AUTHORITY\NETWORK SERVICE' - it worked.

    Peter

    "Hermit Dave" <> wrote in message
    news:...
    > good old asp.net.
    > Whats happening is that it is trying to execute the Query with context of

    NT
    > AUTHORITY\NETWORK SERVICE
    > since you are using trusted connection.
    >
    > so either set up the above user to access SQL & the database.
    > or just use impersonate identity tag to impersonate a fixed user.
    >
    > matrix detailing the execution context with asp.net
    >

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetAP05.asp
    >
    > code project article which has snippet to allow aspnet account (worker
    > process in 2003 runs under Network Server) to the database
    > http://www.codeproject.com/cs/database/sql_in_csharp.asp
    >
    > --
    >
    > Regards,
    >
    > Hermit Dave
    > (http://hdave.blogspot.com)
    > "Peter Afonin" <> wrote in message
    > news:...
    > > Hello,
    > >
    > > Our SQL server used to run under System account, and I had no problems
    > > executing DTS packages from the ASP.NET:
    > >
    > > Dim oPkg As DTS.Package
    > > oPkg = CreateObject("DTS.Package")
    > > oPkg.LoadFromSQLServer("WIN2000", , ,
    > > DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , ,
    > > "Import DPCK")
    > > oPkg.Execute()
    > > oPkg.UnInitialize()
    > > oPkg = Nothing
    > >
    > > Then we switched to the different account, and I cannot execute packages
    > > anymore. I tried all accounts, including thos that have full access and
    > > rights. If I use
    > >
    > > DTSSQLStgFlag_UseTrustedConnection
    > >
    > > I get this error
    > >
    > > System.Runtime.InteropServices.COMException (0x80040E4D): Login failed

    for
    > > user 'NT AUTHORITY\NETWORK SERVICE'. at
    > > DTS._Package.LoadFromSQLServer(String ServerName, String ServerUserName,
    > > String ServerPassword, DTSSQLServerStorageFlags Flags, String
    > > PackagePassword, String PackageGuid, String PackageVersionGuid, String
    > > PackageName, Object& pVarPersistStgOfHost) at
    > > MakeXeroxFiles.MakeFiles.btnMake_Click(Object sender, EventArgs e)
    > >
    > > If I use the flag DTSSQLStgFlag_Default and specify user name and

    > password,
    > > I still get similar message:
    > >
    > > System.Runtime.InteropServices.COMException (0x80040E4D): Login failed

    for
    > > user 'SPECIALTY\pafo'. at DTS._Package.LoadFromSQLServer(String

    > ServerName,
    > > String ServerUserName, String ServerPassword, DTSSQLServerStorageFlags
    > > Flags, String PackagePassword, String PackageGuid, String
    > > PackageVersionGuid, String PackageName, Object& pVarPersistStgOfHost) at
    > > MakeXeroxFiles.MakeFiles.btnMake_Click(Object sender, EventArgs e)
    > >
    > > If I add <identity impersonate="true" /> to the Web.config, I don't get

    an
    > > error anymore, but the package doesn't execute either.
    > >
    > > What can I do? I would appreciate your help very much.
    > >
    > > Thank you,
    > >
    > >
    > > --
    > > Peter Afonin
    > >
    > >

    >
    >
    Peter Afonin, Sep 14, 2004
    #3
  4. Peter Afonin

    Hermit Dave Guest

    yeah i tried it through enterprise manager as well and finally i went the
    query analyser way to execute the stored procs :)

    --

    Regards,

    Hermit Dave
    (http://hdave.blogspot.com)
    "Peter Afonin" <> wrote in message
    news:...
    > Thank you very much, Hermit, it worked.
    >
    > The problem was that when I tried to add 'NT AUTHORITY\NETWORK SERVICE'
    > manually in Enterprise Mgr - I couldn't find it (I don't understand why),
    > but when I ran
    > EXEC sp_grantlogin 'NT AUTHORITY\NETWORK SERVICE' - it worked.
    >
    > Peter
    >
    > "Hermit Dave" <> wrote in message
    > news:...
    > > good old asp.net.
    > > Whats happening is that it is trying to execute the Query with context

    of
    > NT
    > > AUTHORITY\NETWORK SERVICE
    > > since you are using trusted connection.
    > >
    > > so either set up the above user to access SQL & the database.
    > > or just use impersonate identity tag to impersonate a fixed user.
    > >
    > > matrix detailing the execution context with asp.net
    > >

    >

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetAP05.asp
    > >
    > > code project article which has snippet to allow aspnet account (worker
    > > process in 2003 runs under Network Server) to the database
    > > http://www.codeproject.com/cs/database/sql_in_csharp.asp
    > >
    > > --
    > >
    > > Regards,
    > >
    > > Hermit Dave
    > > (http://hdave.blogspot.com)
    > > "Peter Afonin" <> wrote in message
    > > news:...
    > > > Hello,
    > > >
    > > > Our SQL server used to run under System account, and I had no problems
    > > > executing DTS packages from the ASP.NET:
    > > >
    > > > Dim oPkg As DTS.Package
    > > > oPkg = CreateObject("DTS.Package")
    > > > oPkg.LoadFromSQLServer("WIN2000", , ,
    > > > DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , ,
    > > > "Import DPCK")
    > > > oPkg.Execute()
    > > > oPkg.UnInitialize()
    > > > oPkg = Nothing
    > > >
    > > > Then we switched to the different account, and I cannot execute

    packages
    > > > anymore. I tried all accounts, including thos that have full access

    and
    > > > rights. If I use
    > > >
    > > > DTSSQLStgFlag_UseTrustedConnection
    > > >
    > > > I get this error
    > > >
    > > > System.Runtime.InteropServices.COMException (0x80040E4D): Login failed

    > for
    > > > user 'NT AUTHORITY\NETWORK SERVICE'. at
    > > > DTS._Package.LoadFromSQLServer(String ServerName, String

    ServerUserName,
    > > > String ServerPassword, DTSSQLServerStorageFlags Flags, String
    > > > PackagePassword, String PackageGuid, String PackageVersionGuid, String
    > > > PackageName, Object& pVarPersistStgOfHost) at
    > > > MakeXeroxFiles.MakeFiles.btnMake_Click(Object sender, EventArgs e)
    > > >
    > > > If I use the flag DTSSQLStgFlag_Default and specify user name and

    > > password,
    > > > I still get similar message:
    > > >
    > > > System.Runtime.InteropServices.COMException (0x80040E4D): Login failed

    > for
    > > > user 'SPECIALTY\pafo'. at DTS._Package.LoadFromSQLServer(String

    > > ServerName,
    > > > String ServerUserName, String ServerPassword, DTSSQLServerStorageFlags
    > > > Flags, String PackagePassword, String PackageGuid, String
    > > > PackageVersionGuid, String PackageName, Object& pVarPersistStgOfHost)

    at
    > > > MakeXeroxFiles.MakeFiles.btnMake_Click(Object sender, EventArgs e)
    > > >
    > > > If I add <identity impersonate="true" /> to the Web.config, I don't

    get
    > an
    > > > error anymore, but the package doesn't execute either.
    > > >
    > > > What can I do? I would appreciate your help very much.
    > > >
    > > > Thank you,
    > > >
    > > >
    > > > --
    > > > Peter Afonin
    > > >
    > > >

    > >
    > >

    >
    >
    Hermit Dave, Sep 14, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Peter Afonin

    Cannot execute DTS package from ASP.Net

    Peter Afonin, Aug 29, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    603
    Peter Afonin
    Aug 29, 2003
  2. Dean R. Henderson
    Replies:
    1
    Views:
    1,510
    Natty Gur
    Nov 13, 2003
  3. bruce barker
    Replies:
    1
    Views:
    450
    =?Utf-8?B?U2lsZWVzaA==?=
    Apr 2, 2004
  4. Peter Afonin

    Cannot execute DTS package

    Peter Afonin, Sep 14, 2004, in forum: ASP .Net
    Replies:
    3
    Views:
    3,715
    Hermit Dave
    Sep 14, 2004
  5. kevin bailey
    Replies:
    5
    Views:
    2,730
    Dino Chiesa [Microsoft]
    Nov 19, 2004
Loading...

Share This Page