(SQL Server) Generate RSA Key for SFTP

See more SSH Key Examples

Generates an 2048-bit RSA key for SSH/SFTP. Your application will use the generated private key. The public key is to be uploaded to the server and stored in the ".ssh" directory located under the HOME directory of the SSH/SFTP user account.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @key int
    -- Use "Chilkat_9_5_0.SshKey" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.SshKey', @key OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @numBits int
    SELECT @numBits = 2048
    DECLARE @exponent int
    SELECT @exponent = 65537
    DECLARE @success int
    EXEC sp_OAMethod @key, 'GenerateRsaKey', @success OUT, @numBits, @exponent
    IF @success <> 1
      BEGIN

        PRINT 'Bad params passed to RSA key generation method.'
        EXEC @hr = sp_OADestroy @key
        RETURN
      END

    -- Note: Generating a public/private key pair is CPU intensive
    -- and may take a short amount of time (more than few seconds,
    -- but less than a minute).

    DECLARE @exportedKey nvarchar(4000)

    DECLARE @exportEncrypted int

    -- Export the RSA private key to encrypted PuTTY format:
    EXEC sp_OASetProperty @key, 'Password', 'secret'
    SELECT @exportEncrypted = 1
    EXEC sp_OAMethod @key, 'ToPuttyPrivateKey', @exportedKey OUT, @exportEncrypted
    EXEC sp_OAMethod @key, 'SaveText', @success OUT, @exportedKey, 'qa_output/rsa_privkey_putty_encrypted.ppk'

    -- Export the public key to openSSH format..
    EXEC sp_OAMethod @key, 'ToOpenSshPublicKey', @exportedKey OUT
    EXEC sp_OAMethod @key, 'SaveText', @success OUT, @exportedKey, 'qa_output/id_rsa.pub'

    -- Sample id_rsa.pub:
    -- ssh-rsa
    -- AAAAB3NzaC1yc2EAAAADAQABAAA.....6tK3+vjwX/YC9dIXUz2Z 


    PRINT 'Finished.  Upload the id_rsa.pub to your .ssh directory located on the SSH/SFTP server under your user account''s HOME directory'

    PRINT '(Your HOME directory is the default directory you are in when you login via an SSH terminal.)'

    -- After the id_rsa.pub is uploaded to your user account on the SSH/SFTP server, you can authenticate using the private key.
    -- Your application will load the private key (.ppk) as shown here: Load SSH/SFTP Private Key .ppk
    -- and then authenticate by calling AuthenticatePk (or AuthenticatePwPk).

    EXEC @hr = sp_OADestroy @key


END
GO