I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.I've tried giving administrative permissions to the ASPNET worker process user to no avail.I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.Anybody got any idea on how to go about this?

Hi, Does your ASP.NET application runs under impersonation account. If yes then that account needs to be given permission to run DTEXEC command.. ThanksMohit

Hi Jon, This could be due to the ProtectionLevel setting for the individual packages - that's my guess. By default,the Package ProtectionLevel property isset to EncryptSensitiveWithUserKey. This means as long as you personally execute the packages, your credentials are picked up and the packages execute in your security context. This is true even if you're connected to a remote machine, so long as you're using the same AD credentials you used when you built the packages. When thepage you created executes, it runs under theASP.Net Service logon credentials. There are a couple proper long-term solutions but the one that makes the most sense is to use the EncryptSensitiveWithPassword Package ProtectionLevel option and supply a good strong password. You will need to supply the password when youcall the package from ASPas well, and this should allow the package to run without needing your security credentials. Note: You will also need this password to open the packages inBIDS (or Visual Studio)from now on... there's no free lunch. Hope this helps, Andy

Hi Mohit,I'm going to try your impersonation suggestion soon. Thanks.Hi Andy,Thanks for the suggestion. I guess that's another thing I have to try. Hopefully it'll just take another line in the appSettings for web.config.Thanks for the suggestions guys, will get back to you on this later.

First of all I would to the homework and find out what is the actual error you are getting. I don't understand what you mean by "quit execution without any hint of an error nor a failure message". There are lots of ways to get more information about package execution: SSIS logs Examine package.Errors collection after execution Provide implementation of IDtsEvents and supply it to Execute method (second code snippet at http://msdn2.microsoft.com/en-us/library/ms136090.aspx) Find out what error you are getting. Trying to fix the problem without knowing what it is can cause lots of wasted time. And theinfrastructure you create today will ease support and troubleshooting of the application in the future.

Hi Michael,I did implement SSIS logs (Windows Event logs and Text logs) on the packages. On all cases the Windows Event Log will indicate that so and so package has started, and so and so package has ended successfully. No errors logged from there.The log files are nowhere to be found, or if provided as a blank file, wouldn't be opened nor edited, and all my other packages that don't involve file system tasks work perfectly.-- which led me to the conclusion that it is a file permission problem.I'll try your two other suggestions to try and figure out the error though. Thanks.

Mohit,I tried your solution (added impersonation to an Administrative account) but it went just the same way. Even if impersonated with the Administrator the package would start, declare that it "ended succesfully" but the expected tasks won't be performed.I also tried running the package using DTEXEC on the command prompt, and the package was executed perfectly. There is where I saw the difference though; while the DTEXEC run events were logged entirely with the user as "Administrator", from the ASP.NET application all the log messages between start and end were registered under "NETWORK SERVICE".Looks like no matter the identity obtained from ASP.NET, the packages will be run by the worker process (or whatever the NETWORK SERVICE user is).I'll try Andy's solution next.

As per Micheal's suggestion, I looped through the package.Errors collection and logged the problems using log4net, these being the common results:[11/01/07 01:58:11.406] DEBUG [7] EtlUtils.ExecuteSSIS(0) | Package errors encountered: Code = -1073651690; Description = Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.[11/01/07 01:58:11.406] DEBUG [7] EtlUtils.ExecuteSSIS(0) | Package errors encountered: Code = -1073659829; Description = The SSIS logging provider has failed to open the log. Error code: 0x80070005.Access is denied.The 2nd error explains the absence of a text log.Of course, further research of the first error would reveal this support article regarding the said error.I proceeded with trying out Andy's suggestion, that is, setting the package ProtectionLevel property to EncryptSensitiveWithPassword but to my surprise (and frustration) exactly the same errors occured. This is despite the fact that I both deployed the packages using the password I set, and supplied the password in the Password property of the package object in VB.NET.I've tried playing with two other things; changing the threading overloads (from neutralThreaded true to false which translates to apartmentThreaded), and executing it from a ShellExecute call DTEXEC. The earlier had no effect, the latter was worst (it didn't execute the package at all). Setting impersonation either to the server administrative account or to my account (which I expected would work since I was the package creator) didn't work either, although impersonating the creator account did allow the package to invoke and validate more objects, only to arrive at the same result.I've tried several combinations of the above solutions, but to no avail.This is really getting frustrating. Hope someone who has had a similar experience deploying ETL packages can provide some sort of hint or solution.

Jon Limjap wrote:I proceeded with trying out Andy's suggestion, that is, setting the package ProtectionLevel property to EncryptSensitiveWithPassword but to my surprise (and frustration) exactly the same errors occured. This is despite the fact that I both deployed the packages using the password I set, and supplied the password in the Password property of the package object in VB.NET. Did you supply the password when you load the package in the ASP.NET code?

Yes, on my ASP.NET code I set the package.PackagePassword property to the same password I used on the packages right before I called package.Execute().

Hi Jon, Sorry to hear about the continuing issues. Are you able to run the package by logging into SQL Server Integration Services? Andy

Hi Andy,Thanks.Yes I am able to run the packages on the same machine if I log in to SSIS, or if I use the DTEXEC utility. As I stated above, when I run it from DTEXEC in the Windows logs the "user" value for the entire set of tasks logged is set to "Administrator" or the account whoever the current user logged on to the machine is.However, when it is run from ASP.NET (using impersonation of the Administrator account), only the first and last entries are logged under the Administrator --- the rest are logged under the user "NETWORK SERVICE".I'm looking into using SQL Server Job Agent instead, but can't find a way to pass parameters through it. I'll be posting a new thread for that one.

Hi Jon, I am still curious why you're seeing the Network Service account - I bet you are too. ;) It's as if you're changing users (and security contexts) mid-package-execution and that doesn't seem possible. I don't doubt what you're saying, I'm extremely curious as to how (and why) this is occurring in the first place. The only way I know to get parameters into a job is to land them (the parameters) inside the database first, then pick them up in the job step via a query - which seems an awful way to call an SSIS package (to me, at least). Andy

Andy, Micheal, MohitI've FINALLY solved this problem! It appears that when ASP.NET executes a process asynchronously (as what happens with the Microsoft.SqlServer.Dts.Runtime.Package.Execute() method) the credentials that will be used is the credentials set in the Identity settings of the Application Pool of the ASP.NET application in IIS. You can read more about that here.By default, this Identity is set to the Network Service user. I've tried setting it to the Local System user (which should have Administrator rights) but for reasons I still have to find, that didn't work either. The same Access Denied message appeared.So I created a new pool (not wise to tweak settings in the DefaultAppPool), set its Identity settings to Configurable, and assigned a user that is a member of the Administrator function. This time I ran into an error trying to have the Application pool start -- apparently I have to make the user I assigned a member of the IIS_WPG (IIS worker process group) group.That I did, set the web application to run on my new application pool, restarted IIS, and everything went fine, and finally the call to the ETL worked from start to finish!I'd like to thank everyone for their suggestions -- it immensely helped in isolating and narrowing down the possible solutions to this problem.

Good work Jon! :) Andy

Jon Limjap wrote:I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.SQL Server Agent can be implemented via stored proceedures. I don't believe that this requires the installation of the SQL Server 2005 Database Engine on your application server.

Duane Douglas wrote: Jon Limjap wrote:I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.SQL Server Agent can be implemented via stored proceedures. I don't believe that this requires the installation of the SQL Server 2005 Database Engine on your application server.I think it does. The Agent service in itself is under the Database Engine. I haven't found a way (more like haven't had the time to find it) how to install the job agent without the DB engine.

Jon Limjap wrote:Andy, Micheal, MohitI've tried setting it to the Local System user (which should have Administrator rights) but for reasons I still have to find, that didn't work either. I've experience the same issue. Any idea why LOCAL SYSTEM, ASPNET OR NETWORK SERVICE cannot start dtexec? Thanks, Rick

Assert.True wrote: Jon Limjap wrote:Andy, Micheal, MohitI've tried setting it to the Local System user (which should have Administrator rights) but for reasons I still have to find, that didn't work either. I've experience the same issue. Any idea why LOCAL SYSTEM, ASPNET OR NETWORK SERVICE cannot start dtexec? Thanks, RickI'm still trying to find that out -- the kind of permissions required for a user to run dtexec. I need it to be able to use impersonation on another ASP.NET application that runs dtexec. The solution I outlined here won't work when impersonation is set to true unless all the users are Administrators, which is unacceptable.

I'm still trying to find that out -- the kind of permissions required for a user to run dtexec. Thanks- if you figure it out, let me know. I am pretty sure it has to do with local security policy, but I don't have time to figure it out yet either and for now am just using a fixed domain account with an IIS 6.0 application pool which works.

Assert.True wrote:I'm still trying to find that out -- the kind of permissions required for a user to run dtexec. Thanks- if you figure it out, let me know. I am pretty sure it has to do with local security policy, but I don't have time to figure it out yet either and for now am just using a fixed domain account with an IIS 6.0 application pool which works.Hi Assert.True,I think I found the solution for this. If you have access to the Computer Management console of your server where the packages are installed (from Win2K3 Administrative Tools), open the Local Users and Groups node, and check out Groups.Under Groups there should be several groups with the prefix SQLServer2005. One of them is "SQLServer2005DTSUser$MACHINENAME". I think if the user is a member of this group, they would be able to execute DTExec. Haven't tried it out but I'll tell you when I have.

Wrong answer, Andy. DTS Package (as any other process, executed from ASP.NET impersonated thread) should use Default credentials. But it doesn't! And this is not only DTSexecution problem. As I see ONLY MSSQL Database connections aquired from ASP.NET Web Service run under the CORRECT impersonated credentials( Just because those guys know how to obtain current credentials to implement database connect :-) ). We have to go by the wrong way starting Application Pool with user credentials trying to execute other processes on the machine (or services we connect on remote machines)!!!!! Is it the right way?! Of course, not. Impersonate = "true" MUST do the right thing in this case for you! But, as I said, it doesn't. As i see, it's a bug in ASP.NET pocesses (or threads) execution strategy. And this workaround is working but incorrect! We spent a lot of time trying to find the answer in wrong web.config-s, Kerberos domain politics, etc .... NO WAY. Impersonation in ASP.NET Services doesn't work correctly! -Andrew

Changing the App Pool as described worked for me. I was in a real bind over this one- thanks for an outstandingly useful post.

I'm with Jon. If this was possible, I would be totally floored.

My guess is that ASP.NET impersonation is working badly. Impersonation should mean something dont it? So why doenst the package executes with my currently asp.net user? What if i have my dts package db connections setup to use integrated security and i really want to use the asp.net users?? Please give me an advice. Best Regards.

Hi Luis, Can you please clarify the context of your question? Flowing identity to the database can be accomplished via a fixed indentity using IIS 6 application pool identities, specifying an account in the processModel element in the web.config or using impersonation. I look forward to helping you with your issue. Rick

Hi everybody, I'm working on the same project Jon Limjap did. But this time, the clients asked to use impersonation. So we have <identity impersonate="true"/> in our web.config. The authentication is now Kerberos. When I try running the ETL package using the DTS Runtime API, it now requires the impersonated user to have the local Administrator privileges to run successfully. If not, I get the '"The package failed to load due to error 0xC0011008 "Error loading from XML"' error. Any ideas how to solve this? Thanks!

I have followed long time the application pool solution... but that lead me to another problem... In the earlier times i wanted the package to connect to an SQL Server instance using integrated security... since i had to use the application pool i saw myself restricted either to use SQL User or to use the pre configured APP Pool User. That was not in my plans and it ruin my software plans. If anyone find a good solution for this i would be amazed. Kind Regards

Jon, Thanks for such a detailed solution. I've been pulling my hair out for days with this problem and your post has finally helped me solve the issue!

Jon Limjap wrote: Yes, on my ASP.NET code I set the package.PackagePassword property to the same password I used on the packages right before I called package.Execute(). Just wanted to point out that the PackagePassword property must be set on the SSIS Application object, prior to loading the package. Setting it on the package after it has been loaded does not un-encrypt the package. http://agilebi.com/cs/blogs/jwelch/archive/2007/11/15/running-password-protected-packages.aspx

My solution to this problem was to set the package protection level to "don't save sensitive," then programmatically set the connection string for the package before executing it: ConnectionManager connection = null; foreach (var con in pkg.Connections) { if (con.Name == "SourceConnection") connection = con; } if (connection == null) throw new Exception("Connection not found - SSIS should have a connection named \"SourceConnection\""); else connection.ConnectionString = "Dsn=SSIS_TEST;uid=guy;pwd=guys_pw"; I didn't have to change the connection pool or do any impersonation to do this.

Hi Jon, Good work and I'm glad you found a solution. The reason why Local Admin wouldn't work is because that account has admin rights Locally, only on the ASP.Net server. It has no rights what-so-ever on the SQL server. The default IIS user is a local account usually (I believe) so, even if you add this to the SQL server, it won't work unless they have the exact same username and password. If the servers aren't a part of the domain this is even more difficult since there are no global domain accounts. As an aside, it always seems like after you do all that work one or two people come up with a completely different approach that is much easier. For instance, check out the suggestion below to not encrypt the data and simply provide the username/password for the connections just before execution. The other idea is to launch the package through a stored procedure on the SQL server through a simple database connection. You can find a great example here: http://forums.asp.net/t/1129202.aspx

Thanks for this post. I was researching all over and this post helped me a lot. One thing I added though was IMPERSONATION on my webconfig and I was finally able to successfully run my SSIS package on another PC.