Post reply

SMTP authentication using sp_send_cdosysmail?

  • November 4, 2008 at 6:59 am

    #94332

    We've been using sp_send_cdosysmail for a long time to send internal email notices generated from SQL Server 2000. We have our own internal mail server, and have always sent to internal addressess.

    We're now needing to send an email from a database on a remote server, to an address out of our domain realm. Of course, we're getting a Relaying Denied error, proper authentication required error.

    How the heck can I set some SMTP authentication/login info in cdosysmail to send messages? Can I? Every link I hit on Google ends up as a dead end, and the linked microsoft.com pages in the cdosys stored proc are dead (Boooooo, Microsoft! Come on!). Any help here would be great - thanks!

  • November 4, 2008 at 7:57 am

    #893636

    the CDO object has the ability to send the SMTP user And password, but the default procedure

    sp_send_cdosysmail does not have parameters for it;

    if you don't have the sp you can get it here:

    http://support.microsoft.com/kb/312839

    searching for "CDO Authentication" sends you to a lot of examples which pass the CDO object the parameters, but no sql examples;

    here's a .net example:

    http://www.codeproject.com/KB/dotnet/SystemWeb_Mail_SMTP_AUTH.aspx

    you can see the parameters like Authenticate,UserName and Apssword there, which were not included in the default version of the proc sp_send_cdosysmail .

    I'm testing an updated version of the proc now; when it works, I'll post the modified version.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 4, 2011 at 4:08 am

    #1282349

    Even though this is an old post there is no solution posted. Here is one I have found to work after HOURS of trawling and hair loss the following has worked for me and...when you look at it is quite obvious too! You may need to put a bit of additional conditional code into your modified sp_send_cdosysmail stored procedure to check to see if you want/need to use authentication but the lines you are looking for to get this to work are:-

    EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','1' -- this indicates a clear text password

    EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value','AN_ACCOUNT@YOURHOSTINGDOMAIN.WHATEVER' -- MODIFY THIS ENTRY TO SUIT

    EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value','PASSWORD FOR THE ACCOUNT ABOVE' -- MODIFY THIS ENTRY TO SUIT - REMEMBER...THIS IS CLEAR TEXT!!

    There may be some refinement to this to keep the security concious happy but after HOURS of trawling the net to find a solution and getting nowhere (at least for the SQL stored proc solution - lots of VBS, .NET solutions - which gave some clues) this one works for me! Worth copying the error logging code for each of these EXEC steps to match the rest of the sp code but that's a copy and paste job!

    Off now to see if I can find out more RE the authentication types to see if I can secure this more but not wasting too much time!!

    Hope this helps some folks!

    Graeme

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply