How to get privatekey.ppk in ssh private key object in ssis package ruuning from sql server

Question

Hi,

I am able to perform password less sftp in my local ssis package using ssh private key object referencing private key on my desktop.

But I have to deploy this ssis package in sql server which will then run by sql agent job. Where can I store the privatekey.ppk so that I can refer in ssis package in sql server?

Please let me know.

Thanks.

Answer 1

Hi,

Even though SQL agent may have access to filesystem on the target server (depending on the user the SQL agent runs on), I would recommend you to store the private key as a string either in your source code or in some database table.

This approach requires two steps:

1) Have your private key in an appropriate text format.
You need a text file starting with
-----BEGIN ENCRYPTED PRIVATE KEY-----
If your private key is already in such format, skip this step.
If you have a PPK file, you have to convert it to the desired format. To achieve it, you can use our KeyGenerator utility. You can find it in the Samples folder of Rebex SFTP component.

• start the KeyGenerator application
• click on "Load" to load your PPK file
• fill your passphrase to the "Passphrase" fields
• click on "Save private key"

2) Construct the SshPrivateKey from string

• From your text file, use only the text between the lines
  -----BEGIN ENCRYPTED PRIVATE KEY-----
-----END ENCRYPTED PRIVATE KEY-----
• Put it to your program or to your database.

• Use the following code

  // load the private key from string
  string privateKeyText =@"...paste_your_key_here...";
  SshPrivateKey privateKey = new SshPrivateKey(Convert.FromBase64String(privateKeyText), "myTestPassword");
  
  // connect to a server 
  var client = new Sftp();
  client.Connect("sftp.example.com");
  
  // log in
  client.Login("tester", privateKey);