Scheduling simple cdosys email task does not work

Question

I currently have a simple cdosys email task that has been scheduled to send a simple email from ssis.  The email is sent using an activex script in a "SQL 2000 DTS Package Task".  When executed manually, the email is sent ok.   When scheduled (and run under our SQL agent account), it fails.  Can anyone point me in the right direction?  Is this a permissions issue?

'-- this script seems to cause problems, but only when scheduled --
    dim mailer
    set mailer = CreateObject("CDO.Message")
    dim cdoconfig
    const cdoDispositionNotificationTo = "urn:schemas:mailheader:disposition-notification-to"
    const cdoReturnReceiptTo = "urn:schemas:mailheader:return-receipt-to"
    set cdoconfig = CreateObject("CDO.Configuration")

    with mailer
        set .Configuration = cdoconfig
        .BodyPart.charset = "unicode-1-1-utf-8"
        .BodyPart.ContentTransferEncoding = "quoted-printable"
        .Fields("urn:schemas:httpmail:importance").Value = 2
        .Fields.Update 

        .Subject      = "Notification"
        .From         = "[e-mail]"
        .TextBody   = "TEST"
        .Bcc           = "[e-mail]"
        .Send
    end with
'-------------

Also, since I have several DTS packages that are similar, I'd like to keep these packages in the SQL 2000 dts format, instead of converting them into SSIS format and using database mail.

Any help would be appreciated.

Answer 1

Almost certainly permissions. Here's the KB article on this subject:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;918760

Is your SMTP server configured to allow anonymous sending, or to allow the account being used by Agent?

-Doug

 

Answer 2

Thanks for the article.
We did try method 1 (SQL Agent proxy account). We also set method 2 for serverstorage protection level.  We don't have any sensitive information so we skipped method 3 (Encrypt Sensitive with password).  As for method 4, we do have package configuration files that have been set as well.

Also, our SMTP server allows anonymous connections.

However, since we are trying to execute activex script tasks using CDOSYS, we're assuming perhaps there is some security permissions issue we overlooked with "component services" perhaps?  We also tried adding our SQL agent account (as well as proxy account) into our local admins group, but without any luck.  It still runs only manually, but fails for CDOSYS when scheduled.