FTP and SFTP Certificate Configuration Using Variables in SSIS

When using /n software SSIS tasks (such as FTP and SFTP), certificates can be configured dynamically at runtime using SSIS variables and the Other property. This approach avoids hardcoding certificate details at design time and allows for more flexible deployments across environments.

For the FTP task, you would define user variables such as the following:

Certificates for Client Authentication

User::SSLCertStoreType

This should be a String value corresponding to the integer representation of the certificate store type. Common types are documented in the online help

User::SSLCertStore

If the SSLCertStoreType is a PFX file (2) or a PEM Key (6) this will be a full path to the file on disk (for example, "C:\test.pfx"); if it is a Windows certificate store, this value represents the store name. See the online help for details

User::SSLCertStorePassword

This will hold the certificate password if required.

User::SSLCertSubject

The special value of * tells the task to pick the first certificate with private key that it found in the store. You can also match partial subjects by providing part of the subject.

For example if the certificate subject were "CN=Test Certificate, OU=People, C=US" you would set this to "Test" (omit the "CN=" part when using partial matches).

Usage of the Other property

To instruct the task to use these variables, set the following in the Other property:

SSLCertStoreType=%User::SSLCertStoreType%
SSLCertStore=%User::SSLCertStore%
SSLCertStorePassword=%User::SSLCertStorePassword%
SSLCertSubject=%User::SSLCertSubject%

Likewise, in SFTP:

SSHCertStoreType=%User::SSHCertStoreType%
SSHCertStore=%User::SSHCertStore%
SSHCertStorePassword=%User::SSHCertStorePassword%
SSHCertSubject=%User::SSHCertSubject%

Note that SSLCertSubject should be set last, as it triggers the certificate lookup in the specified store.

Accepting the Server's Certificate or Host Key

By default, the tasks do not automatically trust the server's certificate (FTP) or host key (SFTP), but this behavior can be configured dynamically by specifying trusted certificates for FTP through the Other property using the required certificate store settings as shown below:

SSLAcceptServerCertStoreType=%User::SSLAcceptServerCertStoreType%
SSLAcceptServerCertStore=%User::SSLAcceptServerCertStore%
SSLAcceptServerCertStorePassword=%User::SSLAcceptServerCertStorePassword%
SSLAcceptServerCertSubject=%User::SSLAcceptServerCertSubject%

For SFTP, the server's host key can be trusted by specifying its fingerprint using the SSHAcceptServerHostKeyFingerPrint configuration setting in MD5 format (for example, "0a:1b:2c:3d") as shown below:

SSHAcceptServerHostKeyFingerPrint=%User::SSHAcceptServerHostKeyFingerPrint%

This fingerprint can typically be obtained from connection logs or other SFTP clients.