May 11, 2011 at 9:28 am
Hello,
We have a message center application. Basically, all different applications, events, reports that need to send email are enetred into the main message log table. At regular interval we execute the sp_db_sendmail procedure to send emails out (or queue them.). The daily load is about 100 to 200 emails.
We have sql server 2005 Enterprise version SP4 and we will be moving to sql server 2008 R2 enterprise soon.
Development group has proposed to start sending emails through common dot net assembly for the following reasons:
From dev team:
1.Flexibility where we can send the email in different formatted outputs
2.Easily manageable code and reusable
3.We can attach different files
4.Easily configurable
5.We can log all messages in database / files
6.More secured (no need to open ports to send email)
7.Leave database alone only for processing and managing data, queries, stored procedures. Why to add overhead of additional task of sending emails which can be done by other application servers. Plus it poses a huge security risks to the database.
Honestly, as a DBA, I know that any procedure that interact with OS and operates outside sql server poses a scurity risk. But I never knew that sp_db_sendmail is a huge security risk and should be used as a last resource. Is it really a bad thing?
We have so much work load and I am biased on paying attention on the new things to get them done right way. If possible I do not wat to mess with perfectly running message center application which does not even make it to the list of top 100 resource consuming process. But at the same time I do not want to follow 'bad' practice as well.
I am trying to decide to go .NET way or keep database mail. Regardless of the application emails, I still need to get all the sql server alerts emails from sql server. So, I don't think I can disable database mail anyway.
Please let me know if I am missing something here or should I switch to common dot net assembly way of sending reports.
Thanks,
Shaili
May 11, 2011 at 10:50 am
I would use msdb.dbo.sp_send_dbmail for mail within SQL Server. I've never heard of this being a security risk. Perhaps you dev team would like to clarify this.
Francis
May 11, 2011 at 11:04 am
I have used DB Mail for years now and I see the importance of DB mail. It is not to replace other notification services.
If you have MOM, then its a different story. But DB Mail is for DB related purposes and if configured correctly, it is safe too.
Also, I do not agree with the points you listed from your Dev Team.
May 11, 2011 at 11:07 am
_UB (5/11/2011)
But DB Mail is for DB related purposes and if configured correctly, it is safe too.Also, I do not agree with the points you listed from your Dev Team.
This is the key part.
May 11, 2011 at 11:09 am
I see no problem with DB Mail. It uses an SMTP server that you can specify. You can secure that smtp and specify rules there.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 11, 2011 at 11:10 am
The only one of those that's actually correct is that you do have to have a port open to allow SMTP to send the mail. Otherwise, every "feature" they are listing is already built into DBMail.
Output formats? Yep, there's a parameter for that.
Reusable code? You don't even have to write it, just call sp_send_dbmail. Totally reusable. Totally modular.
Attachments? Yep, you can do that too. Can even be dynamic.
Configurable? Yep.
Log all messages? Already does that.
Need to open port? You will need to do this. But you'll have to have an SMTP port open regardless, it's just a question of which server has that open. Only an issue if the people administering your firewall are incompetent.
Database overhead? Are they really asserting that their application won't run any queries on the database server in order to send e-mail based on database content? Their application will almost certainly add more data processing overhead to the same hardware as sp_send_dbmail uses.
The old (SQL 2000) version of e-mail was a horrible problem. Most likely, they didn't get the memo that it's been fixed in 2005 and replaced with DBMail.
If their application will honestly provide a value to the business beyond what DBMail can provide, which is possible, then they should use it. First though, they should provide an ROI analysis of the development time needed to replace an already-working system. They can then be given the opportunity to document each of the points, including validation of the shortcomings of DBMail, and proof that their new system won't introduce anything even worse. That would make a valid ROI analysis, and should really be the basis of a business decision.
Most often, in my experience, developers who want to avoid DBMail (a) don't actually know how it works or what it actually does, and (B) simply think it would be cool to build something like that, because it's easy to do and gets a simple feather in their caps. I'm not saying that's how it is with your team, I'm just saying I've seen that three for three when it's come up for me before.
If you were using SQLMail (from SQL 2000), then I'd completely agree with them that it needs to be replaced, and that it should be done ASAP. There are reasons Microsoft dumped that piece of junk and replaced it with DBMail (SQL 2005 and 2008).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 11, 2011 at 2:59 pm
Thanks all for the reply.
Our Dev guys are referring to this document from Microsoft:
But I agree. Our smtp server is secured with firewall so why not use the database where information is available already.
Also, I need to have database mail configured to send out database email alerts also.
Shaili
May 11, 2011 at 5:39 pm
shaili (5/11/2011)
This article has a smal section in Lockdown of System Stored Procedures (pagers 13-14) that raises sp_send_dbmail as a system stored proc, and therefore should be disabled.
This is a very generic recommendation and the article is generaly a bit vauge and confusing around using DB Mail.
If you check this http://msdn.microsoft.com/en-us/library/ms190307.aspx?ppud=4 you will see that permissions on the dbmail stored proc default to members of the DatabaseMailUser database role in the msdb database. Any SQL System Admin can obviously execute the proc. I wouldn't consider this a risk since if your sa accounts are compromised you have far bigger issues than bering able to execute the sp_send_dbmail stored proc.
Unless the proposed app adds more value at lower cost (Development, time, money, resources, maintenance) I would stick to the current system.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 12, 2011 at 3:17 am
I totally agree with Leo . If you see from a bigger perspective everything we do on a database has a risk involved with it . So this is where security comes in, the more security you will plug-in the more secure you will feel in terms of managing your database enviornment.
There are n-number of security best practices but in real world we can't implement them all.
I had a similar issue with Development Team in my enviornment, but i opposed them because i have seen them using unsafe assemblies to access resources outside the scope of sql server and even beyond server also over the network which i suspect is more prone to security hazard.
DBMAIL is a very safe way of sending mails through your database server. If you see the requirements it require only the smtp port which can always be secured by firewalls and other security practices, except this it require you to have permissions to execute those system stored procedures like sp_send_dbmail which in case you can manage.
So, using DBMAIL to me looks quite a trivial option.
Sachin
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply