SQL Server

Get and Inspect the FTPS Server Certificate Before Login

Demonstrates the Chilkat Ftp2.GetServerCert method, which loads the leaf certificate presented on the connected FTPS control channel into a Cert object. The only argument is the Cert; an active TLS control connection is required. This example uses ConnectOnly followed by LoginAfterConnectOnly so the certificate can be inspected before any credentials are sent.

Background: Chilkat validates the server certificate during the TLS handshake, but an application often needs to apply its own check — pinning a fingerprint, matching a subject, or enforcing an internal policy — and the safe moment to do that is before revealing the login. Splitting the handshake with ConnectOnly establishes TLS and makes the certificate available via GetServerCert, while deferring USER and PASS to LoginAfterConnectOnly. If the certificate fails inspection you simply disconnect, and an untrusted or impersonating server never receives the credentials. For declarative pinning without writing inspection code, SetSslCertRequirement lets you require certificate-field values up front.

Chilkat SQL Server Downloads

Download Chilkat for SQL Server

SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  Demonstrates the Ftp2.GetServerCert method, which loads the leaf certificate presented on the
    --  connected FTPS control channel into a Cert object.  The only argument is the Cert.  It requires
    --  an active TLS control connection.
    --  
    --  This example uses ConnectOnly + LoginAfterConnectOnly so the server certificate can be
    --  inspected and validated BEFORE any credentials are sent.

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

    EXEC sp_OASetProperty @ftp, 'Hostname', 'ftp.example.com'

    --  Use explicit FTPS (AUTH TLS) on the standard FTP port.
    EXEC sp_OASetProperty @ftp, 'AuthTls', 1

    --  Establish the connection and TLS layer and receive the greeting, but do NOT send USER/PASS.
    EXEC sp_OAMethod @ftp, 'ConnectOnly', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        RETURN
      END

    --  The TLS control channel is now up.  Retrieve the server's certificate for inspection.
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @ftp, 'GetServerCert', @success OUT, @cert
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END


    EXEC sp_OAGetProperty @cert, 'SubjectCN', @sTmp0 OUT
    PRINT 'Server certificate subject: ' + @sTmp0

    EXEC sp_OAGetProperty @cert, 'IssuerCN', @sTmp0 OUT
    PRINT 'Issued by: ' + @sTmp0

    --  Perform any application-specific validation of the server certificate here -- for example,
    --  compare the fingerprint or subject against an expected value.  Only proceed to authenticate if
    --  the certificate is acceptable.  Because no credentials have been sent yet, an untrusted server
    --  never sees the username or password.
    --  
    --    if (certificate is not acceptable) { return; }

    --  The certificate passed inspection, so now provide credentials and authenticate.
    EXEC sp_OASetProperty @ftp, 'Username', 'myFtpLogin'

    --  Normally you would not hard-code the password in source.  You should instead obtain it
    --  from an interactive prompt, environment variable, or a secrets vault.
    EXEC sp_OASetProperty @ftp, 'Password', 'myPassword'

    EXEC sp_OAMethod @ftp, 'LoginAfterConnectOnly', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    PRINT 'Authenticated after verifying the server certificate.'

    EXEC sp_OAMethod @ftp, 'Disconnect', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    EXEC @hr = sp_OADestroy @ftp
    EXEC @hr = sp_OADestroy @cert


END
GO