Running SSIS package from asp.net failed

I havean 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) or run the package by batch file, 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 remainingsteps failed which are related to the connection managers A &amp; 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 thoughtthe credentialswere not passedcorrectly. Then, why the first two steps when through?Something wrong with Analysis servicessetting -complaining start fromthere. Any clues?

 

When you run the web application from VS2005 it actually uses the Cassini service, which probably acts differently than IIS in terms of Windows Authentication and impersonation.

The solution would be to add the application user (NETWORK SERVICE) to your SQL 2005 database so that it has access to it. However, we don't allow this at DiscountASP.

Aristotle

DiscountASP.NET
www.DiscountASP.NET