September 6, 2007 at 2:55 pm
do we have replacement for xp_sendmail to send .NET code created pdf attachments to the recepients? as i donot want to give admin previlages to the developer.
This should be scheduled as a job on daily basis.
Any reply is greatly appreciated.
September 7, 2007 at 7:24 am
Well - you CAN assign him specific rights to execute xp_sendmail without admin privs... More maintenance to remember on the security side of things.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 7, 2007 at 8:25 am
Execute permissions should be granted in master database which is security concern in production environment.
We would like to avoid giving access to master database.
September 7, 2007 at 9:25 am
Well - then build a "wrapper" stored proc, which would invoke xp_sendmail based on the contents of the table. Disallow said developer from executing stored proc. Run stored proc from Scheduled job under credentials that ARE allowed to run it.
Otherwise - build something using CLR integration to instantiate MAPI objects directly.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 7, 2007 at 11:24 am
Can "wrapper" proc be created in user database? or else this should be in master database?
September 7, 2007 at 11:36 am
create it in user DB.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 2, 2007 at 7:58 am
Are there any security vulnerabilities using xp_sendmail? as we will be sending emails to outside users regularly.
But as one of you mentioned above,we will create a wrapper stored proc in a userDB and will run as a SQL job periodically.
October 2, 2007 at 8:07 am
It could be exploited to send mails anywhere, which would be coming from your server/company. However it really depends on you properly checking which mails are going out. Set up some sort of internal validation to be sure these mails should go.
October 2, 2007 at 9:00 am
There's also an additional concern with xp_sendmail, which is that it is a synchronous operation. Meaning - because it is MAPI-based, it expects synchronous responsiveness from the account you're dealing with; it operates "in scope" so a bad stumble on the mail server side can cause this under the right circumstances to lock (and keep locked) all sorts of fun resources on the SQL server. If your mail server is anything less than perfect, this can lead to problems on the SQL server side.
When those types of issues happened to us - the connections trying to process said mail were impervious to KILL (they didn't much care about exposure to sunlight, silver or holy water either). Only thing we could find to kill that was a failover of the cluster.
There's an alternative - which helps with the performance issue above. It's called sp_send_cdosys_mail, which can be set up if you install the Collaborative Data Objects feature of your OS. CDO runs out of process so doesn't seem to have such difficulties with breaking the conections/restarting, and didn't seem to jam up our server nearly as much as xp_sendmail.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 2, 2007 at 2:49 pm
I didn't understand about synchronous responsiveness you are talking about? Is there a scenerio which can explain that?
October 2, 2007 at 3:01 pm
meaning- if your SQL Server happen to be paired up with a busy Exchange server for sending, and Exchange goes off and starts "plowing" because of large emails/too many emails, this can sometimes hang the connection trying to feed emails from SQL into Exchange.
A MAPI connection is a much more "hands on" connection than a "traditional" SMTP-type connection, so there are a lot more demands on the connection to be completely synchronous (meaning in this case, that your Exchange server is at all times ready to "take" the incoming mail). It tries to maintain a continuous connection to the mail account, and doesn't seem to reconnect it if there is a temporary outage (on the mail side), so the connection just sits there and starts choking the resources on the SQL, since nothing is processing. Kill, rollback, etc... didnt' seem to do anything for it - just kill the instance or fail over to the other node, both of which cause a smallish outage while SQL "recovers" on the other node.
On the other hand - the CDOsys seems to have no issue with being killed (yes - you obviously lose what was in the KILLed process, but it doesn't hang everything else queued up behind it). It also just seemed to not place so many high expectations on the constancy of the connection to the mail server, so in general - it behaved better for us.
Of course - the server I'm talking about generated a LOT of e-mail, so it may not be something you see if you only send stuff out on occasion.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 3, 2007 at 7:30 am
My personal favoite is xpsmtp
http://www.sqldev.net/xp/xpsmtp.htm
No, MAPI, no Exchange requirements, no funky stuff, just plain simple SMTP 😀
/Kenneth
October 4, 2007 at 7:28 pm
If we are going to use xp_sendmail, then there is no other way than using outlook 2003 client, right? Is ther any other client than this?
We have corporate exchange server, so can we use outlook 2003 client to connect to that corporate exchange server?
or Is there any other best way for using with xp_sendmail?
October 5, 2007 at 7:51 am
It's not so much setting up Outlook as it is setting up the MAPI mail account on the machine (in the Control Panel, Mail area).
In other words - I don't think actually setting up Outlook on the machine is required. Just set up the mail account in the control panel.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 5, 2007 at 10:08 am
I donot see any Mail Area in the control panel?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply