SQL Server

Resolve the FTP Password from OS Secure Storage

Demonstrates the EnableSecrets property, which lets password properties contain a secret specification beginning with !! that Chilkat resolves from the operating system's secure storage instead of using the literal text.

Background: This is a cleaner alternative to reading a secret yourself and assigning it: with EnableSecrets on, you set the password to a reference like !!my_secret_name and Chilkat fetches the real value from the platform's secure store. The literal password never appears in your source or configuration, which is exactly the practice recommended throughout these examples.

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'

    --  EnableSecrets (default 0) allows password properties to contain a "secret specification"
    --  beginning with "!!" that Chilkat resolves from the operating system's secure storage instead
    --  of using the literal text.
    EXEC sp_OASetProperty @ftp, 'EnableSecrets', 1

    --  Provide a secret specification rather than a literal password.  Chilkat looks up the named
    --  secret in the OS secure store.
    EXEC sp_OASetProperty @ftp, 'Password', '!!my_ftp_password_secret'

    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 using a password from OS secure storage.'

    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