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.