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.

Running SSIS package (.dtsx) from a ASP.Net web service

Where do I find about running an SSIS package (.dtsx) from a ASP.Net web service, particularly issues to do with permissions

cheers

Rob

Hi Rob,

There is books online entry that gives an example of how to do this.

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

Also note the comments in the Community Content at the bottom. It has some important notes about security and thread impersonation.

~Matt

Running SSIS package (.dtsx) from a ASP.Net web service

Where do I find about running an SSIS package (.dtsx) from a ASP.Net web service, particularly issues to do with permissions

cheers

Rob

Hi Rob,

There is books online entry that gives an example of how to do this.

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

Also note the comments in the Community Content at the bottom. It has some important notes about security and thread impersonation.

~Matt

sql

Running SQL 2000 and SQL 2005 simultaneously

I have seen information on the web about running two versions of SQL at the same time on the same server but was wondering any tricks of the trade regarding it. The main recommendation that I have seen is to have SQL 2000 SP4 running first as the default instance (which is my current situation on a development server) and then install SQL 2005 as a named instance on it. Should the SQL 2005 installation place its files in a dedicated directory (ie, C:\SQL2005) as opposed to the default directory that the installation would normally write to, thus not overwriting any SQL 2000 system files? Any other recommendations?

Well directories of the core files (files needed to run SQL Server should be separated in different directories), for data and log files it depends. If you already established a physical structur of distributed data files and log files (to enhance performance due to split IO access) you should reuse this structure, placing the files on the disk using the same "logic" as you did for the other instance. If you did not do that yet, you can place the files wherever you want, I would just ot mix them up with the other (data-)files as it will ease the migration lateron if you keep them separately.

Jens K. Suessmeyer

http://www.sqlserver2005.de