TSQL Script that can identify SSIS packages that using SSL certificates in their operations.

Just wanted to confirm whether you are referring to the SSL Certificated enabled for SMTP operations or for something else..
If you refer to the EnableSSL property of SMTP Connection Manager, then we can find it out, kindly confirm.

If you wish to identify whether the Source or Destination Connection managers are encrypted using SSL Certificate, then we can try to find it out from the Connection manager string..

No. Cause SSL or TLS is as the name says on the session layer (layer 5) whereas T-SQL is above the application layer (layer 7).

What is your use-case?

Hi Raja

Great clarifying question  I wish to identify whether the Source or Destination Connection managers are encrypted using SSL Certificate.

How could I do this via the connection string?.

Thank you.

The connection managers are never encrypted.

hi ste5an,

Can you link an article that discusses this?

Thank you​​​​​​​​​​

There is no article needed. The connection manager is an entity running on standard x86/x64 hardware. Here we don't have encrypted object instances.

A connection manager may use connections encrypted with SSL, but this is a different thing.

Hi Ste5an,

'A connection manager may use connections encrypted with SSL, but this is a different thing'

Thank you for rephrasing this is what I would like to determine for my SSIS packages.

Raja | Ste5an any thoughts, ideas.

Cheers

Hi Raja,

Yes, all of our packages are in the SSIS Package store.

Thank you. 

Hi Raja,

This post from SQL shack gives the rest of the info I need.

https://www.sqlshack.com/how-to-retrieve-information-about-ssis-packages-stored-in-msdb-database/

Thank you. 

Yes, seems a good article..
However, pls be noted that there might be slight variation in the nodes structures based upon SSIS version and hence you can modify the XML nodes path accordingly to fetch the connection manager string values.

Cheers raja

Caveat: You ran into exactly the obvious pitfall. Encyrpt=True does you only tell, whether the TLS encryption layer is used. You cannot tell whether it uses one of the TLS or SSL protocols..

The answer to your question is about SSIS packages that using SSL certificates in their operations is: No, there is no general T-SQL approach.

There may be cases where you can, but no general way to do so. The reason for this is, that TLS happens layers below T-SQL.
The only general answer is: Any secure systems uses TLS 1.3, maybe TLS 1.2, but for sure not SSL 3.0 or lesser.

btw, SSL 3.0 was that last SSL protocol and was superseded by TLS 1999 (see RFC 2246).

Hi Ste5an,

Cheers for the info a have noticed this today i.e. When Encrypt=True we have no way to tell whether an SSIS package is using SSL or TLS. Thankfully the query I found in the post above narrowed the amount SSIS packages I need to test likely using Wireshark from 87 down to 29.

Thank you again for the assistance. 

>> Encyrpt=True does you only tell, whether the TLS encryption layer is used. You cannot tell whether it uses one of the TLS or SSL protocols..

Yes, that's correct..
However, with SQL Server version, we can guess whether it is SSL or TLS as 2016 onwards supports TLS only.

Starting with SQL Server 2016 (13.x), Secure Sockets Layer (SSL) has been discontinued. Use Transport Layer Security (TLS) instead.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-2017

One more pitfall, lets say you have created a Source/ Destination Connection Manager with Encrypt = True but lets say this Source/Destination Connection manager is not used inside your package at all, this one needs manual verification or confirmation.