SSIS: Specify a certificate with variables for FTP and SFTP tasks

Using the latest build of the SSIS tasks available on our website it is possible to specify a certificate using user variables and the Other property. The benefit of this is that you do not have to specify a certificate at design time.

For the FTP task, you would have some user variables defined. For instance:

Certificates for Client Authentication

User::SSLCertStoreType
This should be a String type with a value that corresponds to an integer representation of the store type. Common types are:

• 0 - Windows User Store
• 1 - Windows Machine Store
• 2 - PFX File
• 3 - String representation of the PFX File
• 6 - PEM Key
• 7 - String representation of the PEM File
• 8 - Public key file
• 9 - String representation of the public key file
• 10 - String representation of the SSH public key
• 11 - p7b file
• 12 - String representation of the p7b file
• 13 - SSH public key file
• 14 - PPK file
• 15 - String representation of the PPK file

See the help file included with the tasks for a complete list of available types.

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 instance "C:\test.pfx". If the SSLCertStoreType is a Windows store the possible values for this property are:

• MY
• CA
• ROOT
• SPC

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 could set this to "Test", you would want to omit the "CN=" part when setting partial certificate subjects.

Usage of the Other property
To tell the task to use these variables you would specify the following as the value of the Other property of the component:

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

Likewise in SFTP, you would use the Other property like this:

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

Note that it's important the SSLCertSubject be set last as that will initiate a search for the certificate in the specified store.

Accepting the Server's Certificate or Host Key

By default the tasks will not accept the server's certificate (FTP) or host key (SFTP). There are multiple options for accepting these values dynamically.

Option 1
FTP: You may set the SSLAcceptServerCertAcceptAny property via an expression to true
SFTP: You may set the SSHAcceptServerHostKeyAny property via an expression to true

Option 2
FTP: You may specify the SSLAcceptServerCert* configuration settings to accept the server certificate. This is very similar to the procedure listed above for setting the SSLCert configuration settings. For instance you could set the Other property to:

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

The following are common values for store types associated with public certificate use with SSLAcceptServerCertStoreType (see help file for a complete list):

• 0 - Windows User Store
• 1 - Windows Machine Store
• 8 - Public Key File (.cer file)
• 9 - String representation of a public key file (.cer file)

SFTP: The SSHAcceptServerHostKeyFingerPrint configuration setting can be assigned to the 16-byte MD5 fingerprint of the host's key. You may supply it by HEX encoding the values in the form "0a:1b:2c:3d".

Most SFTP clients, and the verbose log output from the /n software task, will log the fingerprint in this format when attempting to connect. If you do not know the fingerprint of the server you should be able to locate this within the logs from the task or from another SFTP client.

An example of this setting would be:

SSHAcceptServerHostKeyFingerPrint=%User::SSHAcceptServerHostKeyFingerPrint%