xp_cmdshell permissions issues

[bouwob]

I am working on an email sender. The problem lies with the fact that it has to be limited to 1000 message every 10 minutes. Thinking about how to do this I decided I would create a SQL job that would call the spammer when needed.

Here is the SQL
declare @users table
(
messageid int,
userid varchar(7)
)

declare @userIDs varchar(max)

insert into @users select top 1000 * from email_users

DECLARE foreach Cursor FOR --set up cursor. This will run through each unique computer
Select distinct messageid From @users

Open foreach --open cursor
--declare variables to hold information form @rows
declare @messageid int

Fetch NEXT FROM foreach INTO @messageid --fetch first row
While (@@FETCH_STATUS = 0)
BEGIN
DECLARE foreachDetail Cursor FOR --run through each of the details records from last query
Select userid From @users where messageid = @messageid

Open foreachDetail --open cursor
--declare variables to hold information form @details
declare @userid varchar(7)
set @userids = ''

Fetch NEXT FROM foreachDetail INTO @userid --fetch first row
While (@@FETCH_STATUS = 0)
BEGIN
set @userIDs = @userIDs + @userid + ','
Fetch NEXT FROM foreachDetail INTO @userid --fetch next row
end
close foreachDetail --close cursor
DEALLOCATE foreachDetail --eliminate used memory
--declare message veriables
declare @message varchar(max)
declare @fromWho varchar(100)
declare @subject varchar(250)
select @message=message, @fromWho=fromWho, @subject = subject from email_message where messageid = @messageid

DECLARE @result int

EXEC @result = xp_cmdshell '\\server\spam\spammerPlus.exe "@messageid" "@message" "" "@fromWho" "" "@subject" "@userIDs"'
if (@result = 0)
begin
delete from email_users where messageid = @messageid and userid in (select value from dbo.split(@userIDs, ','))
end
Fetch NEXT FROM foreach INTO @messageid --fetch next row
end
close foreach --close cursor
DEALLOCATE foreach --eliminate used memory
END


\\server\spam\spammerPlus.exe code looks like this

try
{
string messageid = args[0];
string messageBody = args[1];
string owner = args[2];
string fromWho = args[3];
string html = args[4];
string subject = args[5];
string to = args[6];

SmtpClient emailClient = new SmtpClient("smtp-gw1.somewhere.com");
MailMessage message = new MailMessage();
message.IsBodyHtml = true;
string[] froms = fromWho.Split(',');
message.From = new MailAddress(froms[0], froms[1]);
foreach (string tmpTo in to.Split(','))
{
message.Bcc.Add(new MailAddress(tmpTo + "@email.somewhere.com"));
}
message.Subject = subject;
message.Body = messageBody + "<span style=\"color:white;\">" + messageid + "</span>";
emailClient.Send(message);
}
catch (Exception ex)
{
SmtpClient emailClient = new SmtpClient("smtp-gw1.somewhere.com");
MailMessage message = new MailMessage();
message.From = new MailAddress("a@somewhere.com", "The Spammer");
message.To.Add(new MailAddress("a@somewhere.com"));
message.Subject = "Spammer Error";
string body = ex.ToString();
foreach (string tmp in args)
{
body += tmp;
}
message.Body = body;
emailClient.Send(message);
}


Unfortunatly when I run the SQL, I always get an error return of

NULL
Unhandled Exception: System.Security.SecurityException: Request for the permission of type 'System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
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 spammerPlus.Program.Main(String[] args)
The action that failed was:
Demand
The type of the first permission that failed was:
System.Net.Mail.SmtpPermission
The first permission that failed was:
<IPermission class="System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Access="Connect"/>
NULL
The demand was for:
<IPermission class="System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Access="Connect"/>
NULL
The granted set of the failing assembly was:
<PermissionSet class="System.Security.PermissionSet"
version="1">
<IPermission class="System.Security.Permissions.EnvironmentPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Read="USERNAME"/>
<IPermission class="System.Security.Permissions.FileDialogPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Unrestricted="true"/>
<IPermission class="System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Read="\\server\SPAM\"
PathDiscovery="\\server\SPAM\"/>
<IPermission class="System.Security.Permissions.IsolatedStorageFilePermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Allowed="AssemblyIsolationByUser"
UserQuota="9223372036854775807"
Expiry="9223372036854775807"
Permanent="True"/>
<IPermission class="System.Security.Permissions.ReflectionPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Flags="ReflectionEmit"/>
<IPermission class="System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Flags="Assertion, Execution, BindingRedirects"/>
<IPermission class="System.Security.Permissions.UIPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Unrestricted="true"/>
<IPermission class="System.Security.Permissions.UrlIdentityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Url="file://server/spam/spammerPlus.exe"/>
<IPermission class="System.Security.Permissions.ZoneIdentityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Zone="Intranet"/>
<IPermission class="System.Net.DnsPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Unrestricted="true"/>
<IPermission class="System.Drawing.Printing.PrintingPermission, System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
version="1"
Level="DefaultPrinting"/>
</PermissionSet>
NULL
The assembly or AppDomain that failed was:
spammerPlus, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null
The method that caused the failure was:
Void Main(System.String[])
The Zone of the assembly that failed was:
Intranet
The Url of the assembly that failed was:
file://server/spam/spammerPlus.exe
NULL


I have been running around in circles for the last 2 days, trying to make this work. The code seems 100% (I can run locally with no problems). I think I am missing a permissions setting somewhere. Have you seen this error before and if so, what setting changes have you made to resolve it?

 

[philhege]

A job runs in the context of the SQL Agent account. Does this account have rights to the process?

Permissions aside, I'd re-architect this totally within .NET and call the executable with a scheduled task. The SQL piece of your application is participating in the wrong application layer.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.