SQL Server

Set an FTPS Client Certificate (Cert object)

Demonstrates the Chilkat Ftp2.SetSslClientCert method, which configures a client certificate for FTPS servers that request mutual TLS authentication. The only argument is a Cert that must have access to its private key.

Note: The local paths are relative to the application's current working directory. Absolute paths may also be used. Supply the paths appropriate to your own environment.

Background: Some high-security FTPS deployments require the client to present its own certificate during the TLS handshake — mutual TLS — as a second factor alongside the username and password. Because that happens while connecting, the certificate must be set before Connect. The Cert must carry its private key, since the client proves possession of it during the handshake.

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

    --  Demonstrates the Ftp2.SetSslClientCert method, which configures a client certificate for FTPS
    --  servers that request mutual TLS authentication.  The only argument is a Cert object that must
    --  have access to its private key.

    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

    --  Load the client certificate (with its private key) from a PFX file.
    --  The PFX password should come from a secure source rather than being hard-coded.
    DECLARE @pfxPassword nvarchar(4000)
    SELECT @pfxPassword = 'myPfxPassword'
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, 'qa_data/client.pfx', @pfxPassword
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    --  Provide the client certificate before connecting.
    EXEC sp_OAMethod @ftp, 'SetSslClientCert', @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_OAMethod @ftp, 'Connect', @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 'Connected with a client 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