November 3, 2010 at 12:02 pm
Hi,
I am trying to launch a SSIS package via a web service but I an getting the following error:
[SecurityException: That assembly does not allow partially trusted callers.]
CDRSWebService.LaunchPackage(String sourceType, String sourceLocation, String packageName) +0
Default2.Button1_Click(Object sender, EventArgs e) +107
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +6785
System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +242
System.Web.UI.Page.ProcessRequest() +80
System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context) +21
System.Web.UI.Page.ProcessRequest(HttpContext context) +49
ASP.default_aspx.ProcessRequest(HttpContext context) +37
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +181
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +75
This is the Default.aspx:
Imports System.Data.SqlTypes
Imports System.Data
Imports System.Data.SqlClient
Partial Class Default2
Inherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim MyWebService As New CDRSWebService
Dim sourceType As String
Dim sourceLocation As String
Dim packageName As String
sourceType = "sql"
sourceLocation = ""
Select Case DropDownList1.Text
Case "AMA"
packageName = "CDRS_Primary_Billing_Process"
MyWebService.LaunchPackage(sourceType, sourceLocation, packageName)
Label2.Text = "AMA Processing has completed"
Case "at&t"
packageName = "CDRS_Process_at&t_Files"
MyWebService.LaunchPackage(sourceType, sourceLocation, packageName)
Label2.Text = "at&t Processing has completed"
Case "ANPI"
packageName = "CDRS_Process_ANPI_Files"
MyWebService.LaunchPackage(sourceType, sourceLocation, packageName)
Label2.Text = "ANPI Processing has completed"
Case "Sierra"
packageName = "CDRS_Process_Sierra_Files"
MyWebService.LaunchPackage(sourceType, sourceLocation, packageName)
Label2.Text = "Sierra Processing has completed"
End Select
This is the WebService.vb:
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
<WebService(Namespace:="http://yellowstone/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class CDRSWebService
Inherits System.Web.Services.WebService
' LaunchPackage Method Parameters:
' 1. sourceType: file, sql, dts
' 2. sourceLocation: file system folder, (none), logical folder
' 3. packageName: for file system, ".dtsx" extension is appended
<System.Web.Services.WebMethod()> _
Public Function LaunchPackage( _
ByVal sourceType As String, _
ByVal sourceLocation As String, _
ByVal packageName As String) As Integer 'DTSExecResult
Dim packagePath As String
Dim myPackage As New Microsoft.SqlServer.Dts.Runtime.Package
Dim integrationservices As New Microsoft.SqlServer.Dts.Runtime.Application
Dim serverName As String
Dim serverUsername As String
Dim serverPassword As String
serverName = "yellowstone"
serverUsername = "sa"
serverPassword = "******"
' Combine path and file name.
packagePath = Path.Combine(sourceLocation, packageName)
Select Case sourceType
Case "file"
' Package is stored as a file.
' Add extension if not present.
If String.IsNullOrEmpty(Path.GetExtension(packagePath)) Then
packagePath = String.Concat(packagePath, ".dtsx")
End If
If File.Exists(packagePath) Then
myPackage = integrationservices.LoadPackage(packagePath, Nothing)
Else
Throw New ApplicationException( _
"Invalid file location: " & packagePath)
End If
Case "sql"
' Package is stored in MSDB.
' Combine logical path and package name.
If (integrationservices.ExistsOnSqlServer(packagePath, serverName, serverUsername, serverPassword)) = True Then
myPackage = integrationservices.LoadFromSqlServer( _
packageName, serverName, serverUsername, serverPassword, Nothing)
Else
Throw New ApplicationException( _
"Invalid package name or location: " & packagePath)
End If
Case "dts"
' Package is managed by SSIS Package Store.
' Default logical paths are File System and MSDB.
If integrationservices.ExistsOnDtsServer(packagePath, "yellowstone") Then
myPackage = integrationservices.LoadFromDtsServer(packagePath, "yellowstone", Nothing)
Else
Throw New ApplicationException( _
"Invalid package name or location: " & packagePath)
End If
Case Else
Throw New ApplicationException( _
"Invalid sourceType argument: valid values are 'file', 'sql', and 'dts'.")
End Select
Return myPackage.Execute()
End Function
End Class
Client is Windows 7 64-bit using IE 8
Server is Windows 2008 SP2 32-bit with SQL Server 2008 R2
Any advice or help would be greatly appreciated
Thanks,
JP
November 3, 2010 at 12:18 pm
[SecurityException: That assembly does not allow partially trusted callers.]
Double-hop security issue, I think. Not sure how to work around that for SSIS though. Either that or are you on two different sub-domains for the SSIS and webservice servers?
Btw, why are you calling SSIS from a webservice?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 3, 2010 at 12:31 pm
Double-hop security issue, I think. Not sure how to work around that for SSIS though. Either that or are you on two different sub-domains for the SSIS and webservice servers?
Just one domain.
Btw, why are you calling SSIS from a webservice?
Can either do it:
1. Running a package remotely by calling SQL Server Agent
2. Running a package remotely by calling a remote component or service
November 3, 2010 at 1:00 pm
Some very brief google research:
Run the webservice as admin:
http://database.itags.org/sql-server/274814/
Same starter post, more clarification at the end, needs fully trusted in .NET:
http://www.windows-tech.info/15/35c56638890ee860.php
Some information about custom assemblies which I don't fully understand but you might, and seems semi-relevent:
http://thedailyreviewer.com/dbsoftware/view/repostexternal-assemblies-in-reports-10618054
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 3, 2010 at 1:02 pm
Thanks Craig. I will check it out.
November 10, 2010 at 9:27 am
Worked with Microsoft to add strong naming and created a .snk file for the web site. That seemed to work until we wiped out Sharepoint Services 3.0 and had to reinstall it. Now that we did that we are getting the same error again.
Does anybody know if there is some setting on the server that needs changing after the reinstall of Sharepoint Services 3.0? I am still using strong naming and the .snk file but get the same error as above.
Thanks
November 11, 2010 at 12:24 pm
Answer:
In IIS 7 there is a .NET Trust Level category which needed to be set to Full Trust for the specific Web Site. It was originally set to Minimal.
Hope this helps somebody else with the same problem.
JP
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply