SQL Server

FTP Active vs. Passive Data Connections

Demonstrates the data-connection-mode properties Passive, UseEpsv, AutoSetUseEpsv, PassiveUseHostAddr, and the active-mode properties ActivePortRangeStart, ActivePortRangeEnd, and ForcePortIpAddress.

Background: FTP's split into a control connection and separate data connections is the source of most of its firewall trouble. In passive mode (the default, and usually correct) the client opens the data connection, which works through client-side NAT. In active mode the server connects back, requiring the client to be reachable — hence the port-range and advertised-IP settings for opening firewall holes. EPSV is the IPv6-friendly modern form of passive with automatic PASV fallback, and PassiveUseHostAddr ignores a private IP a misconfigured server may report.

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

    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'

    --  Passive mode (the default) has the client connect to the server for each data transfer, which
    --  works through most client-side firewalls.  Active mode has the server connect back to the
    --  client.
    EXEC sp_OASetProperty @ftp, 'Passive', 1

    --  In passive mode, optionally use EPSV before falling back to PASV.  AutoSetUseEpsv lets Chilkat
    --  decide automatically.
    EXEC sp_OASetProperty @ftp, 'UseEpsv', 1
    EXEC sp_OASetProperty @ftp, 'AutoSetUseEpsv', 1

    --  PassiveUseHostAddr (default 1) uses the control-connection host address rather than the
    --  address returned by PASV, which avoids problems when a server reports a private IP.
    EXEC sp_OASetProperty @ftp, 'PassiveUseHostAddr', 1

    --  The following apply only in ACTIVE mode (Passive = 0): restrict the local port range the
    --  client listens on, and set the IP advertised to the server.
    EXEC sp_OASetProperty @ftp, 'ActivePortRangeStart', 50000
    EXEC sp_OASetProperty @ftp, 'ActivePortRangeEnd', 50100
    EXEC sp_OASetProperty @ftp, 'ForcePortIpAddress', '203.0.113.5'

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

    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