August 29, 2002 at 4:28 am
There is a difference of opinion between our server infrastructure team and myself (dba).
I am of the opinion that the way forwards is to use SQL mail, install a MAPI client (outlook), create operators and receive notifications via email.
Sounds quite easy does it not.
BUT (there always is one)
Our server infrastructure team are of the opinion that the server should not have a MAPI client installed as this is a security risk!
They think that I should use SMTP mail via a stored procedure, this means that I then have to create extensive dts packages to notify myself of failures e.t.c
I now have to give yet another convincing argument (this has been going on for over a year) as to why we cannot use SMTP mail.
Has anyone else come across this narrow-minded approach?
Is there another alternative solution?
Regards
Andoi
August 29, 2002 at 5:29 am
Using SMTP works fine however you still open yourself to security risks. Such as if not configured properly a person can use your machine to forward mail. Have them layout the security risks between the two in detail. Also MAPI client doesn't eman you will be receiving mail does it? If not thn most of the security risks they mention will not apply as this is a one way process for your needs.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
August 29, 2002 at 7:25 am
They mean SMTP mail where you are connecting to a separate SMTP server or SMTP mail where you need to install IIS and the SMTP portion on the same server as SQL?
If the latter, you inherit the inherent security risks with IIS. If the former, you then have to use CDO in most cases... which means you're creating and destroying objects and inherit the inherent stability risk that can accompany this if not done right. Otherwise you're relying on xp_cmdshell, and that IS a security risk.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
August 30, 2002 at 2:15 am
Thanks for the information.
So it I look at this again installing SMTP using CDONTS to send mail via another server is acceptable if configured properly.
However the use of SMTP mail will circumvent our email filtering system and the fact that many Trojan horses use their own SMTP engines to send malicious e-mails meant that it is probably best to minimize the use of SMTP.
So if we use outlook and configure the mailbox to only allow outgoing mails then this will follow the trusted route through our network to its destination. This will minimize the risks involved.
Then we can remove all unnecessary software from the server and harden the operating system & database.
I think that I now have a better grasp of the situation, thanks to a fresh outlook on the problem
Regards
Andoi
August 30, 2002 at 9:55 am
CDONTS will let you point at an SMTP server. If your admins want you to use SMTP, I assume they already have one. It should be in the critical path for virus checks and the like. However, you're not retrieving mail through SMTP, so you're not going to pull anything down with SQL Server (most likely you'd be using POP3, but since you aren't using SQL Mail...) so that shouldn't be a concern on your end.
It should be a concern on their end if they are giving you a path which circumvents the normal channels, but then, that's their headache because they created. I'm not speaking as a DBA here, but as a infrastructure team lead myself (my current role at my company). They should give you a path which forces through at least the virus checks.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
September 2, 2002 at 8:00 am
Correct if you lock down IIS and only run its SMTP service its pretty safe and a custom procedure that checks if an allowed account is executing the procedure adds another layer of sanity.(you can always encrypt the proc) The procedure can be put in place and called from any where and be utilized to send alerts. For daily queries such as failed jobs I write compiled vb / asp code to be called from procedures that populate a table and query a static table and send results via email. You can then truncate the tables data or just use a temp table. Microsoft provides (finally) a set of Q articles for the public for utilizing the CDONTS AND CDOSYS com object for sending mail, pages and alerts. This solution works good for me due to no mail clients on production machines
September 9, 2002 at 12:53 am
Does this mean that because of security risks one should use SQL mail instead of SMTP
September 9, 2002 at 1:44 am
It does appear that there are security risks associated with both methods of sending mail from SQL servers. However it is up to the Individual DBA or others which method is the most cost effective in terms of working practices and configuration.
I have looked at the method described by Shawn (above) and from my investigation it does appear that SQL Mail does not use the MAPI Client but accesses the MAPI DLL's and uses the profile set up by the MAPI Client to send the mail.
I have tested this by removing outlook from one of my test boxes and the SQL Mail still works (as the profile seems to be cached on startup of SQL Agent) however on restarting SQL Server the SQL Mail Profile disappears.
I am currently having a few minor problems with other things but will post more information when I can get back to it .
Regards
Andy
September 9, 2002 at 11:42 am
Outlook or a MAPI client is not required for SQL Mail, so long as the MAPI .dlls are there. What is[/is] required and why the MAPI client is usually installed is the ability to create the MAPI profile. However, there are ways to create the profile without using Outlook.
Here's how to create the MAPI profile without installing Outlook:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306962
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply