Email From a CLR Stored Proc - SMTPPermission

I am trying to send email from a CLR Stored proc.

I get the following error.

A .NET Framework error occurred during execution of user defined routine or aggregate 'HelloWorld':
System.Security.SecurityException:

Request for the permission of type 'System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.Net.Mail.SmtpClient.Initialize()
at System.Net.Mail.SmtpClient..ctor(String host)
at StoredProcedures.HelloWorld()
.

How do I handle the SmtpPermission?

Here is the stored proc

public static void HelloWorld()
{
MailMessage mail = new MailMessage();

//set the addresses
mail.From = new MailAddress("test@.test.EDU");
mail.To.Add("test@.test.EDU");

//set the content
mail.Subject = "Hello World";
mail.Body = "Did you get this? I am emailing from a CLR stored proc!";

//send the message
SmtpClient smtp = new SmtpClient("127.0.0.1");
smtp.Send(mail);

}

SQL Server comes with when last I counted three different mail internal so you are reinventing the wheel by using System.Net. Run a search for SQL Server mail and SQL Server Agent mail in the BOL (books online) because I think there are known issues with the IMAPI mail. SQL Server Agent mail can also be used to send pages. Hope this helps.|||

Have the same problen - and the above RUDE comment isnt any help!

Instantiating SmtpClient gives -exception of type 'System.Security.SecurityException'

Hmmm - if it would work we've got a lot of objects we could put to work without reenventing new methods just to work in a CLR proc!

What's necessary to get SmtpClient to work in a CLR proc?

|||

OK - there's 2 or 3 ways to get the job done...

Easiest( although not MSDN recommended just to jet a CLR proc to run) is to set the permission level to External_Access...

SQL Server Host Policy Level Permission Sets
The set of code access security permissions granted to assemblies by the SQL Server host policy level is determined by the permission set specified when creating the assembly. There are three permission sets:SAFE,EXTERNAL_ACCESS andUNSAFE.

The permision level is set on the properties pages of the CLR project , database tab - set Permission Level-External, set Aassembly Owner-dbo, and run tsql 'ALTERDATABASE DataBaseName SET TRUSTWORTHYON'
This will get the job DONE! - and the SmtpClient wiill work ok...

Then do it right and Sign the Assenbly with a Strong name Key file...
Read MSDN

Creating an Assembly

Discusses creating SAFE, EXTERNAL_ACCESS, and UNSAFE CLR assemblies in SQL Server

That's it...

RLewis - MCSD

(and there sure is a lot of NO HELP answers goin around...)

|||

Brilliant!!!!!

Solution 2 worked like a charm. El mucho gracias!!!!