potential problems with sp_OACreate permissions
-
Can someone please tell me what, if any, potential problems I might run into if I grant execute permissions on the OA stored procedures, such as sp_OACreate, sp_OAGetProperty, sp_OAMethod, sp_GetErrorInfo, and sp_OADestroy? We need to do this so that an email can be sent using sp_sendmail. Are there any major security risks doing this?
-
From the SQL Server Standard magazine:
"Generally the use of the sp_OA stored procedures are avoided whenever possible because their improper use can lead to access violations or memory leaks or even security issues.
XPSMTP provides SMTP support through extended stored procedures. You can find XPSMTP at SQLDev.net (http://sqldev.net/xp/xpsmtp.htm)."
From BOL:
"Permissions
Only members of the sysadmin fixed server role can execute sp_OACreate"
Take Care
Rui Lufinha
-
I can tell you exactly what security problems you could encounter:
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @toNewObject OUT
Use your imagination for ideas on how anyone who gets access to the oa procedures could use this line.
When we HAVE to use them, we generally wrap them with a sp in master and then do security checks on login name/etc. before calling the oa procedures. I personally do like any of the security implications of the oa procedures, and will go to some lengths to avoid their use.
-
I also experienced some memory leak with using QA_create
-
Thank you all for the replies. Just one follow-up question. We are trying to use this to send emails. Our network people have a port block, so the developers can't send any emails from the web server that is outside the firewall. The developer is trying to send a database request back to the SQL Server box and use the OACreate to send the email since it sits within the firewall and can talk to the exchange server. Does anyone see another way of doing this? Thanks in advance for any help.
-
we use an extended sproc to send SMTP mail, as someone else suggested above.
The one we use is xp_smtp_sendmail - do a search for that on google - very simple to use, simply requires an SMTP mail server that allows relaying.
Once installed, it is simply a matter of going
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = @From,
@FROM_NAME = @FromName,
@TO = @To,
@replyto = @ReplyTo,
@CC = @cc,
@priority = @Priority,
@subject = @Subject,
@message = @Message,
@type = 'text/html',
@server = @server
And you're away.
Regards
Al
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply