best way to send email from SQL Server 2000?
-
Hello All,
I just took over administration of a SQL Server 2000 box and am trying to tidy up the security a bit on the machine.
The development team is currently using CDO to send emails from SQL Server (see: http://support.microsoft.com/default.aspx?scid=kb;en-us;312839&sd=tech). My problem with this solution is that it uses the sp_OA OLE Automation stored procedures which require sysadmin privileges to work...which means I need to grant sysadmin to every application/person that needs to send emails!
It seems that there has to be a more secure way to handle this.
I looked into using SQL Mail (which I have no experience with) as an alternative but have run into all sorts of issues w/ the configuration. We use Lotus Notes in my shop and it is tough (almost impossible) to get SQL Server to play nice w/ Lotus Notes.
I've read a couple of articles suggesting that SQL Mail occasionally hangs when its not using an Exchange Server for email. Is it worth the trouble setting up SQL Mail w/ Lotus Notes? Are there any other alternatives to look at?
I'd appreciate any feedback!
Thanks!
John
-
Go to http://www.sqldev.net/xp/xpsmtp.htm for a SQL Mail alternative. I am using SQL Mail on several servers. I haven't had problems getting it configured -- but then we are on Exchange. But, I find that SQL Mail is not always reliable. If the Exchange server goes down for maintenance, I sometimes have to reboot the SQL Server to get SQL Mail working again.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Hello,
Yes, there is a way. We had to switch from Exchange to Notes and we lost SQL Mail that requires Exchange server on the network. (By the way, if somebody know how to configure SQL Mail without Exchange - please, comment) So I am currently using ASP / VB scripts to send SMTP mail. It is just several lines of code. You can execute a job that runs the OS script and executes this ASP or VB file from SQL Server as a job or you can just use xp_cmdshell. You would like to use CDONTS.NewMail object if SMTP server is running on the server, or a combination of CDO.Message and CDO.Configuration objects if you are going to use external SMTP server. Here is the example.Let me know if you need the one with configuration
<%
Option Explicit
Dim objNewMail
Set objNewMail = Server.CreateObject("CDONTS.NewMail")
objNewMail.From = "yelena_varshal@mycompany.com"
objNewMail.To = "yelena_varshal@mycompany.com"
objNewMail.Subject = "Subject of the email"
objNewMail.Body = "Body of the email."
objNewMail.Send
Set objNewMail = Nothing
Response.Write "Email has been sent"
%>
Regards,Yelena Varsha
-
I tend to use SQLMail with Exchange in most places, but there are times when I've needed to send mail. I've use the above techniques as well as ASPMail with DTS (similar to above) to get mail off a SQL server. I haven't use the XPSMTP, but it should owrk the same.
One note, usually the SMTP server is the exchange server for me, so if that's down, then mail is down anyway. You can use the internal SMTP server, but I've had the same issues. That server is down or some other when Exchange isn't and you get caught.
-
We found xp_smtp_sendmail much easier (download here: http://sqldev.net/xp/xpsmtp.htm) especially since we didn't have to install an exchange server or have access on one to get up and running.
-
Thanks for all of your feedback!
The installation instructions on the xp_smtp_sendmail website read:
"4. Grant rights to the correct set of users using OSQL or SQL Query Analyzer by executing:grant execute on xp_smtp_sendmail to public
By default only the member of the sysadmin role have execution rights on the XP after it is being registered"
I'm a little confused regarding the required permissions to execute it...Once installed, does this mean only sysadmins can use xp_smtp_sendmail or anyone w/ public access?
-
By default only sysadmins have permission to use it. But if you run this "grant execute on xp_smtp_sendmail to public" then everyone will have permission to use it. That is probably what you want.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Were you using sp_send_cdontsmail? If so, you could make copies of it in any databases where it is needed?
sp_send_cdontsmail is found in the Master db which requires sysadmin permission however, copies of it will work from any of your databases - no need to grant sysadmin permissions.
I do not have sysadmin permissions so this is what I do in databases which I develop:
CREATE PROCEDURE [dbo].[sp_FLEETMAIL]
-- copy of sp_send__cdontsmail
@From varchar(100),
@To varchar(100),
@Subject varchar(100),
@Body varchar(4000),
@cc varchar(100) = null,
@BCC varchar(100) = null,
@ATTACH varchar(100) = null
AS
Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @cc
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID
GO
-
Hi All,
I ended up creating a new role in master and granted EXEC for all of the sp_OA procs to the new role. Now if someone needs to run sp_send_cdontsmail(regardless of what db it is setup in) all I have to do is add them to this new role and it should work...no sysadmin is needed anymore.
Ian- you probably have a similar security setup...thanks for the tip!
I didn't think this would work because BOL says that you need to be a member of sysdmin to use the sp_OA procs but it seems to work just fine!
Thanks for your help everyone!
John
PS - I'd still be interested if someone knows how to setup SQL Mail w/ Lotus Notes...if anyone knows a way to do this...please post! Thanks!
-
You cannot because it is not a MAPI client.
-
Glad it (CDO) worked for you too.
My company (a UK/International Logistics firm) also runs Lotus Notes however installing Notes Clients on SQL Servers was seen as less efficient (than CDO) and more costly to administer.
If you seek to use Domino userID/name/address resolution you might consider holding a DTS linked copy of the table (probably a flat file) on SQL Server.
You could then create an (easy to use) intermediary procedure which emulates Notes Client address resolution.
For good CDO presentation (another issue) I have intermediary procedures to paragraph CDO mail using "good old" Char(13). With the right control chars you could offer comprehensive formatting I.e. bold, italic etc.
For dynamic spreadsheet attachments there was an excellent article in SQL Server Central however I use a .NET interface for this.
-
does xp_sendmail require admin privaledges?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_6hbg.asp
(watch for wrapping)
-
Do you have an example of your script that is using an external SMTP server to send the message and not an SMTP server that's on the SQL server?
thanks!
Mike B
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply