SQL Server

Set the SMTP or POP3 Password from a SecureString

Demonstrates the Chilkat MailMan.SetPassword method, which sets the POP3 or SMTP password from a SecureString. The first argument selects which password is being set: pop3 for the POP3 password (equivalent to the PopPassword property) or smtp for the SMTP password. In this example the password is obtained at runtime from an interactive prompt, moved into a SecureString, and used to verify the SMTP login.

Background: A SecureString keeps a secret encrypted in memory and limits how long it is exposed — unlike a plain string, which sits in the clear, may be silently copied by the runtime, and can linger where a crash dump or memory scan could reveal it.

But in-memory protection only matters if the secret isn't already exposed elsewhere. Writing the password as a literal in the source would defeat the whole purpose: it would be readable by anyone with the code, committed into source control history, and recoverable from the compiled application. The password should instead come from outside the program, such as:

An interactive prompt (shown here) — for applications where a person is present to supply the password, so it is never stored at all.
A protected file kept out of source control, with filesystem permissions restricting it to the account that runs the application.
An environment variable or configuration supplied at deployment time, so the secret never lives with the code.
A secrets manager or vault (or an OS keychain), which centralizes storage, access control, auditing, and rotation.

Using SetPassword with a SecureString, rather than the plain SmtpPassword / PopPassword properties, keeps the secret protected for the remainder of its life in the process.

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 MailMan.SetPassword method, which sets the POP3 or SMTP password from a
    --  SecureString.  The 1st argument selects which password is being set: use "pop3" for the
    --  POP3 password (equivalent to the PopPassword property) or "smtp" for the SMTP password.

    DECLARE @mailman int
    EXEC @hr = sp_OACreate 'Chilkat.MailMan', @mailman OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    --  Configure the SMTP server connection.  The password is supplied separately, below.
    EXEC sp_OASetProperty @mailman, 'SmtpHost', 'smtp.example.com'
    EXEC sp_OASetProperty @mailman, 'SmtpPort', 465
    EXEC sp_OASetProperty @mailman, 'SmtpSsl', 1
    EXEC sp_OASetProperty @mailman, 'SmtpUsername', 'user@example.com'

    --  The password must not be hard-coded as a literal in the source.  That would defeat the
    --  purpose of using a SecureString, because the secret would be visible in the source code,
    --  committed to source control, and recoverable from the compiled application.
    DECLARE @password nvarchar(4000)

    --  --------------------------------------------------------------------------------------
    --  Insert code here to obtain the password from an interactive prompt and assign it to the
    --  "password" variable.  (The prompting code is not shown, because it is specific to the
    --  application and programming language.)
    --  --------------------------------------------------------------------------------------

    --  Move the password into a SecureString, which keeps it encrypted in memory.
    DECLARE @securePassword int
    EXEC @hr = sp_OACreate 'Chilkat.SecureString', @securePassword OUT

    EXEC sp_OAMethod @securePassword, 'Append', @success OUT, @password
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @securePassword, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @securePassword
        RETURN
      END

    --  Set the SMTP password from the SecureString.
    EXEC sp_OAMethod @mailman, 'SetPassword', @success OUT, 'smtp', @securePassword
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @securePassword
        RETURN
      END

    EXEC sp_OAMethod @mailman, 'VerifySmtpLogin', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @securePassword
        RETURN
      END


    PRINT 'Authenticated using a password held in a SecureString.'

    EXEC @hr = sp_OADestroy @mailman
    EXEC @hr = sp_OADestroy @securePassword


END
GO