SQL Server

Get Public Key in Format for ~/.ssh/authorized_keys

See more SSH Key Examples

If you have an RSA private key, then by definition you also have the public key because the RSA public key is a subset of the private key. (The public key is just the modulus + exponent, whereas the private key contains additional items.)

This example converts an RSA private key to the OpenSSH format public-key (a single line of text) that can be inserted into the ~/.ssh/authorized_keys file on the SSH server to enable public-key authentication for a particular private key.

Note: On the SSH server, such as on a Ubuntu Linux computer, the sshd config file is located at /etc/ssh/sshd_config. It contains a line that indicates the authorized keys file, and the typical default is:

AuthorizedKeysFile      .ssh/authorized_keys

SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- Load a private key from a file.  The format can be text format, such as .ppk (PuTTY private key) or .pem.
    DECLARE @privateKeyPath nvarchar(4000)
    SELECT @privateKeyPath = '/myKeys/myRsaKey.ppk'

    -- Here's an example of a non-encrypted private key in ppk format:

    -- PuTTY-User-Key-File-3: ssh-rsa
    -- Encryption: none
    -- Comment: rsa-key-20220606
    -- Public-Lines: 6
    -- AAAAB3NzaC1yc2EAAAADAQABAAABAQDKQUdx8EU6qIpVcT1NbL/sSnELJdifQa+K
    -- kOqvgKIU30wIwGMfigSeiTFXw2jWsPcMyOIRKUvR3nf8wtR6NEj3qDfGUo3b/8Fx
    -- 5BHDKuR5uU0ZJmv3Am+/hSPWOJFltL8DkxDLEpGDujLwGiUbpD9SADYhhvk9OBA7
    -- 0l70hYs9gWJBjm9CR8hPg3b3F7gOYljAY2dk6W3PGmzQi+D7BTE0yYfhEOoDX7ah
    -- sgrUnWIiIWK9cqLT8XbAb8gitnCmP0LerxRAkX25O4Gx/uGSrMxO/cRwz5UpiNL2
    -- cciM7P4vzlbcpQiKrKaBYg5GB84QqE3xCt9cEr/qD235LpSMnWRR
    -- Private-Lines: 14
    -- AAABAQClKCE7PUSK3c34b3vrmX4vaapdvA3kHjNGJ4g8wAGaoazpCJDo1D9pZgZQ
    -- 8FTP27ohSniwItSzD8NTN5ViJQfglBDXddo5Z+ODKQYIJSJk85etjd5j2i1+ay4U
    -- ZCT2tF22gYUZDpScyJOH1RGwPLMoNtv9DMbB4uH+t46qhdJp6aWvJB7L/HUhnszc
    -- qbwTG//CDL2j8Y1Mre8zvWBA8cr0I0qCzWD/Xw5MgDgpA444CFGpYb1mt8ghLniT
    -- hMGgt6scUO/lnBEYo17a9N7wBexvyk0ZgEVo6nweRnCFHijNbG+C82svrsLQuL7g
    -- o65BcEqLo+wBO+FtutTf2gtevZcBAAAAgQDnt2Freyfn1V93qZar06w7dXn4yV4G
    -- cvQ3FmJBhk7akEFuZPOceDqrAgzS+TmsH4C96ssETdMxMO4vwEjSnZAatw6TKgnK
    -- 9aDJL6/uzhk/dmBmyp7iWttN9QW9cRQvLYtwJssNDPUmF1/1cGFMpLyLfTlRBCok
    -- 8dksYys4FTUfeQAAAIEA33N+LIO8i2U1NNfy2yU5GGJoFaXwPIa/CZNzZ8hbvLwm
    -- 5yay4MKxGgizX+FhQ/OiEiFDEs1GDssKTXaqhE9mI/vKKLGYj9wEiWhwnawx56GI
    -- WhdTZPtWkU4tYj/OkABD8vp973W1k4NEjYxVg8AoZZL1Kr4zturV8KJl9v4b/ZkA
    -- AACBANAijbBLPxSTdyG9duZT89+jIdgjdjVdV5UL/xZLEh6pWbHludZHItXl62G7
    -- luuJbiqhx2FANxhSu7/tAEX3z2jx7ari51Ddf/+sqfrLwc3dPkU8YXpUNYJIaNFR
    -- LyzMMZvL2sPxnE6dxt4nR761y+EnTZpZ2oeSC21Lzq2xKcBp
    -- Private-MAC: e0c3b5eba925944355d8d63fefcec02a982c7281a87e9994c04ce956b5c3e69b

    DECLARE @sbKey int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbKey OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @sbKey, 'LoadFile', @success OUT, @privateKeyPath, 'utf-8'
    IF @success = 0
      BEGIN

        PRINT 'Failed to load the private key from a file.'
        EXEC @hr = sp_OADestroy @sbKey
        RETURN
      END

    DECLARE @key int
    EXEC @hr = sp_OACreate 'Chilkat.SshKey', @key OUT

    -- If the private key file is encrypted, then you'll need to set a password.
    -- If it's not encrypted, the password here is ignored.
    EXEC sp_OASetProperty @key, 'Password', 'private_key_password'

    -- The FromPuttyPrivateKey method will actually auto-detect the format and correctly load any string-based format.
    -- For example, you can pass in a PEM private key.
    EXEC sp_OAMethod @sbKey, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @key, 'FromPuttyPrivateKey', @success OUT, @sTmp0
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @key, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbKey
        EXEC @hr = sp_OADestroy @key
        RETURN
      END

    -- Get the public key in OpenSSH format:
    DECLARE @pubKeyStr nvarchar(4000)
    EXEC sp_OAMethod @key, 'ToOpenSshPublicKey', @pubKeyStr OUT

    PRINT @pubKeyStr

    -- Sample output:
    -- ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDKQUdx8EU6qIpVcT1NbL/sSnELJdifQa+KkOqvgKIU30wIwGMfigSeiTFXw2jWsPcMyOIRKUvR3nf8wtR6NEj3qDfGUo3b/8Fx5BHDKuR5uU0ZJmv3Am+/hSPWOJFltL8DkxDLEpGDujLwGiUbpD9SADYhhvk9OBA70l70hYs9gWJBjm9CR8hPg3b3F7gOYljAY2dk6W3PGmzQi+D7BTE0yYfhEOoDX7ahsgrUnWIiIWK9cqLT8XbAb8gitnCmP0LerxRAkX25O4Gx/uGSrMxO/cRwz5UpiNL2cciM7P4vzlbcpQiKrKaBYg5GB84QqE3xCt9cEr/qD235LpSMnWRR rsa-key-20220606

    -- To authorize the private key for a particular SSH user account, copy the above public key into the ~/.ssh/authorized_keys file.
    -- If authorized_keys already contains some keys, you can append it to a new line in the file.

    EXEC @hr = sp_OADestroy @sbKey
    EXEC @hr = sp_OADestroy @key


END
GO