(SQL Server) SFTP Get/Set File Permission

Demonstrates how to set the permissions of a file on the SFTP server.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @sftp int
    -- Use "Chilkat_9_5_0.SFtp" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.SFtp', @sftp OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Connect to the SSH/SFTP server.  
    DECLARE @hostname nvarchar(4000)
    SELECT @hostname = 'sftp.example.com'
    DECLARE @port int
    SELECT @port = 22
    DECLARE @success int
    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

    -- Authenticate with the SSH server.  Chilkat SFTP supports
    -- both password-based authenication as well as public-key
    -- authentication.  This example uses password authenication.
    EXEC sp_OAMethod @sftp, 'AuthenticatePw', @success OUT, 'myLogin', 'myPassword'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    -- After authenticating, the SFTP subsystem must be initialized:
    EXEC sp_OAMethod @sftp, 'InitializeSftp', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    -- Get the file permissions for the "hamlet.xml" file on the server.
    -- The hamlet.xml file is located in the qa_data directory found under the HOME directory of the SSH user account.
    -- We are passing the remote file path (we are not passing a file handle returned by a previous call to OpenFile).
    DECLARE @isHandle int
    SELECT @isHandle = 0
    DECLARE @followLinks int
    SELECT @followLinks = 1
    DECLARE @permVal int
    EXEC sp_OAMethod @sftp, 'GetFilePermissions', @permVal OUT, 'qa_data/hamlet.xml', @followLinks, @isHandle
    IF @permVal < 0
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    -- Note: Filesystem permissions on Linux/Unix/MacOSX are typically written in octal (POSIX file permissions).  For example 0644 is octal.
    -- To convert to decimal: octal 644 = 6*8^2 + 4*8 + 4 = 420 (decimal)
    -- 
    -- The server may return a permissions value (in octal) such as 100644.
    -- The "100" indicates the file type, such as regular file or directory. 

    -- Show the decimal value of the permissions

    PRINT 'decimal permissions value: ' + @permVal

    -- To set the file permissions, pass the integer value.
    -- Let's change the permissions to 0664, which is 6*8^2 + 6*8 + 4 = 436 (decimal)
    EXEC sp_OAMethod @sftp, 'SetPermissions', @success OUT, 'qa_data/hamlet.xml', @isHandle, 436
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @sftp


END
GO