SQL Server

Require Specific FTPS Server Certificate Fields

Demonstrates the Chilkat Ftp2.SetSslCertRequirement method, which adds a required match against the FTP server certificate. The first argument selects the certificate field (such as SubjectCN or IssuerCN) and the second is the required value. It is a void method; a non-matching certificate causes the connection to fail.

Background: Standard TLS validation confirms a certificate is trusted and valid for the hostname, but it does not guarantee you reached the specific server you expect — a form of pinning closes that gap. Requiring a certificate field to match a known value rejects any certificate that does not, defending against a mis-issued or substituted certificate even if it would otherwise pass validation. Multiple requirements can be added, and all must be satisfied.

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.SetSslCertRequirement method, which adds a required match against the FTP
    --  server certificate.  The 1st argument selects the certificate field and the 2nd is the required
    --  value.  It is a void method.

    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'
    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'

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

    --  Require that the server certificate's subject common name matches an expected value.  If the
    --  presented certificate does not match, the TLS connection fails.  Supported fields include
    --  SubjectDN, SubjectCN, IssuerDN, IssuerCN, and others.
    EXEC sp_OAMethod @ftp, 'SetSslCertRequirement', NULL, 'SubjectCN', 'ftp.example.com'

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

    PRINT 'Connected; server certificate met the requirement.'

    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
        RETURN
      END

    EXEC @hr = sp_OADestroy @ftp


END
GO