SQL Server

SFTP Host Key Fingerprint

See more SFTP Examples

Demonstrates how to get the SSH server's host key fingerprint after connecting. 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 assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

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

    DECLARE @hostname nvarchar(4000)
    SELECT @hostname = 'sftp.example.com'
    DECLARE @port int
    SELECT @port = 22
    EXEC sp_OAMethod @sftp, 'Connect', @success OUT, @hostname, @port
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    -- Get the classic MD5 fingeprint
    DECLARE @md5_fingerprint nvarchar(4000)
    EXEC sp_OAGetProperty @sftp, 'HostKeyFingerprint', @md5_fingerprint OUT

    PRINT @md5_fingerprint

    -- Sample output:
    -- ssh-rsa 3072 21:b0:d8:41:4e:ef:78:10:20:af:01:b7:71:5d:eb:94

    -- Starting in Chilkat v9.5.0.92, we can also get the SHA256 fingerprint.
    -- (it is also possible to get fingerprints using other hash algorithms such as SHA384, SHA512, etc.)

    DECLARE @includeKeyType int
    SELECT @includeKeyType = 1
    DECLARE @includeHashName int
    SELECT @includeHashName = 1
    DECLARE @sha256_fingerprint nvarchar(4000)
    EXEC sp_OAMethod @sftp, 'GetHostKeyFP', @sha256_fingerprint OUT, 'SHA256', @includeKeyType, @includeHashName

    PRINT @sha256_fingerprint

    -- Sample output:
    -- ssh-rsa SHA256:Ufgj480OsdsCZRjj9sSNM6fpgIcSJ61RsIG8usndUIY=

    -- The key type and hash name can be optionally included or not.
    SELECT @includeKeyType = 0
    SELECT @includeHashName = 1
    EXEC sp_OAMethod @sftp, 'GetHostKeyFP', @sha256_fingerprint OUT, 'SHA256', @includeKeyType, @includeHashName

    PRINT @sha256_fingerprint

    -- SHA256:Ufgj480OsdsCZRjj9sSNM6fpgIcSJ61RsIG8usndUIY=

    SELECT @includeKeyType = 1
    SELECT @includeHashName = 0
    EXEC sp_OAMethod @sftp, 'GetHostKeyFP', @sha256_fingerprint OUT, 'SHA256', @includeKeyType, @includeHashName

    PRINT @sha256_fingerprint

    -- ssh-rsa Ufgj480OsdsCZRjj9sSNM6fpgIcSJ61RsIG8usndUIY=

    SELECT @includeKeyType = 0
    SELECT @includeHashName = 0
    EXEC sp_OAMethod @sftp, 'GetHostKeyFP', @sha256_fingerprint OUT, 'SHA256', @includeKeyType, @includeHashName

    PRINT @sha256_fingerprint

    -- Ufgj480OsdsCZRjj9sSNM6fpgIcSJ61RsIG8usndUIY=

    EXEC @hr = sp_OADestroy @sftp


END
GO