1

I'm using SSIS (Visual Studio 2015 targeting SQL Server 2016) to build a script task to consume an API. I built the following code on my local machine (Visual Studio 2017) and it works great:

    public void Main()
    {
        string baseURL = "API.com";

        Uri requestUri = new Uri(baseURL);
        HttpWebRequest req = WebRequest.Create(requestUri) as HttpWebRequest;

        string username = "APIKEY";
        string password = "";
        string auth64 = Convert.ToBase64String(System.Text.ASCIIEncoding.ASCII.GetBytes(username + ":" + password));
        //ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls |SecurityProtocolType.Tls11 |
        //SecurityProtocolType.Tls12;
        //ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls | SecurityProtocolType.Tls11 |
       // SecurityProtocolType.Tls12|SecurityProtocolType.Ssl3;

        ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
        //System.Net.ServicePointManager.SecurityProtocol |= SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;

        //ServicePointManager.SecurityProtocol = (SecurityProtocolType)768 | (SecurityProtocolType)3072;

        //System.Net.ServicePointManager.SecurityProtocol = (System.Net.SecurityProtocolType)3072;
        //System.Net.ServicePointManager.SecurityProtocol = (SecurityProtocolType)0x00000C00;
        req.Headers.Add("Authorization", "Basic " + auth64);
        //req.KeepAlive = false;
        //req.ProtocolVersion = HttpVersion.Version10;
        //req.ServicePoint.ConnectionLimit = 1;

        try
        {
            using (HttpWebResponse response = (HttpWebResponse)req.GetResponse())
            {
                using (StreamReader sr = new StreamReader(response.GetResponseStream()))
                {
                    Console.WriteLine("Worked");
                }
            }
        }
        catch (Exception e)
        {
            Console.WriteLine("Didn't Work");
        }
    }

As soon as I paste this into a script task and try to run it I get the following error:

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host

Digging around a little more I see this:

The underlying connection was closed: an unexpected error occurred on send

Everything on this error that I can find suggests changing the SerivcePointManager to TLS12. Please note all of the iterations that have been commented out in the above code. Nothing seems to work.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Chris
  • 265
  • 2
  • 16
  • One of the accepted/upvoted answers [here](https://stackoverflow.com/questions/5420656/unable-to-read-data-from-the-transport-connection-an-existing-connection-was-f) might help you out. It's a pretty comprehensive list, some of which you've already tried. – vhoang Nov 29 '19 at 23:45
  • I've just gone through that list....Nothing works so far. – Chris Nov 30 '19 at 01:33
  • 3
    Try moving "ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;" up and put it right after "string baseURL" line, before creating the HttpWebRequest object and try that? – Tim Mylott Dec 02 '19 at 16:13
  • Tim MyLott - I moved the security protocol to where you indicated and it WOKRED!!!!!! – Chris Dec 02 '19 at 18:19
  • Great, glad I could help – Tim Mylott Dec 02 '19 at 22:05

0 Answers0