RickGaribay.NET


Greetings,

I have an ASP.NET web form that calls out to a component which includes the following code which leverages the Application and Package classes in the Microsoft.SqlServer.Dts.Runtime namespace:

Application application = new Application();

Package package = application.LoadFromDtsServer(@"File System\MyPackages\MyPackage", "MyMachine", null);

string id = package.ID;

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

I am logged in to the app as Domain\Me.

I have Windows Integrated Authentication configured in IIS and am using Windows Authentication in ASP.NET. I am using impersonation to impersonate a fixed identity, say Domain\Bob. This has the following effect:

WindowIdentity.GetCurrent() returns Domain\Bob as expected since the process is being impersonated by Domain\Bob.

Thread.CurrentPrincipal.Identity.Name returns Domain\Me as expected since the ASP.NET application is using Windows Authentication.

The id variable successfuly is initialized to the GUID of the package, proving that the package name is resolving, however, from the ASP.NET application, the package fails.

Issuing the package.Errors[0] command at the command window provides the following information:

{Microsoft.SqlServer.Dts.Runtime.DtsError}
base {Microsoft.SqlServer.Dts.Runtime.DtsObject}: {Microsoft.SqlServer.Dts.Runtime.DtsError}
Description: "An OLE DB error has occurred. Error code: 0x80040E4D.\r\nAn OLE DB record is available. Source: \"Microsoft SQL Native Client\" Hresult: 0x80040E4D Description: \"Communication link failure\".\r\nAn OLE DB record is available. Source: \"Microsoft SQL Native Client\" Hresult: 0x80040E4D Description: \"Shared Memory Provider: I/O Error detected in read/write operation [4]. \".\r\nAn OLE DB record is available. Source: \"Microsoft SQL Native Client\" Hresult: 0x80040E4D Description: \"Login failed for user 'MyMachine\\ASPNET'.\".\r\n"
ErrorCode: -1071636471
HelpContext: 0
HelpFile: null
IDOfInterfaceWithError: "{8BDFE898-E9D8-4D23-9739-DA807BCDC2AC}"
Source: "Packages MyPackage"
SubComponent: "Connection manager \"DataMart\""
TimeStamp: {12/21/2006 2:18:55 PM}

For some reason, the ASPNET process account is being passed to the package!

This makes no sense, because clearly, Domain\Bob is impersonating the worker process and all downstream resources should be accessed via Domain\Bob. This includes SQL Server Database Engine and I would imagine SSIS as well. ASPNET should not come into play at all since I am impersonating.

I also tried using regular (non-fixed identity) impersonation (<identity impersonate="true" />), but again, SSIS is trying to authenticate ASPNET, instead of Domain\Me as would be expected.

This is definetely isolated to ASP.NET applications, because making the same call from within a unit test succeeds. It would seem that this is a definete issue because the behavior is inconsistent with ASP.NET security principles.

Has anyone else seen this problem

Any direction or suggestions very much appreciated.

Thanks,

Rick





Re: Microsoft.SqlServer.Dts.Runtime & ASP.NET Identity Problem

Michael Entin - MSFT


There was a discussion of this issue recently:

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=927084&SiteID=1

The impersonation applies to the current thread only, but SSIS creates additional threads in order to be able to perform multiple steps simultanously. Unfortunately, the impersonation context is not passed to these additional threads, so any data base access occurs under process user context, not under impersonated context.

Even if passed the impersonation context to other threads, executing packages in ASP.NET would have very limited application anyway:

  1. Since you are impersonating user, you can only access local resources. Accessing resources (databases, files) from other machines requires delegation, and would not work in this scenario.
  2. IIS process recycle behavior makes executing packages in-proc rather unreliable solution.

It is better to execute SSIS package outside of ASP.NET process, e.g. by starting DTEXEC or using Agent Job (Agent proxies are convinient if you want to execute under specific credentials).







Re: Microsoft.SqlServer.Dts.Runtime & ASP.NET Identity Problem

Assert.True

Michael, thank you for the highly relevant response.

It would appear from your response that the new threads that are spawned are binding to the underlying process- regardless of impersonation- which in the case of IIS 5 is ASPNET and IIS 6 would be NETWORK SERVICE, correct

If this is the case, then is setting the processModel (for IIS 5) or configuring an application pool (for IIS 6) with a fixed identity a solution to the problem

Certainly from point #2, app pools would be highly desirable for isolation purposes.

As for #1, is it not the case that delegation is only required in double-hop scenarios (i.e. IIS Client -> IIS Service -> SQL) whereas in this particular case, the sequence would be IISClient -> SQL I could be confused on the double hop issue, but I am almost certain that this is the exact approach for implementing impersonation in ASP.NET, scalability concerns aside.

Again, thank you for your very informed response.

Rick







Re: Microsoft.SqlServer.Dts.Runtime & ASP.NET Identity Problem

Michael Entin - MSFT

The delegation problem occurs if the client (e.g Internet Explorer), IIS and SQL are located on three distinct machines (first hop from IE to IIS, second from IIS to SQL). The client is usually separate from server, so the double hop occurs if SQL is on a separate machine. If SQL is local to IIS, everything will work fine.

Now about configuring IIS application with a fixed identity - yes, this will definetely work. And you don't need impersonation in this case at all.

However, from security point it is usually not desirable to run web applications under account with full data access rights that are usually required for ETL process. This is why I suggested using SQL Agent - you configure an Agent proxy for this identity, instead of configuring IIS application to run under this identity. You then give ASP account only enough rights on the database server to start this job. This gives you some priviledge separation between web application and ETL process.




Re: Microsoft.SqlServer.Dts.Runtime & ASP.NET Identity Problem

Assert.True

Thank you, Michael for the clarification. Am I correct in assesing that for a vanilla security scenario, the process account must be a member of the sysadmin server role If so, then this is definetely undesirable. Is the agent proxy the only alternative

I must say that having done a cursory read through the procedure of setting up proxy accounts in Kirk Haselden's book, it seems quite ardous to implement, but if this is the recommended approach then I will give it a technical read. To ensure I understand, it is possible to restrict the configured process identity using Agent proxies so that the resulting access is least priveledged on the SSIS host, correct

Michael Entin - MSFT wrote:
The delegation problem occurs if the client (e.g Internet Explorer), IIS and SQL are located on three distinct machines (first hop from IE to IIS, second from IIS to SQL). The client is usually separate from server, so the double hop occurs if SQL is on a separate machine. If SQL is local to IIS, everything will work fine.

Thanks for reminding me of this subtlety. I totally knew that but its amazing how doing talks on security using a VPC for all the demos can really fry your brain- its a VPC not a distributed environment!

Thanks,

Rick






Re: Microsoft.SqlServer.Dts.Runtime & ASP.NET Identity Problem

Assert.True

Michael,

Another question. In my scenario: IE -> IIS - SQL Server where each is on a different machine, what are the requirements of IIS in order to be able to load the Microsoft.SqlServer.Dts.Runtime and do Application.LoadFromDtsServer

This works fine on my dev machine where IIS and SQL are co-located, but on the integration server, XCOPYing the Microsoft.SQLServer.ManagedDTS.dll and Microsoft.SQLServer.DTSRuntimeWrap.dll caused the runtime to throw the following exception:

Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.

Are there client pre-reqs for the IIS box to be able to invoke a remote SSIS server

Thanks,

Rick






Re: Microsoft.SqlServer.Dts.Runtime & ASP.NET Identity Problem

Michael Entin - MSFT

>Am I correct in assesing that for a vanilla security scenario, the process account must be a member of the sysadmin server role

It depends on what the package is doing, there is no generic answer. Usually just access to appropriate tables is enough, but if you use tasks like Transfer Logins - you probably need sysadmin.

>what are the requirements of IIS

Since you are executing the package on IIS box (it does not matter where the package was loaded from - since you execute it, it is executed in your process), you need full SSIS installation.






Re: Microsoft.SqlServer.Dts.Runtime & ASP.NET Identity Problem

Assert.True

Michael, et al,

>> SSIS creates additional threads in order to be able to perform multiple steps simultanously. Unfortunately, the impersonation context is not passed to these additional threads, so any data base access occurs under process user context, not under impersonated context.

Does this same behavior apply to Dtexec.exe using the System.Diagnostics.Process technique It sure seems so.

Thanks,

Rick






Re: Microsoft.SqlServer.Dts.Runtime & ASP.NET Identity Problem

Michael Entin - MSFT

Assert.True wrote:

Michael, et al,

>> SSIS creates additional threads in order to be able to perform multiple steps simultanously. Unfortunately, the impersonation context is not passed to these additional threads, so any data base access occurs under process user context, not under impersonated context.

Does this same behavior apply to Dtexec.exe using the System.Diagnostics.Process technique It sure seems so.

Thanks,

Rick

I'm not sure what you mean by "same behavior apply"

Once you run DTEXEC, the process is created with some account, and since no impersonation occurs inside DTEXEC, all threads run under account used to run the DTEXEC process. So SSIS runtime multithreading behavior makes no difference here, since all threads use process user token.

What you might be seeing is that new process you create runs under account of the ASP.NET process, ignoring the impersonation you perform - this is Windows behavior, see

http://msdn2.microsoft.com/en-us/library/ms682425.aspx

If the calling process is impersonating another user, the new process uses the token for the calling process, not the impersonation token. To run the new process in the security context of the user represented by the impersonation token, use the CreateProcessAsUser or CreateProcessWithLogonW function.






Re: Microsoft.SqlServer.Dts.Runtime & ASP.NET Identity Problem

Assert.True

Michael Entin - MSFT wrote:

What you might be seeing is that new process you create runs under account of the ASP.NET process, ignoring the impersonation you perform -

This is exactly what I am doing. If ACLs authenticate the impersonated identity as do most downstream resources, you would think that kicking off dtexec.exe would be managed like any other resource.

Anyway, this seems to be easily resolved by either chaning the processModel in IIS5 to run as a fixed account or using application pool with fixed identity in IIS 6+. Is this the guidance

CreateProcessAsUser, etc are unmanaged Win32 calls, correct Do you have any sample code for what the call might look like for spinning up a dtexec process from ASP.NET/WCF

Thanks,

Rick






Re: Microsoft.SqlServer.Dts.Runtime & ASP.NET Identity Problem

Michael Entin - MSFT

Sorry, I don't have any code for doing it.

Yes, running ASP.NET under fixed account is an option. But I think using SQL Agent to run the packages is usually better. If you use Agent, you configure a SQL Proxy to run the job with the package under account you need, and the ASP.NET account only needs priviledges to connect to SQL Server and start this job.