CDOSYS Mail Failing outside my domain
-
Hoping someone could point me in the right direction.
I had set up a sproc to send email alerts using CDOSYS (based on a handy script from SQLServerCentral). Recently, the emails have started to fail when we changed to an external mail server. Here's the error message:
-2147220977
The server rejected one or more recipient addresses.
The server response was: 550 Authentication is required for relay
**Thanks In Advance for your time!!
The TSQL:
declare @vcFrom varchar(128)
declare @vcBody varchar(8000)
declare @vcSMTPServer varchar(255)
declare @vcSenderPass varchar(128)
set @vcFrom= 'x@mydomain.net'
set @vcBody= 'body '
set @vcSMTPServer= 'remote.mailserver'
set @vcSenderPass= 'password'
declare @vcTo varchar(100) set @vcTo='me@otherdomain.com
declare @vcSenderName varchar(128) set @vcSenderName= @vcFrom
declare @vcSubject varchar(255) set @vcSubject= 'subject'
declare @vcSendUsing char(1) set @vcSendUsing= '2'
declare @vcPort varchar(3) set @vcPort= 25 --'587' '8889'
declare @vcAuthenticate char(1) set @vcAuthenticate= '1'
declare @vcDSNOptions varchar(2) set @vcDSNOptions= '0'
declare @vcTimeout varchar(2) set @vcTimeout= '30'
Declare @iMessageObjId int
Declare @iHr int
Declare @vcErrMssg varchar(255)
Declare @vcErrSource varchar(255)
Declare @vcErrDescription varchar(255)
--Default values
Set @iHr = 0
Select @vcTo = Replace(@vcTo, ';', ',')
If @vcSubject is null Set @vcSubject = 'Message from SQL Server '
If @vcFrom is null Set @vcFrom = 'SQL-'
-- Create the SMTP message object.
EXEC @iHr = sp_OACreate 'CDO.Message', @iMessageObjId OUT
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error creating object CDO.Message.'
Goto ErrMssg
End
print 'smtp object created'
--Set Configuartion fields
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',
@vcSMTPServer
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value',
@vcSendUsing
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPConnectionTimeout").Value',
@vcTimeout
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPServerPort").Value',
@vcPort
/*SETTING AUTHENTICATION HERE */
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPAuthenticate").Value',
@vcAuthenticate
if @vcAuthenticate = 1
BEGIN
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value',
@vcSenderName
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value',
@vcSenderPass
print 'authetication set'
END
EXEC @iHr = sp_OAMethod @iMessageObjId, 'Configuration.Fields.Update'
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error updating Message configuration fields.'
Goto ErrMssg
End
EXEC @iHr = sp_OAMethod @iMessageObjId, 'Fields.Update'
print 'configuration fields updated'
-- Set SMTP message object parameters.
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'To', @vcTo
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Subject', @vcSubject
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'From', @vcFrom
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'DSNOptions', @vcDSNOptions
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Sender', @vcSenderName
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'HTMLBody', @vcBody
EXEC @iHr = sp_OAMethod @iMessageObjId, 'Fields.Update'
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error updating Message parameters.'
Goto ErrMssg
End
print 'smtp object parms updated'
-- Send the message.
EXEC @iHr = sp_OAMethod @iMessageObjId, 'Send'
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error Sending e-mail.'
Goto ErrMssg
End
Else
Print 'Mail sent.'
Cleanup:
-- Destroy the object and return.
EXEC @iHr = sp_OADestroy @iMessageObjId
EXEC @iHr = sp_OAStop
Return
ErrMssg:
Begin
Print @vcErrMssg
If @iHr <> 0
Begin
EXEC sp_OAGetErrorInfo @iMessageObjId, @vcErrSource Out, @vcErrDescription Out
Print @iHr
Print @vcErrSource
Print @vcErrDescription
End
If @vcErrMssg = 'Error creating object CDO.Message.'
Return
Else
Goto Cleanup
End
SMTP LOG
Here's a snippet from the smtp log. It seems obvious that it is not authenticating. I don't understand why when I set authtication on and supply a userid and password in my sqlserver sproc.
--sending outside domain, authentication required for relay
11:06:56 [65.43.61.1xx][61040850] cmd: HELO chev0
11:06:56 [65.43.61.1xx][61040850] rsp: 250 MailA40 Hello [65.43.61.130]
11:06:59 [65.43.61.1xx][61040850] cmd: MAIL FROM: <xxxxxxxxx@x.net>
11:07:00 [65.43.61.1xx][61040850] rsp: 250 OK <xxxxxxxxx@x.net> Sender ok
11:07:00 [65.43.61.1xx][61040850] cmd: RCPT TO: <me@otherdomain.net>
11:07:00 [65.43.61.1xx][61040850] rsp: 550 Authentication is required for relay
11:07:00 [65.43.61.1xx][61040850] cmd: QUIT
11:07:00 [65.43.61.1xx][61040850] rsp: 221 Service closing transmission channel
11:07:00 [65.43.61.1xx][61040850] disconnected at 7/30/2007 11:07:00 AM
-
Ignore this post.
-
Any firewall in the production box. Check that and let know.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Sugesh,
Thanks for your reply.
The response I got from our network admin in response to the firewall was that the firewall isn't an issue in this case. It's not checking for SMTP authentication. I even had him disable the anti virus to see if it would send. Still fails.
Should I check something else in regard to the firewall?
Thank You, Kim
-
I even asked our hosting company to SMTP authentication bypass for my ip address. Still, I have had no success getting email to send when sending to an email address outside my domain.
I checked the event log on the server and there is no error messages relating to this issue. The next suggestion my network admin suggested was looking at the mail logs.
I have to believe it is an issue with the manner in which the server is configured since it works from my development server, but not my production server. I am using the exact same code. Argh, this is so frustrating when I have not changed a thing.Now that both machines were restarted, it fails on dev and prod.
I'd be thrilled to hear anybody's suggestion on things I should be checking or things I should be asking my mail or network admin to check into.
-
Here's a snippet from the smtp log. It seems obvious that it is not authenticating. I don't understand why when I set authtication on and supply a userid and password in my sqlserver sproc.
--sending outside domain, authentication required for relay
11:06:56 [65.43.61.130][61040850] cmd: HELO chev0
11:06:56 [65.43.61.130][61040850] rsp: 250 MailA40 Hello [65.43.61.130]
11:06:59 [65.43.61.130][61040850] cmd: MAIL FROM: <xxxxxxxxx@x.net>
11:07:00 [65.43.61.130][61040850] rsp: 250 OK <xxxxxxxxx@x.net> Sender ok
11:07:00 [65.43.61.130][61040850] cmd: RCPT TO: <me@otherdomain.net>
11:07:00 [65.43.61.130][61040850] rsp: 550 Authentication is required for relay
11:07:00 [65.43.61.130][61040850] cmd: QUIT
11:07:00 [65.43.61.130][61040850] rsp: 221 Service closing transmission channel
11:07:00 [65.43.61.130][61040850] disconnected at 7/30/2007 11:07:00 AM
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("<A href="http://schemas.microsoft.com/cdo/configuration/SMTPAuthenticate"
.Value'">http://schemas.microsoft.com/cdo/configuration/SMTPAuthenticate").Value',@vcAuthenticate
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("<A href="http://schemas.microsoft.com/cdo/configuration/sendusername"
.Value'">http://schemas.microsoft.com/cdo/configuration/sendusername").Value',@vcSenderName
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("<A href="http://schemas.microsoft.com/cdo/configuration/sendpassword"
.Value'">http://schemas.microsoft.com/cdo/configuration/sendpassword").Value',@vcSenderPass
EXEC @iHr = sp_OAMethod @iMessageObjId, 'Configuration.Fields.Update'
-
The one thing to check would be the value of the username you're sending in, depending on the smtp server that may be your complete username@domainname.com or just username with out the @domainname.com?
-
Joe,
Thanks for the info. I did try as you suggesting running the sproc without the '@domain.com' and still get the same error message:
The server response was: 550 Authentication is required for relay
I guess I found it interesting that the SMTP log doesn't show anything related to authentication being set. Compare the two messages (one was authenticated from Outlook, the other is mine that fails from the sql server sproc):
SMTP log, showing authenication:
11:15:17 [63.134.207.xx][15929998] rsp: 220 x40.webcontrolcenter.com
11:15:17 [63.134.207.xx][15929998] connected at 7/30/2007 11:15:17 AM
11:15:17 [63.134.207.xx][15929998] cmd: EHLO x5.webcontrolcenter.com
11:15:17 [63.134.207.xx][15929998] rsp: 250-MailA40 Hello [63.134.207.22] 250-SIZE 31457280 250-AUTH LOGIN CRAM-MD5 250 OK
11:15:17 [63.134.207.xx][15929998] cmd: MAIL FROM:<x@xinc.com>
11:15:17 [63.134.207.xx][15929998] rsp: 250 OK <x@xinc.com> Sender ok
11:15:17 [63.134.207.xx][15929998] cmd: RCPT TO:<me@idomain.net>
11:15:17 [63.134.207.xx][15929998] rsp: 250 OK <me@domain.net> Recipient ok
11:15:17 [63.134.207.xx][15929998] cmd: DATA
11:15:17 [63.134.207.xx][15929998] rsp: 354 Start mail input; end with <CRLF>.<CRLF>
11:15:17 [63.134.207.xx][15929998] rsp: 250 OK
11:15:17 [63.134.207.xx][15929998] Data transfer succeeded, writing mail to 48591169.eml
11:15:17 [63.134.207.xx][15929998] cmd: RSET
11:15:17 [63.134.207.xx][15929998] rsp: 250 OK
11:15:17 [63.134.207.xx][15929998] cmd: QUIT
11:15:17 [63.134.207.xx][15929998] rsp: 221 Service closing transmission channel
11:15:17 [63.134.207.xx][15929998] disconnected at 7/30/2007 11:15:17 AM
11:15:20 [146.82.220.xxx][30848077] cmd: QUIT
SMTP log without authentication:
11:17:08 [65.43.61.1xx][45537943] rsp: 220 maila40.webcontrolcenter.com
11:17:08 [65.43.61.1xx][45537943] connected at 7/30/2007 11:17:08 AM
11:17:08 [65.43.61.1xx][45537943] cmd: HELO chev0
11:17:08 [65.43.61.1xx][45537943] rsp: 250 MailA40 Hello [65.43.61.130]
11:17:08 [65.43.61.1xx][45537943] cmd: MAIL FROM: <x@x.net>
11:17:08 [65.43.61.1xx][45537943] rsp: 250 OK <x@x.net> Sender ok
11:17:08 [65.43.61.1xx][45537943] cmd: RCPT TO: <me@otherdomain.net>
11:17:08 [65.43.61.1xx][45537943] rsp: 550 Authentication is required for relay
11:17:09 [65.43.61.1xx][45537943] cmd: QUIT
11:17:09 [65.43.61.1xx][45537943] rsp: 221 Service closing transmission channel
11:17:09 [65.43.61.1xx][45537943] disconnected at 7/30/2007 11:17:09 AM
-
This issue sounds like it is on the SMTP side similar to what we had and my last company. We could sent any internal emails that we wanted from any of our servers pointing to the same STMP server. But, if we wanted to send external emails then we had to have it setup on the mail side for each server we wanted to send from. It sounds like your development server is setup but your production server is not.
If it is the same issue, it has nothing to do with your servers themselves. It is all on the mail side. Unfortunately, I don't remember the specific term of what is needed.
Hope something here can help you out or help someone else remember as well.
Chad
-
One thing to check - if your SMTP server is a windows machine (e.g. Exchange/IIS SMTP) be sure that basic authentication is enabled.
-
I did verify basic authentication is enabled. And I reset the password for the account I'm using for authentication to be sure I have it set correctly.
Here's what the support guy said:
If it requires a username/password field, make sure you're using a valid email address (usually the same as the FROM address) and password. This is all that would be required to send and authenticate through our servers using scripting.
-
Does your username match your email address? I've seen where the username (e.g. lastnameinitial) didn't match the email address (e.g. firstname.lastname@somewhere.com), when dealing with an exchange server it's looking for the user name not the email address...
-
Yes, I am sure the userid and password are correct. I'm using the same that I have personally in my Outlook settings. I've also tried using the administrator mail account (that I set up myself and reset the password to be sure it was right).
I think I'm switching mail servers soon, since I can't get this figured out. The support should be better to help me get thru this issue.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply