DTS web service problem

Discussion in 'ASP .Net Web Services' started by Graham, Jun 7, 2004.

  1. Graham

    Graham Guest

    I'm trying to create a web service that will allow a remote authenticated
    user to start a DTS package. I've created the web service method as follows;

    [WebMethod]
    public bool TestDTS(string serverName, string packageName)
    {
    DTS.Package pkg = new DTS.PackageClass();
    object pVarPersistStgOfHost = null;
    pkg.LoadFromSQLServer(serverName, "", "",
    DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection,
    "", "", "", packageName, ref pVarPersistStgOfHost);
    pkg.Execute();
    }

    and added the appropriate COM reference and set up IIS to accept only
    Windows authentication requests. Additionally I've set the <identity
    impersonate="true" /> entry in the web.config file as I need the DTS package
    to be run under the user 's account.

    The DTS code in the method works as expected, however when I execute the
    method, the package always executes under the local ASPNET user account not
    the user's account therefore can't log into my database. Anyone any ideas on
    how to get the package to run under the user's account?

    Thanks,
    Graham
    Graham, Jun 7, 2004
    #1
    1. Advertising

  2. Graham

    [MSFT] Guest

    Hi Graham,

    I suggest you may add a web method to the web service and return following
    string:

    System.Security.Principal.WindowsIdentity.GetCurrent().Name

    This will return the actual account the web service is running with. If
    this is "ASPNET", Change the account that the Aspnet_wp.exe process runs
    under to the System account in the <processModel> configuration section
    of the Machine.config file. To to the impersonate, the account should
    have enough permission. "ASPNET" account may not have such permission.

    Hope this help.

    Luke
    Microsoft Online Support

    Get Secure! www.microsoft.com/security
    (This posting is provided "AS IS", with no warranties, and confers no
    rights.)
    [MSFT], Jun 8, 2004
    #2
    1. Advertising

  3. Graham

    Graham Guest

    Luke,

    Thanks for the post. I've done as you suggested and the web service does run
    under the authenticated account (e.g. domain\user) as you'd expect as
    <identity impersonate="true" /> in the web.config file. However the DTS
    package doesn't run as you'd expect.

    I've enhanced the code that calls the DTS package a bit as per the knowledge
    base article "HOW TO: Handle Data Transformation Services Package Events in
    Visual C# .NET"
    (http://support.microsoft.com/default.aspx?scid=kb;en-us;319985) and here's
    the wierd thing, when the Run() method is called, the username returned by
    System.Security.Principal.WindowsIdentity.GetCurrent().Name is the
    authenticated user (e.g. domain\user)

    Everything is fine until the call to the package.Execute() method call which
    looks like it is called by domain\user. The OnQueryCancel event is the first
    to be called and System.Security.Principal.WindowsIdentity.GetCurrent().Name
    still returns domain\user. The next event to be fired is OnError(). The
    description in this event is telling me that the ASPNET account doesn't have
    login permissions to the SQL Server however the DTS package should be
    running under the domain\user account!!

    The domain\user account has sufficient permissions to successfully run the
    DTS package via the SQL Enterprise Manager and I've tried running the
    changing the <processModel> setting to use the SYSTEM account but it didn't
    make any difference.

    Is there some wierd COM Interop security thing going on here?

    Thanks,
    Graham
    Graham, Jun 8, 2004
    #3
  4. Graham

    [MSFT] Guest

    Hi Graham,

    I performed a test and get same result with you. With deep research, I
    found the problem occur when interop with DST COM component. The
    impersonate we made in ASP.NET didn't work here. DTS COM still use the
    account for ASP.NET process. I suggest you may consider create a COM+
    component to call the DTS package and call the COM+ component in ASP.NET.
    This should be able to pass right account.

    Luke
    Microsoft Online Support

    Get Secure! www.microsoft.com/security
    (This posting is provided "AS IS", with no warranties, and confers no
    rights.)
    [MSFT], Jun 9, 2004
    #4
  5. Graham

    DalePres Guest

    I'm not sure it's clear in the previous responses so let me simplify the
    whole thing. What you're trying to do will only work if IIS and SQL Server
    are on the same machine. If not, you would have to use SQL Server logins in
    your connection string to access the SQL Server from your webservice.

    Dale

    "Graham" <> wrote in message
    news:%...
    > I'm trying to create a web service that will allow a remote authenticated
    > user to start a DTS package. I've created the web service method as

    follows;
    >
    > [WebMethod]
    > public bool TestDTS(string serverName, string packageName)
    > {
    > DTS.Package pkg = new DTS.PackageClass();
    > object pVarPersistStgOfHost = null;
    > pkg.LoadFromSQLServer(serverName, "", "",
    > DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection,
    > "", "", "", packageName, ref pVarPersistStgOfHost);
    > pkg.Execute();
    > }
    >
    > and added the appropriate COM reference and set up IIS to accept only
    > Windows authentication requests. Additionally I've set the <identity
    > impersonate="true" /> entry in the web.config file as I need the DTS

    package
    > to be run under the user 's account.
    >
    > The DTS code in the method works as expected, however when I execute the
    > method, the package always executes under the local ASPNET user account

    not
    > the user's account therefore can't log into my database. Anyone any ideas

    on
    > how to get the package to run under the user's account?
    >
    > Thanks,
    > Graham
    >
    >
    DalePres, Jun 10, 2004
    #5
  6. Graham

    [MSFT] Guest

    Thank for Dale's suggestion. It is also a proper solution for the issue.

    Luke
    Microsoft Online Support

    Get Secure! www.microsoft.com/security
    (This posting is provided "AS IS", with no warranties, and confers no
    rights.)
    [MSFT], Jun 10, 2004
    #6
  7. Graham

    Graham Guest

    Dale,

    Thanks for the info. Will give it a try.

    Graham


    "DalePres" <> wrote in message
    news:...
    > I'm not sure it's clear in the previous responses so let me simplify the
    > whole thing. What you're trying to do will only work if IIS and SQL

    Server
    > are on the same machine. If not, you would have to use SQL Server logins

    in
    > your connection string to access the SQL Server from your webservice.
    >
    > Dale
    >
    > "Graham" <> wrote in message
    > news:%...
    > > I'm trying to create a web service that will allow a remote

    authenticated
    > > user to start a DTS package. I've created the web service method as

    > follows;
    > >
    > > [WebMethod]
    > > public bool TestDTS(string serverName, string packageName)
    > > {
    > > DTS.Package pkg = new DTS.PackageClass();
    > > object pVarPersistStgOfHost = null;
    > > pkg.LoadFromSQLServer(serverName, "", "",
    > > DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection,
    > > "", "", "", packageName, ref pVarPersistStgOfHost);
    > > pkg.Execute();
    > > }
    > >
    > > and added the appropriate COM reference and set up IIS to accept only
    > > Windows authentication requests. Additionally I've set the <identity
    > > impersonate="true" /> entry in the web.config file as I need the DTS

    > package
    > > to be run under the user 's account.
    > >
    > > The DTS code in the method works as expected, however when I execute the
    > > method, the package always executes under the local ASPNET user account

    > not
    > > the user's account therefore can't log into my database. Anyone any

    ideas
    > on
    > > how to get the package to run under the user's account?
    > >
    > > Thanks,
    > > Graham
    > >
    > >

    >
    >
    Graham, Jun 16, 2004
    #7
    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. Stephen Russell

    Deploy a DTS package on a Web Server C#

    Stephen Russell, Aug 13, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    700
    Ivan Demkovitch
    Aug 13, 2003
  2. Page Horton

    Problem with DTS package

    Page Horton, Jan 7, 2005, in forum: ASP .Net
    Replies:
    6
    Views:
    2,022
    Page Horton
    Jan 7, 2005
  3. Leo
    Replies:
    1
    Views:
    466
    Clamps
    Sep 1, 2005
  4. =?Utf-8?B?R29yZG9u?=

    Problem using SQL Server DTS in a Web Form

    =?Utf-8?B?R29yZG9u?=, Feb 25, 2007, in forum: ASP .Net
    Replies:
    0
    Views:
    385
    =?Utf-8?B?R29yZG9u?=
    Feb 25, 2007
  5. Scott Good

    Connecting different Apps with Web Services or DTS??

    Scott Good, Feb 4, 2004, in forum: ASP .Net Web Services
    Replies:
    1
    Views:
    115
    Jan Tielens
    Feb 5, 2004
Loading...

Share This Page