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 Server DTS Package from ASP.NET page problem

I'm having some problems running a DTS package from my ASP.NET appllication. It runs prefectly in debug from my local machine but as soon as I move it to the web server I receive the following error:

Error: -2147467259
Source: Microsoft JET Database Engine
Description: The Microsoft Jet database engine cannot open the file '\\serverB\PayFiles\Pay3.xls'. It is already opened exclusively by another user, or you need permission to view its data.

The file is not opened by anyone else and the share permissions are full control for everyone.

My set up is as follows

serverA - Webserver & DB Server
serverB - File Server

The DTS package pulls in the file from serverB to a database table on serverA. The sa account is used for the database connection and connecting to the DTS package from the application. My code for running the DTS package is as follows -

Private Sub ExecutePackage(ByVal serverAs String,ByVal packageAs String,ByVal usernameAs String,ByVal passwordAs String)Const DTSSQLStgFlag_Default = 0Const DTSStepExecResult_Failure = 1Dim oPKGAs DTS.Package, oStepAs DTS.Step oPKG =New DTS.PackageDim sServerAs String, sUsernameAs String, sPasswordAs String Dim sPackageNameAs String, sMessageAs String Dim lErrAs Long, sSourceAs String, sDescAs String' Set Parameter Values sServer = server sUsername = username sPassword = password sPackageName = package' Load Package oPKG.LoadFromSQLServer(sServer, sUsername, sPassword, _ DTSSQLStgFlag_Default, , , , sPackageName)' Set Exec on Main ThreadFor Each oStepIn oPKG.Steps oStep.ExecuteInMainThread =True Next' Execute oPKG.Execute()' Get Status and Error MessageFor Each oStepIn oPKG.StepsIf oStep.ExecutionResult = DTSStepExecResult_FailureThen oStep.GetExecutionErrorInfo(lErr, sSource, sDesc) sMessage = sMessage &"Step " & oStep.Name &" Failed<br><br>Error: " & lErr &"<br>Source: " & sSource &"<br>Description: " & sDesc &"<br><br>"Else sMessage = sMessage &"Step " & oStep.Name &" Succeeded<br>"End If Next oPKG.UnInitialize() oStep =Nothing oPKG =Nothing' Display Results 'MsgBox(sMessage) lblOutput.Text = sMessageEnd Sub

I'm using windows authentication with identity impersonate set to true. If I change to use basic authentication and enter my login when I go to the site the DTS package runs successfully. So I assume that I'm seeing this 'double hop' issue.

serverA is for testing so I moved the DTS package and web application to serverB so now everything is on the same server and should eliminate the double hop? No! Works perfectly from my PC in debug, but from the web server the same error occurs. Changing to basic authentication does not workaround the issue on the live server.

The DTS package always works when run from Enterprise manager.

I know this is a premissions issue - does anyone know what I'm doing wrong? Hopefully I've supplied enough info, if not just ask!

You may want to use a windows service instead of a web page, as when the web page stops rendering the process will also stop.

|||

If I use a windows service will I still be able to give the user control over when the package is run?

|||

I've decided to use a stored procedure and DTSRun to do this instead and then simply execute the stored procedure from my page. Just in case anyone else wants to use it this is my code.

CREATE PROC [dbo].[sp_DTSRun] @.ServerNameVARCHAR(30),@.PackageNameVARCHAR(50)ASBEGINDECLARE @.ReturnValueint, @.Cmdvarchar(500)SET @.ReturnValue = -1SET @.Cmd ='dtsrun /S ' + @.ServerName +' /N ' + @.PackageName +' /E'--' /U "LoginName" /P "password" 'EXECUTE @.ReturnValue = master..xp_cmdshell @.Cmd, NO_OUTPUT--RETURN @.ReturnValueSELECT @.ReturnValue [Result]END

Private Sub ExecutePackage(ByVal serverAs String,ByVal packageAs String)Dim ExecuteAdapterAs New PAY_DAL.RunDTSTableAdapters.sp_DTSRunTableAdapter() ExecuteAdapter.SetCommandTimeOut(0)Dim strResultAs String = ExecuteAdapter.Execute(server, package)Select Case strResultCase 0 lblOutput.Text ="The package executed successfully."Case 1 lblOutput.Text ="The package failed - contact support."Case 2 lblOutput.Text ="2"Case 3 lblOutput.Text ="The package was cancelled by the user."Case 4 lblOutput.Text ="The utility was unable to locate the requested package. The package could not be found."Case 5 lblOutput.Text ="The utility was unable to load the requested package. The package could not be loaded."Case 6 lblOutput.Text ="The utility encountered an internal error of syntactic or semantic errors in the command line."End Select lblOutput.Visible =True End Sub

Just pass the package and server name in. To use a username and password remove the /E (trusted connection) and add /U and /P.

Running Sql Server Developer Edition with VS 2005 and ASP.NET

I am having problems installing Sql Server Developer edition (2005) with VS 2005 and getting any ASP.NET projects to work.

It says SQl Server express must be installed.

I would like to use the full developer edition to link to my projects.

Can someone help?

Thanks,

Mike G

In your connection string in web.config in your ASP.Net projects, are you pointing to SQLExpress, or have you configured it to connect to your Dev Edition SQL Server? More details on your configuration and error message would help.|||

I am having the same problem. When I try to add a new sql database to an asp.net web application using the following steps:

1. Right-click the App_Data folder

2. Select "Add New Item"

3. Select Sql Database

4. Change SQL Database name to xxxxxx.mdf

5. Click "Add"

I then get the following message:

"Connections to SQL Server Files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URl: http:go.microsoft.com/fwlink/?linkID=49251."

I am using VS 2005 Professional and SQL Server 2005 Standard Edition. I want to be able to do this with SSE. In VS2005 I have gone to Tools...Options...Database Tools...Data Connections and changed the SQL Server Instance Name from SQLEXPRESS to MSSQLSERVER. Should I make it the name of my server?

Any help would be greatly appreciated.

John

|||

By using App_Data it appears you're forced into using SQL Express, because you're defining a connection to a physical database file, rather than establishing a connection string to an existing server.

In my web.config file I configure an application variable called ConnectionString containing something like this:

Integrated Security=True;Data Source=MyServer;Initial Catalog=MyDatabase;Application Name=MyApp;

Then I configure a SQL Connection object and set the .ConnectionString property to the application connection string. After that all is well.

|||

So does that mean the App_Data folder is only used with SQL Server Express and not to be used with SQL Server 2005?

John

Running Sql Server Developer Edition with VS 2005 and ASP.NET

I am having problems installing Sql Server Developer edition (2005) with VS 2005 and getting any ASP.NET projects to work.

It says SQl Server express must be installed.

I would like to use the full developer edition to link to my projects.

Can someone help?

Thanks,

Mike G

In your connection string in web.config in your ASP.Net projects, are you pointing to SQLExpress, or have you configured it to connect to your Dev Edition SQL Server? More details on your configuration and error message would help.|||

I am having the same problem. When I try to add a new sql database to an asp.net web application using the following steps:

1. Right-click the App_Data folder

2. Select "Add New Item"

3. Select Sql Database

4. Change SQL Database name to xxxxxx.mdf

5. Click "Add"

I then get the following message:

"Connections to SQL Server Files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URl: http:go.microsoft.com/fwlink/?linkID=49251."

I am using VS 2005 Professional and SQL Server 2005 Standard Edition. I want to be able to do this with SSE. In VS2005 I have gone to Tools...Options...Database Tools...Data Connections and changed the SQL Server Instance Name from SQLEXPRESS to MSSQLSERVER. Should I make it the name of my server?

Any help would be greatly appreciated.

John

|||

By using App_Data it appears you're forced into using SQL Express, because you're defining a connection to a physical database file, rather than establishing a connection string to an existing server.

In my web.config file I configure an application variable called ConnectionString containing something like this:

Integrated Security=True;Data Source=MyServer;Initial Catalog=MyDatabase;Application Name=MyApp;

Then I configure a SQL Connection object and set the .ConnectionString property to the application connection string. After that all is well.

|||

So does that mean the App_Data folder is only used with SQL Server Express and not to be used with SQL Server 2005?

John