SSIS Tips: Enforcing TLS 1.2 For SSIS 2012 Connections

Impetus

The main inspiration behind this article comes from a recent issue faced in one of my projects for configuring TLS 1.2 based connectivity to a HTTP endpoint and steps taken in resolving the same

Scenario

In one of the projects, there was a SQL Agent job which started suddenly failing. There was no changes done on any of the core functionality so it was evident that the failure had something to do with some changes done at the destination end.The failure was attributed to a task which was utilizing a HTTP connection manager to connect to a URL for downloading response in  XML format.

The failure message looked like below in the SQL Agent history

i.e.
 Error: The underlying connection was closed: An unexpected error occurred on a send.
  Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
    An existing connection was forcibly closed by the remote host

On analysis and checking with the admin team, we came to know that they enabled TLS 1.2 on the server endpoint. This was causing the connection to fail as the SQLServer we had was on 2012 version and it was still using TLS 1.0 based connection.

The challenge was to see how this can be sorted out. This article discusses a quick solution which can be applied in scenarios like above to ensure successful connection

Solution

The solution involves forcing the connection to use TLS 1.2 this can be done by using the below single line of code inside a script task in your SSIS package.

This should be the first task inside the package and will have single line code as shown below

    System.Net.ServicePointManager.SecurityProtocol = (System.Net.SecurityProtocolType)3072;

Here's how code looks inside the Script Task

3072 corresponds to TLS 1.2 protocol in the SecurityProtocolType enumeration within System.Net namespace

namespace System.Net
{
    [System.Flags]
    public enum SecurityProtocolType
    {
       Ssl3 = 48,
       Tls = 192,
       Tls11 = 768,
       Tls12 = 3072,
    }
}

And you need to have .Net framework 4.5 installed to get this work correctly.

Once this is done it enforces TLS 1.2 for the connection following and connections would be successful.

Conclusion

As shown above this method can be used in SSIS 2012 to ensure TLS 1.2 protocol is enforced for connecting to a client app which is using enhanced encryption