SSIS Calling Web Service Task 401 Unauthorized Error
May 23, 2007
Hello,
I'm pretty stuck on a security issue in SSIS. The web service works by itself, but I can't call it from SSIS, it gives me a 401 unauthorized error. The web service also uses impersonation of my domain admin account.
I have tried the following things:
Setting integrated windows authentication in IIS
Setting the NTFS permissions of those web site directories to EVERYONE
Using a credential / proxy in SSIS and running it from SQL Agent
Changing the log on services of MSSQLSERVER, SQLAGENT, and SQL Integration Services to my domain admin account
I can't get anything to work. What is wrong with this thing? Microsoft's security model has gotten completley out of of hand imo
Also in the security event log it shows all authentication as successful.
View 2 Replies
ADVERTISEMENT
Mar 1, 2008
Hi: I a SSIS beginner and am hoping this is an easy one. I am using a Foreach loop container with an in-memory ADO recordset as the enumerator. The recordset has 3 columns and one of them I wish to fixup data in by calling a web service task that returns an integer. I see how to call the web service and return the integer into a variable but am unsure how to approach putting that variable back into the in-memory ADO recordset? Should I even be trying to do it this way?
thanks!
Henry
View 11 Replies
View Related
May 6, 2008
The Web Service Task seems to support calling methods using parameters but not (as far as I can see) using the Document/Literal calling convention. Is this correct? Is this likely to change in the future?
Thanks
*** Campbell
View 5 Replies
View Related
Feb 11, 2008
I receive the following error when i call report server web service from an asp.net application:
"The request failed with HTTP status 401: Unauthorized."
Here are the IIS logs on the reporting services server. When i open IE and browse to the web service i provide my user credentials and i can access the web service just fine. However when i call the web service via my asp.net application it looks like my credentials are not being passed??
Browsing web services via internet explorer.
2008-02-11 21:26:13 W3SVC1836052065 HQSQLDEV1 10.69.21.140 GET /Reports/images/16fold.gif - 8080 triwestdbloom 10.69.21.140 HTTP/1.1 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.2;+WOW64;+SV1;+.NET+CLR+2.0.50727;+.NET+CLR+1.1.4322) 10.69.21.140:8080 200 0 0 15
Call from ASP.NET App
2008-02-11 21:26:13 W3SVC1836052065 HQSQLDEV1 10.69.21.140 GET /Reports/images/16fold.gif - 8080 - 10.69.21.140 HTTP/1.1 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.2;+WOW64;+SV1;+.NET+CLR+2.0.50727;+.NET+CLR+1.1.4322) 10.69.21.140:8080 401 1 0 0
Here is my code:
RSWebService.ReportingService rs = new RSWebService.ReportingService();
rs.Credentials = new System.Net.NetworkCredential(@"triwestdbloom",
"blah", "triwest");
//rs.PreAuthenticate = true;
//Get all folders
RSWebService.CatalogItem[] allItems = rs.ListChildren("/", false);
//Get only folders not hidden to the user.
List<RSWebService.CatalogItem> visibleItems = new List<RSWebService.CatalogItem>();
foreach (RSWebService.CatalogItem item in allItems)
{
Response.Write(item.Name.ToString());
}
Any ideas?
View 2 Replies
View Related
Apr 19, 2007
Has anyone encountered this error? I get it when I run the installer (c#). I have seen a few posts on google and msdn, but still can't get past the error. I have tried uninstall/resinstalling the parser, registering from the command line, setting the property of the interop to "donotregister" and others with no luck.
Any ideas?
Thanks,
Lee
View 3 Replies
View Related
Feb 26, 2007
Posted the same message to the Service Broker forum. But the board is neither responsive nor active. So I am trying this forum. Hope someone can help.
I would like to call an SSIS package from a Service Broker Queue.
There is one way that I am aware of -
Using xp_cmdshell from within an activation stored procedure and using DTEXEC.
Is there a more elegant way of executing an SSIS package from within SSB?
Also,
I am not interested in writing a .NET external activator to process my
messages in the queue. I would like this operation to be strictly
database oriented. Having said this, I am also trying to avoid triggers
processing the messages in the queue.
Thank you!
View 5 Replies
View Related
Feb 26, 2007
I would like to call an SSIS package from a Service Broker Queue.
There is one way that I am aware of -
Using xp_cmdshell from within an activation stored procedure and using DTEXEC.
Is there a more elegant way of executing an SSIS package from within SSB?
Also, I am not interested in writing a .NET external activator to process my messages in the queue. I would like this operation to be strictly database oriented. Having said this, I am also trying to avoid triggers processing the messages in the queue.
Thank you!
View 6 Replies
View Related
Nov 15, 2006
I am trying to call a SSIS package from a web service hosted on the same machine as the package file is sitting. All that the package does is a simple Execute SQL task with one datasource connection.
I have set impersonation as true. When I run the package from the web service on the virual port (through Visual studio IDE) it runs fine.
http://localhost:4609/WebServiceRunSSIS/LaunchSSISPackageServiceCS.asmx
In the above case I don't think impersonation means anything coz it runs successfully even when there is no impersonation set!
I then run the same service (asmx page) from the browser after making it a web service on my machine
http://localhost/WebServiceRunSSIS/LaunchSSISPackageServiceCS.asmx
I get an error indicating package failure.
I have enabled logging in SSIS. The package log indicates the following:
Failed to acquire connection "APNetDS". Connection may not be configured correctly or you may not have the right permissions on this connection.
Effectively, my web service impersonated account (which is my admin account) is not being authenticated for any of the db connection (uses windows integrated authentication) and I find that odd. To impersonate, I have set the Impersonation to true in the web.config file. The authentication mode is windows.
Please tell me what bit of security I am missing? In case if it helps, the database connection is to my local database and hence should not deny access to my own account. And yes, I think my impoersonation is working because when I debug, the user.identity shows my user id.
Thank you,
Sumeet
View 9 Replies
View Related
Mar 3, 2006
Hello,
I was wondering if someone could point me in the direction of any sample code or documentation that allows you to execute SSIS packages from within a Script task?
In SQL Server 2000 DTS this was accomplished by instantiating the DTS.Package object and calling either the LoadFromSQLServer or LoadFromStorageFile method.
I have not been able to find any similiar logic in the SQL Server 2005 SSIS environment.
Any help would be greatly appreciated.
Thank you,
Gordon Radley
View 6 Replies
View Related
Sep 12, 2007
When running an integration services package from a windows service I get the "Object ... has been disconnected or does not exist at the server." exception after aproximately six minutes of execution.
This is *not* my windows service failing. I can loop indefinately while tracing to a log file within the service and it will run forever. While calling the mypackage.execute(...) method however, after six minutes (give or take) the exception is thrown...
my code looks something like this:
<code>
dim foo as Microsoft.SqlServer.Dts.Runtime.Application
mypackage = foo..LoadPackage(strimportPkgFilename, pkgevents)
results = myPackage.Execute(Nothing, Nothing, pkgevents, Nothing, Nothing)
</code>
<error>
A first chance exception of type 'System.Runtime.Remoting.RemotingException' occurred in mscorlib.dll
Exception in: frmMyForm.DoImports
Message: Object '/b76f98a0_5bd9_49d8_a524_eeb49d55b303/bqbhkjnaofq_ifr_cwz+srid_1.rem' has been disconnected or does not exist at the server.
</error>
oddly, this same code works perfectly if I run it within a windows form application no matter how long it takes.
It also runs fine if the package can complete in under six minutes.
Any suggestions?
Mark
View 2 Replies
View Related
Nov 6, 2007
Hi ,
Iam using 'Execute SQl task' which calls a stored procedure located in sql server database.The task's SQL source type is variable and the variable has the follwoing expression "EXEC PROC_SEL_MBO_REPORT "+@[User::V_SP_Job_Date]after evaluation it is like EXEC PROC_SEL_MBO_REPORT '01/NOV/2007'.It is working fine
Now the procedure is changed to Oracle.So I have changed it to "BEGIN PROC_SEL_MBO_REPORT " + "("+ @[User::V_SP_Job_Date]+")"+"; END"+";" after evaluation it is like BEGIN PROC_SEL_MBO_REPORT ('01/NOV/2007') END;.It is sucessfully executing from the task but no data is loaded into the tables which are used by the procedure internally.
Executing 'execute BEGIN PROC_SEL_MBO_REPORT ('01/NOV/2007') END;' is perfectly alright from SQl developer or sql plus.
Please help me.. thanks in advance
Regards,
GK
View 5 Replies
View Related
Jun 29, 2015
I am searching for a solution for Calling or consume a web service in SSIS through Script task. I have gone through so many links but i am able to find the exact solution. I am getting so many references, though i am unable to crack it.
My requirement is i need to call a web service URL through script task which is having a client certificate. When we are trying to connect to the URL it will ask for the certificate authentication. After calling this URL we will get a WSDL file from the web service, We need to consume that WSDL file and we need to identify the methods inside this WSDL and need to write the data available in this WSDL to the data base tables.
How can we call that web service URL( With certificate) through script task and how can we read the WSDL file and How we can load the data into DB table.
View 8 Replies
View Related
Apr 6, 2007
I have been struggling with a problem with the Web Service Task. I have a package that uses this and has to authenticate to the web service. This works fine on my development server but if I export the package to file and run from another computer / user, the package fails as it doesn't log in to the web service. I have found the problem to be the package was set to default of EncryptSensitiveWithUserKey and I understand why this is. If I change to use EncryptSensitiveWithPassword, it prompts for creds and works fine. My question is how do I use a package configuration file to use different creds (there is not password option for the http connection) so I can standardize on use of config files for this operation. Thanks.
Cale
View 5 Replies
View Related
Jan 11, 2007
Hi,
I m trying to use the web services task, but when i try to run it i got the following error
[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Object reference not set to an instance of an object.. at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".
This is probably because i never worked with this task, even so, does anyone knows what might be wrong?
I got a connection a wsdl, i can configure the service, method and variables(i use fixed values), and i configure the output to a variable with the type object(i have tryed string and int)
I even can download the wsdl file, but the error seems to be in the connection
Thanks
View 7 Replies
View Related
Mar 22, 2007
I have the following setup
Machine 1
WSS 3.0
Installed Reporting Service Add-in
Machine 2
Reporting Service
Sharepoint Object Model
SQL Server
Sharepoint and Report Server DB
I have congigured Reporting Service for Sharepoint Integrated Mode adn Created new Reprot Server DB
Sahre Point Integration is Green and verified link
In Sharepoint Central Administration
Manage Reporting Service Integratio i have selected the URL and "Trusted Account" as my web applications use "Windows Integration - NTLM".
All services, DB access everything running under one domain user account.
I am logged into sharepoint with same account. When clieck on set server defaults i get following error
An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. --> Server was unable to process request. ---> The request failed with HTTP status 401: Unauthorized.
Same error when i try to add a report to a sharepoint webapplication
The domain account is part of "Farm Administration", "site colletion administrato" and local administrator also on both servers.
Can someone let me know how to fix this.
View 15 Replies
View Related
Oct 10, 2006
This is the first time I've tried creating an "execute sql task" with a "full result set".
I've read in the documentation that I must set the resultname to 0, which is done, and that the variable must be of type object. Also done.
[Execute SQL Task] Error: Executing the query "select * from blah" failed with the following error: "The SelectCommand property has not been initialized before calling 'Fill'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Has anyone else had success with a full result set?
Thanks,
-Lori
View 10 Replies
View Related
Nov 7, 2006
There is not a way to pass parameters to input of Web Service tasks. I heard this problem is fixed with SQL2K5 SP1 and even the online doc says that one can choose either "value" or "variable" when specifying input for web service tasks, but after I installed what-I-think-is SP1, there is still no way to do this.
If one can only specify values (hard-coded) as input to web service tasks, then this would be a very severe limitation. I hope I'm wrong, so could someone please give a pointer. Thanks
Kevin Le
View 7 Replies
View Related
Feb 7, 2008
Hi,
In SSIS web service task - when you specify the Service and Method in the input tab for a WSDL file being used,
it seems to prompt for the parameters to be supplied only in the body of the WSDL file, and not the header.
I need to be able to provide security information (present in the SOAP header of the WSDL) like username, password etc..which is necessary to post any response to the web server, and I cannot see where I can give this in the input tab of the web service task.
Any help highly appreciated.
Thanks
View 3 Replies
View Related
Apr 5, 2007
Is it possible to do this under SSIS 2005? How? I see I can add a reference to system.web.services.dll.. but then what?
The web service was developed in vb.net/vs.net 2005 and I have no problem adding and consuming it from a web page developed using vs.net 2005 - asp.net/vb.net.
Thanks for any help or information.
View 3 Replies
View Related
May 26, 2006
Hi
Does any body knows how to convert this VBScript Code to VBDotNet (SQL SERVER 2005 INTEGERATION). Below Codes Returns a list of all the services installed on a computer, and indicates their current status (typically, running or not running).Its in VBScript Format.
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\" & strComputer & "
ootcimv2")
Set colRunningServices = objWMIService.ExecQuery("Select * from Win32_Service")
For Each objService in colRunningServices
Wscript.Echo objService.DisplayName & VbTab & objService.State
Next
Regards
Deepu M.I
View 4 Replies
View Related
May 30, 2008
Hi everybody,
I'm fairly new to the SSAS/SSIS world (though not new on databases, etc.) and I'm having some problems with the SSIS packages in our Cube environment.
Currently in our SSAS/SSIS project, we have two major connection managers, one to the database we use for loading the Cube, and the other connector for the cube itself. To load the data from the database to the cube, we wrote some SSIS packages and used the Analysis Service Processing tasks to process all the dimensions and measures. This works pretty good, so no problems here.
The real problem starts, when I try to change the connection parameters, e.g. because the server changed, or the database has been renamed.
As soon as the connection managers points to another (existing) cube, regardless if the structure is exactly the same as the one of the old cube, the tasks lose all the assigned objects from their lists. It is really annoying to add all these exactly same objects to the task again. I tried experimenting with the DelayValidation attribute so the Development Studio doesn't destroy my work every time, but when I deploy the package the Cube breaks. Obviously some kind of deeper connection is destroyed when I change the connection string.
Is there a way to prevent the package from breaking/losing objects, without me having to sacrifice 15 minutes every time I change the connection parameters?
Regards,
Tris
View 4 Replies
View Related
Dec 21, 2005
I am trying to configure a web service task. I have created the HTTP Connection, and specified the WSDL. When I select the service name on the Input page, I get the error ...
Item has already been added. Key in dictionary: 'BaseTO' Key being added: 'BaseTO'
I have previously been able to successfully configure the task in a separate project.
The Webservice methods take and return complex parameters. e.g. BaseTO above is custom transfer object type which the other transfer objects inherit. It is a List<> of some type of this object which is being passed/returned from the service.
I suspect these complex types will not be supported anyway but am puzzled by the original problem.
Any help much appreciated.
Greg.
View 1 Replies
View Related
Mar 19, 2008
Hi pals,
I am facing problems while calling Oracle stored procedure which has no parameters.
I have used Microsoft OLE DB provider for Oracle.
I have taken one Execute SQL task and set the SQLStatement as
call procedurename
I also set the IsStoredProcedure property to True for Execute SQL task.
Is there any synatx problem?
I am getting an error saying
Error: 0xC002F210 at Call Sp, Execute SQL Task: Executing the query "(call sp_procname)" failed with the following error: "ORA-00928: missing SELECT keyword.
I removed the curly braces for the SQL statement and again ran the Package, i am getting the below error
Executing the query "call sp_procname" failed with the following error: "ORA-06576: not a valid function or procedure name
Can anyone help me out on this regard!
Thanks in advance.
View 2 Replies
View Related
Sep 12, 2007
When trying to set up a Web Service Task in SSIS I get this error message when I click on the "Test Connection" button:
Invalid URI: The format of the URI could not be determined.
The web service is http://www.webservicex.net/CurrencyConvertor.asmx. When I enter the same URL into Internet Explorer it connects to the web service with no errors. We do have a proxy server. What is causing my problem?
Fred
View 5 Replies
View Related
May 31, 2007
Hi,
I have created a SSIS package with Web Service task in it,which execute remote server web service
I got following error during Web service execution
Note:I am not using proxy settings,i also checked HTTP connection and it suceeded
Error:[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Object reference not set to an instance of an object.. at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".
Please help..
Thanks in advance
View 4 Replies
View Related
Dec 17, 2007
I am trying to execute an SSIS package from an MS Access 2003 database that imports a table from the Access database into a target table in SQL 2005. I saved the package in SQL 2005 and tested it out. If I run it from the Management Studio Console with Run->Execute ... everything works just fine. However, if I try to run it using "Exec master.dbo.xp_cmdshell 'DTExec /SER DATAFORCE /DTS SQL2005TestPackage /CHECKPOINTING OFF /REPORTING V'" the execution will always fail when the Access database is open (shared mode). It will only work when the Access database is not open. The connection manager looks like this: "Data Source=E:Test.mdb;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Jet OLEDB:Global Bulk Transactions=1". The error is listed below:
Code: 0xC0202009
Source: NewPackage Connection manager "SourceConnectionOLEDB"
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not use ''; file already in use.".
What am I doing wrong?
View 5 Replies
View Related
Jun 22, 2007
Hi.
I have problem executing several SSIS-packages in a chain.
To be more precise:
I have implemented a Biztalk 2006 application which via a local webservice executes an SSIS package. The package it self calls another SSIS package, which is located in the same folder as the calling package. The second package then calls a third package etc.
The problem arises, when the first package calls the second package. An Access Denied exception is received. Can any one explain me how to fix this?
The user connected to the application-pool that the weservice is running under, has execution-rights on all of the packages.
Thanks
View 1 Replies
View Related
Feb 9, 2006
Hi, can anyone help with this pls - it's getting very fustrating! there seems to be some messages and text on this issue (looks like a common problem that MS haven't published anything about yet!) but nothing I could make heads or tails off... so any help here would be very useful!
I'm using VS2005 (release, NOT beta), the new reportviewer web control and an ASP.NET page - really basic, nothing fancy. I've set the reportviewer properties to:
ReportServerURL= http://mytestserver/reportserver
ReportPath = /AdventureWorks Sample Reports/Company Sales
I'm testing the ASP.NET page is on my local machine - and the reportviewer is connecting to a remote test server (W2k3 SP1). When I view the page, then I get
HTTP ERROR 401 Unauthorized
Directory Security on the report server is Windows Authentication - W2K3 with SP1 and Reporting Services 2005 (release, NOT beta)
Directory Security on my local machine is Windows Authentication - XP SP2 and VS2005 (release, NOT beta)
Any ideas pls?
View 5 Replies
View Related
Nov 30, 2007
I transfer files from FTP site using SSIS FTP Task. If there is no files to tranfer, the task failes, and and an email sent. How can I configure the package to send an email only if anything any error happened other than "File does not exist".
Thanks,
Michael Abramovich.
View 1 Replies
View Related
Dec 11, 2006
I installed SQL Server 2005 Integration services on Windows XP machine. Then I went to the SQL Server Configuration Manager and tried to Start the SSIS service. The "Log On As" value is "NT AUTHORITYNetworkService". But I get the follwoing error:
The request failed or the service did not respond in a timely fashion. COnsult the event log or other applicable error logs for details.
Event Log details:
------------------
The SQL Server Integration Services service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
I tried running the service using the "Local Service" and the "Local System" accounts also, but am still encountering the same error. What do I do...
View 2 Replies
View Related
May 11, 2007
I am using .net 2005 as front end and Sql Server 2005 as backend, as i face the problem on client side when i installed my software it works properly but after some time i face this error "HTTP Error 401.2 - Unauthorized: Access is denied due to server configuration.
Internet Information Services (IIS)" Also some time it shows me all records. Some time it disconnect the database connection so i can not understand why it happens like this, if somebody know then please help me out this bug.
View 1 Replies
View Related
Aug 20, 2007
My configuration database is made to work with both SQL Server 2005 and 2000, could this be an error that has to do with that?
Here is the query that I am using...
DECLARE @Server [nchar] (100)
SET @Server = (CONVERT(char(100), (SELECT SERVERPROPERTY('Servername'))))
INSERT INTO [tempdb].[dbo].[Server_Roles] (Server, ServerRole, UserName)
SELECT
@Server,
role.name,
member.name
FROM
sys.server_role_members rm
inner join
sys.server_principals member
ON
rm.member_principal_id = member.principal_id
inner join
sys.server_principals role
ON
rm.role_principal_id = role.principal_id
********Begin Errors***************
" failed with the following error: "Invalid object name 'sys.server_principals'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task I
Error: 0x0 at Execute SQL Task I: Invalid object name 'sys.server_role_members'.
Error: 0x0 at Execute SQL Task I: Invalid object name 'sys.server_principals'.
View 4 Replies
View Related
