Search code examples
sql-serverssissql-server-2019

SSIS encrypt and trustservercertificate connection parameters not working

I am trying to add the connection parameters Encrypt=True and TrustServerCertificate=True to connection strings in various SSIS packages.

All the connections are defined as package parameters, and all the parameters have Encrypt=True;TrustServerCertificate=True; but when i execute the package in VS2022 all the database connections are not encrypted.

enter image description here

When running on the SSIS server, I use Environment variables to set the connections string and have added the parameters there, but when i execute the package on the SSIS server, the connections are also not encrypted.

enter image description here

I am checking for encyption using sys.dm_exec_connections.

    SELECT s.session_id AS spid
    ,c.auth_scheme as Authenication
    ,c.encrypt_option as Encrypt
         ,s.login_time as loginTime
    ,s.last_request_end_time AS lastBatch
    ,s.[program_name] AS programName
      ,CASE
        WHEN s.transaction_isolation_level = 0   THEN 'Unspecified'
        WHEN s.transaction_isolation_level = 1 THEN 'ReadUncommitted'
        WHEN s.transaction_isolation_level = 2 THEN 'ReadCommitted'
        WHEN s.transaction_isolation_level = 3 THEN 'Repeatable'
        WHEN s.transaction_isolation_level = 4 THEN 'Serializable'
        WHEN s.transaction_isolation_level = 5 THEN 'Snapshot'
    END AS transactionIsolationLevel
   
FROM sys.dm_exec_sessions AS s
    LEFT JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id
     WHERE s.is_user_process = 1
and s.[program_name] = 'SSIS-Package'

enter image description here

Is there something I should be doing in the Package, Solution, or Environment to enable usage of these parameters?

Solution

  • SSIS doesnt use the standard parameters for encrypt and trustservercertificate as defined in documentation for sql client.

    Need to instead use the parameters as labeled in SSIS Connection Manager enter image description here

    Use Encryption for Data=True; Trust Server Certificate=True;

    A connection string that does encrypt connection would look like this.

    Data Source=servername;Initial Catalog=databasename;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;Use Encryption for Data=True;Trust Server Certificate=True;Application Name=SSIS-Package;