DTS web service problem

[Graham]

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

 

[[MSFT]]

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.)

 

[Graham]

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

 

[[MSFT]]

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.)

 

[DalePres]

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

 

[[MSFT]]

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.)

 

[Graham]

Dale,

Thanks for the info. Will give it a try.

Graham

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

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