Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Call SSIS package residing on DB Server from a web application ASP.NET residing on Web server Expand / Collapse
Author
Message
Posted Monday, February 15, 2010 2:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 9, 2012 4:17 AM
Points: 12, Visits: 113
I have a requirement where I need to call a SSIS package from a ASP.NET application.
The SSIS package is residing on DB Server (created using BI studio) and client application is on another web server which do not have SSIS or SQL Server installed.

How can I achieve this and what are the prerequisits for achieiving this. Any help is appreciated. Or is there any other way to achieve this requirement?

Thanks
Vishal
Post #865414
Posted Monday, February 15, 2010 7:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 13, 2011 3:28 AM
Points: 41, Visits: 112
have you ever tried to start the job on the server with sp_start_job. you can connect to the database server and run this procedure
i guess this store procedure should work though i have never tried it myself ('');

do try and let me know if it works
Post #865533
Posted Monday, February 15, 2010 7:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:56 AM
Points: 5,101, Visits: 11,901
You obviously need to create a job to run the package before trying that idea ... it should work.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #865539
Posted Monday, February 15, 2010 9:31 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:39 AM
Points: 3,428, Visits: 14,438
vishal.shinde (2/15/2010)
I have a requirement where I need to call a SSIS package from a ASP.NET application.
The SSIS package is residing on DB Server (created using BI studio) and client application is on another web server which do not have SSIS or SQL Server installed.

How can I achieve this and what are the prerequisits for achieiving this. Any help is appreciated. Or is there any other way to achieve this requirement?

Thanks
Vishal


Yes you can run it as a job with a stored procedure but this is double hop so the Asp.net runtime needs permissions in both boxes or you will get null user error.

You also should run the agent with admin level permission because you can run it as a proxy.


Kind regards,
Gift Peddie
Post #865589
Posted Tuesday, February 16, 2010 1:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
You could also send the execution command directly to the server. Something in the like of:

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /SQL "\Folder\MyPackage" /SERVER "MyServer" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

Note that this command is for a 64-bit machine. You should check where the dtexec.exe file is located on your server. The package MyPackage is deployed to the MSDB folder on the Integration Services server, located on the SQL Server.

I have however no idea what the security settings should be to make this work from ASP.NET.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #865882
Posted Tuesday, February 16, 2010 8:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 1, 2013 8:05 AM
Points: 8, Visits: 70
I can share with you that we recently tried to make this happen via our Intranet, and you are going to run into authentication/permission issues. We got the package stored in MSDB to execute via ASP.NET page when running page from Visual Studio server on a remote admin machine, but when the page was deployed to the Intranet, the package would fail. Looked all over for a way to make it work, but there's no obvious workaround that we could find. We then created a job for the package and called an SP from ASP.NET page to run the job, but we had similar issues with that, and we finally gave up. Lots of people seem to have the idea that in theory this should work, but I have not found anyone who has been able to successfully achieve it from IUSR or ASPNET user accounts.
Post #866150
Posted Tuesday, February 16, 2010 10:01 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:39 AM
Points: 3,428, Visits: 14,438
We got the package stored in MSDB to execute via ASP.NET page when running page from Visual Studio server on a remote admin machine, but when the page was deployed to the Intranet, the package would fail.


I don't understand what you mean by deploy to intranet because the Agent it is automation tool so it needs to be in MSDB and not file system because if you deploy it to file system then you need to take care of Asp.net file permission and could escalate to Asp.net reflection permissions. That is the reason you use the Agent subsystem for all Asp.net execution.


Kind regards,
Gift Peddie
Post #866203
Posted Wednesday, February 17, 2010 8:00 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:16 AM
Points: 487, Visits: 1,231
We execute dozens of SSIS packages daily from a custom .Net "Operator Console" desktop app we developed. It's not ASP.Net but the concepts would be the same. Basically the app calls sp_start job which runs the SSIS package. It greatly simplifies the execution of the packages for the operators - just point and click. They can monitor the status of jobs and the success/failed outcome.

There are of course SQL security considerations. The account calling sp_start_job must have execute permissions. In our case the jobs run in the context of the SQL Agent which has the appropriate permissions to the resources needed.



Post #867102
Posted Wednesday, February 17, 2010 8:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 1, 2013 8:05 AM
Points: 8, Visits: 70
Sorry, I don't understand your post. Let me clarify on my post. The ASP.NET page was developed locally by an Admin Windows and Admin SQL user. When he executed the page locally thru VS server, the package, stored in MSDB, executed successfully. When the ASP.NET page was deployed to our Intranet, the package execution would fail. We believe the reason has to do with the credentials not being passed properly. We tried a number of different tricks, including Forms Authentication, ASP.NET Impersonation, and finally running the app in a separate application pool with admin SQL user. Nothing worked. I'm not suggesting that it can't be done from ASP.NET but rather that we were not able to resolve. There does not appear to be an easy answer to the authentication issues as passing credentials from ASP.NET to SQL to SSIS Package execution or to Job execution is nonobvious and pretty ornery.
Post #867140
Posted Wednesday, February 17, 2010 8:49 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:16 AM
Points: 487, Visits: 1,231
Well, it sounds like you're experiencing the "double-hop" issue. If you do not have Kerberos authentication enabled between these servers on your network (I do not know much about this) you cannot pass the credentials of the web client user to the web server (first hop) and then to the SQL Server (second hop).

One solution would be to create a standard SQL login with the appropriate permissions to execute sp_start_job. Use this login in your asp.net connection string to call the stored procedure that executes the SSIS package.

It is not ideal, but it works. However, the job is always called by the sql login that was created, so you do lose the audit trail back to the actual user.




Post #867169
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse