SQL Server

SFTP Authenticate with a Private Key

Demonstrates the Chilkat SFtp.AuthenticatePk method, which authenticates the connected session using public-key authentication. The first argument is the username and the second is an SshKey private key. The matching public key must already be authorized for the account on the server.

Note: The private-key path is relative to the application's current working directory. Supply the path to your own key file.

Background: Public-key authentication is the standard for unattended SFTP transfers: the private key stays on the client, the server trusts the corresponding public key, and no reusable password crosses the wire. The SshKey object imports keys in several formats — this example loads an OpenSSH private key. For an encrypted key, set its Password property before importing.

Chilkat SQL Server Downloads

Download Chilkat for SQL Server

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
    DECLARE @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  Demonstrates the SFtp.AuthenticatePk method, which authenticates the connected session using
    --  public-key authentication.  The 1st argument is the username and the 2nd is an SshKey private
    --  key.  The matching public key must already be authorized for the account on the server.

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

    --  Step 1: Connect the SSH transport.  Port 22 is the usual port.
    DECLARE @port int
    SELECT @port = 22
    EXEC sp_OAMethod @sftp, 'Connect', @success OUT, 'sftp.example.com', @port
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    --  Load the SSH private key.  LoadText reads the file's text, which is then imported.
    DECLARE @privKey int
    EXEC @hr = sp_OACreate 'Chilkat.SshKey', @privKey OUT

    DECLARE @keyText nvarchar(4000)
    EXEC sp_OAMethod @privKey, 'LoadText', @keyText OUT, 'qa_data/id_rsa'
    EXEC sp_OAGetProperty @privKey, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @privKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END
    EXEC sp_OAMethod @privKey, 'FromOpenSshPrivateKey', @success OUT, @keyText
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @privKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    EXEC sp_OAMethod @sftp, 'AuthenticatePk', @success OUT, 'mySshLogin', @privKey
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    --  Step 3: Open the SFTP subsystem.  This must be done after authentication and before any
    --  file or directory operations.
    EXEC sp_OAMethod @sftp, 'InitializeSftp', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END


    PRINT 'Authenticated with a private key.'

    EXEC sp_OAMethod @sftp, 'Disconnect', NULL

    EXEC @hr = sp_OADestroy @sftp
    EXEC @hr = sp_OADestroy @privKey


END
GO