• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 762
  • Last Modified:

Sending Email Through SQL Server

I am trying to use the example at http://support.microsoft.com/default.aspx?scid=kb;en-us;312839 to send email from SQL Server (2000) using CDOSYS. I have no idea what CDOSYS is, but in the example, it says to replace 'cdoSMTPServerName' with the name or IP address of my smtp server. I did that and received an error message that 'The server rejected the sender address. The server response was: 530 authentication required - for help go to...'

Is there a way that I can add the authentication required? In my email account settings, I have checked that the server requires authentication and to use the same settings as my incoming mail server if that helps.

0
dbbishop
Asked:
dbbishop
  • 6
  • 5
  • 2
  • +1
1 Solution
 
nito8300Commented:
Hi there. give this one a try, really easy to use: http://www.sqldev.net/xp/sp_smtp_sendmail.htm 

Hope it helps.
0
 
dbbishopAuthor Commented:
nito8300: I installed it and ran a test. It returs 1, indicating an error...
0
 
Anthony PerkinsCommented:
>>indicating an error...<<
And the error is ...

Hint: When executing this stored procedure from SQL Query Analyzer, you can see the error message (if any) in the Messages tab.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
dbbishopAuthor Commented:
Duhh. Sorry, temporary brain-freeze. Same message as CDOSYS:

Error: sending message
Server response: 530 authentication required - for help go to http://help.yahoo.com/help/us/sbc/dsl/mail/pop/pop-11.html
0
 
Anthony PerkinsCommented:
You will not be able to use XPSMTP for this purpose, here is one reason (see Authentication support):

http://www.sqldev.net/xp/xpsmtp.htm#Planned_functionality_enhancements

And you may not be able to use CDOSYS or any other interface without getting the following error message:
Server response: 550 5.7.1 Unable to relay for xyz@domain.com

See here for an explanation (under the question on relaying mail):
http://www.sqldev.net/xp/xpsmtp.htm#Troubleshooting
0
 
TAB8Commented:
Is mail relay enabled on your email server ...   ?
0
 
dbbishopAuthor Commented:
I don't know. How would I find out. I have AT&T DSL service. the mail server is through yahoo.com.
0
 
Anthony PerkinsCommented:
>>How would I find out.<<
See here:
http://help.sbcglobal.net/article.php?item=323
0
 
dbbishopAuthor Commented:
Well, everything looks fine. I have Outlook up and running. I have the right account information entered for the email account. I am using my AT&T (sbcglobal.net) userid as the FROM: address in SQL. The error does not say anything about relaying, it says authentication is required. The error refers to this location (http://help.sbcglobal.net/article.php?item=6239). It deals with authentication. I do not know what needs to be done to support authentication from within SQL Server. As I stated earlier, in the account setup, there is a checkbox that indicates my server requires authentication and to use the same settings as my incoming mail server. But I do not know what is happening there 'behind the scenes' and if it is something that can be mimicked within xpsmtp. As far as I know, my isp is yahoo.com so there is no one else I can contact.
0
 
Anthony PerkinsCommented:
Ok.  Let's try again:  Since you require that your SMTP mail be authenticated "You will not be able to use XPSMTP for this purpose"

>>The error does not say anything about relaying, it says authentication is required.<<
My point was that, if and when you find a tool that you can use from SQL Server that offers authentication, than your next roadblock to overcome MAY be relaying mail.
0
 
dbbishopAuthor Commented:
I would like to readdress this topic. All the testing I was doing and comments I was making was on my home system. I have had to drop this for awhile but am back to playing with it again, now at work. When I attempt to run xp_sendmail, I get the message:

Server: Msg 17985, Level 16, State 1, Line 0
xp_sendmail: Procedure expects parameter @user, which was not supplied.

I was unable to find a @user parameter in the documentation. The query I was running was:

EXEC master.dbo.xp_sendmail @recipients=N'xxxx@someone.com;dbishop@myplace.com',
     @message=N'The master database is full.',
     @copy_recipients=N'whoever@nowhere.com',
     @subject=N'Master database status' ;

I did read that in order for xp_sendmail to run, the sql mail session must be started. I went ahead and ran xp_startmail with userid and password. I get the following message:

Server: Msg 18035, Level 16, State 1, Line 0
xp_startmail: Your SQL Server Service is running under the local system account. You need to change your SQL Server Service account ro run under a domain account in order to complete this operation

I am not a DBA so not really certain what this is saying, but before I try to excalate this up the ladder and see if the necessary changes can be made to run under a domain account, is this something that can be done without much trouble and without affecting the current operation of our server? If it cannot be done easily, am I just out of luck completely with being able to send mail within T-SQL?
0
 
Anthony PerkinsCommented:
I am afraid I have no idea.  I have never used xp_sendmail as it requires a MAPI client, such as Outlook installed on the server and that is very rarely a good idea, especially when there is such a simple alternative (XPSMTP) using the SMTP protocol.

In any case, good luck with SQL Mail.
0
 
TAB8Commented:
Just download and install xp_smtp_sendmail from SQLDev.Net. ... Information about how to install and use xp_smtp_sendmail can be found at the SQLDev.net .. it doesnt require a mapi profile and is very robust and reliable "and free"
0
 
Anthony PerkinsCommented:
TAB8,

I suspect if you re-read the thread you will find that has already been suggested more than once.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now