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

0 votes
asked Jun 6 by ankitakha (120 points)

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.

Applies to: Rebex SFTP

1 Answer

0 votes
answered Jun 6 by Jan Sotola (16,920 points)

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);
    
...