Running SSIS package from asp.net application failed

I have an asp.net web application and a web service (both of them are created in VS 2005 - asp.net 2.0). They are located on the same web server. In both web.config files, I have set <authentication mode="Windows"/> and <identity impersonate="true"/>. Also, configured the IIS settings to use Integrated Windows Authentication and unchecked the Anonymous access (for both). The web service is called from the web app, so I have to pass credentials for authentication to the web service. The web service loads and executes a SSIS package. The package and all the other sql objects are located in the sql server 2005 (windows server 2003 - the same server as the web server).

When run the web service from develop environment (vs. 2005), I get whatever I expected. When call it from web application, however, the package failed (no error message).

In the SSIS package, there are three connection managers –

· A: Microsoft OLE DB Provider for Analysis Services 9.0 à connectionType=OleDbConnection

· B: .Net Providers \ SqlClient Data Provider à connection type=SqlConnection

· C: Native OLE DB \ Microsoft OLE DB Provider for SQL Server à connectionType=OLEDB

After ran the web application and check the sql database, I can tell that the package was reached and when through the first two steps (clear some records in table_1 and extract some records from table_2 ) which relate to the connection manager B – ADO.Net connection. The remaining steps failed which are related to the connection managers A & C.

From SSIS package log file, found that the user credentials (domain and username) were correctly passed from web service to sql server 2005 at the first two events, but the credentials (or operator) changed from domainABC\user123 to NT AUTHORITY\NETWORK SERVICE after packageStart. Then, it complains … either the user, domainABC\serverName$, does not have access to the database, or the database does not exist.

I think the credentials are passed ok but some setting related to the Analysis services are not correct - complaining start from there. Any clues?

Please help and thank you all!

FYI: The problem was solved.

What I did are list as below:

A. We created a new web app pool and pointed the both web application and web service to this web app pool (which was configured not use default identity - Predefined: Network Service but use Configurable - created a new use name).

B. Under SQL server , added this new user and assign the certain rights to it.

C. In the web application and web service, set impersonate=false (instead of true)

D. In the SSIS package, we also have a flat file destination connection which is to write the output to .txt file. I give the read & write rights to this new use. then, it works.