I'm using SSIS to connect to a Cisco web service hosted on the Cisco machine that manages our telephones. I've tested the service in SoapUI and have found everything to be working. I can get/use the WSDL, can connect to the service using authentication credentials, get an expected reply.
I first tried to use a Web Service Task. I set up my Http Connection Manager, selected it to use credentials, and ran the test. The test completed successfully. I downloaded the wsdl and saved it locally. In the Web Service Task I point to the wsdl file and the service and methods are detected in the input pane. But when I execute the task, I get the following exception:
Error: 0xC002F304 at Web Service Task, Web Service Task: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The Web Service threw an error during method execution. The error is: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.. 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()".
Again, my Http connection test passed, and I’ve retyped my credentials that enabled me to successfully test with SoapUI several times now.
After being unable to resolve the issue with a Web Service Task, I tried using a Script Task. I set up a HttpWebRequest and set all the header properties the same way they were while testing in SoapUI. Also, I set the Authorization: Basic value to correct value. I then built the data String that is passed to a StreamWriter exactly the way it was for the SOAP Envelope in SoapUI. The StreamWriter Writes and then Closes, but when I call GetResponse, I get the wonderful exception of:
Warning: 0x0 at Script Task 1, WEB EXCEPTION CAUGHT: : The server committed a protocol violation. Section=ResponseStatusLine Error: 0x6 at Script Task 1: The script returned a failure result.
For this issue I’ve tried
Setting ProtocolVersion to 1.0 and then tried 1.1
Setting KeepAlive to false
Setting ServicePoint.Expect100Continue = false;
Tried to programmatically set useUnsafeHeaderParsing to true but the script would not compile even though the editor did not indicate any errors (may have been caused by reflection, I don’t know)
Don’t know how to access the configs in my SSIS package to set useUnsafeHeaderParsing to true that way
None of these worked. It doesn’t matter which task I use, I just need one to work. Anybody have any ideas/suggestions or can point me toward anything else I can use to research these issues? (I’ve been Googling for two days now :( ).
Below is a snippet of my code from my script in case anyone else can see an issue I’m overlooking. Thank you for your time and reading my question!
StreamReader responseReader = null;
StreamWriter requestWriter = null;
string data = getPayload();
HttpWebRequest httpRequest = (HttpWebRequest)WebRequest.Create("http://***********:8443/realtimeservice2/services/RISService");
httpRequest.ProtocolVersion = System.Net.HttpVersion.Version11;
httpRequest.Method = "POST";
httpRequest.AutomaticDecompression = DecompressionMethods.Deflate | DecompressionMethods.GZip;
httpRequest.ContentType = "text/xml;charset=UTF-8";
httpRequest.Accept = "Accept: text/*";
httpRequest.Headers.Add("SOAPAction", "executeCCMSQLStatement");
httpRequest.Headers.Add("Authorization", "Basic ********");
httpRequest.ContentLength = data.Length;
httpRequest.Host = "*********:8443";
httpRequest.KeepAlive = false;
httpRequest.UserAgent = "Apache-HttpClient/4.1.1 (java 1.5)";
requestWriter = new StreamWriter(httpRequest.GetRequestStream(), System.Text.Encoding.ASCII);
requestWriter.Write(data);
requestWriter.Close();
WebResponse webResponse = httpRequest.GetResponse();//EXCEPTION THROWN
UPDATE
So we’ve tried installing the service host’s certificate into the trusted root store of the machine making the call. The connection manager can now test an https successfully, but when I run the package I still get the same ‘Could not establish trust relationship for the SSL/TLS’ issue. My desktop can still successfully connect to the service, make the call, and get back expected results. But for some reason I cannot get the DB server and the service host to play nice together.
