April 29, 2010 at 2:14 pm
I have a stored procedure that is getting called from a .NET web application to send an email. As long as I'm not attaching any files, the procedure works great. When I try to attach a file, it works when I run it from SSMS. However, when we run it within the application, we get an error which appears to be the user context does not have rights to the file system.
I have tried impersonating myself in the sp since I have sysadmin rights as well as rights to the file location. I gave the sql user rights to impersonate me and I still get errors. From what I have read, it appears a sql user cannot impersonate a domain user. At the same time, the sql user does not have, nor can it be granted file system access.
My question is: How do I send an attachment in a sp?
The error coming from the .NET application is: "The client connection security context could not be impersonated. Attaching files require an integrated client login." It tells me the error is a SqlException.
Here is my code:
ALTER PROCEDURE sp_sendEmployeeConfirmation
@EmployeeID VARCHAR(12),
@BenefitsYear VARCHAR(10),
@Email VARCHAR(64) = ''
AS
DECLARE @HTMLMessage NVARCHAR(MAX);
DECLARE @Subject NVARCHAR(255);
DECLARE @RowCounter INT = 1;
DECLARE @TotalBen INT;
DECLARE @TotalDeduct DECIMAL(10,2);
DECLARE @SSN VARCHAR(12);
DECLARE @FileAttach NVARCHAR(MAX);
DECLARE @RandomDir VARCHAR(8);
DECLARE @Command VARCHAR(256);
DECLARE @BenList TABLE(RowNumber INT IDENTITY (1, 1),
BENTXT VARCHAR(50),
BENCOV VARCHAR(50),
BENBTX VARCHAR(50),
BENMPD DECIMAL(10,2),
BENLCN VARCHAR(50));
SELECT @SSN = PER_SSN
FROM TEAMS.dbo.PERSON
WHERE (PER_ID = @EmployeeID);
INSERT INTO @BenList (BENTXT,
BENCOV,
BENBTX,
BENMPD,
BENLCN)
SELECT BENTXT,
BENCOV,
BENBTX,
CONVERT(DECIMAL(10,2), BENMPD) AS BENMPD,
BENLCN
FROM Benefits
WHERE (BENSSN = @SSN)
AND (BenefitsYear = @BenefitsYear)
ORDER BY BENCTR;
SELECT @TotalDeduct = SUM(BENMPD)
FROM @BenList;
SELECT @RandomDir = UniversalData.dbo.fn_GenerateRandomPassword(8,1);
SET @Command = 'md D:\TempFiles\' + @RandomDir + '\';
EXECUTE AS LOGIN = 'PINNACLE\SQLAdmin';
EXEC master.dbo.xp_cmdshell @Command;
REVERT;
SET @Command = 'copy \\SQLSand\WebReports\Benefits\' + @BenefitsYear + '\' + @SSN +
'.pdf D:\TempFiles\' + @RandomDir + '\';
EXECUTE AS LOGIN = 'PINNACLE\SQLAdmin';
EXEC master.dbo.xp_cmdshell @Command;
REVERT;
IF @Email <> ''
BEGIN
SET @HTMLMessage =
N'<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">' +
CHAR(13) + CHAR(10) +
N'<html>' + CHAR(13) + CHAR(10) +
N'<head>' + CHAR(13) + CHAR(10) +
N'<title>Benefits Confirmation Message</title>' + CHAR(13) + CHAR(10) +
N'<link href="http://hr.pinnacle.inside.pisd/StyleSheet.css" rel="stylesheet" ' +
N'type="text/css" />' + CHAR(13) + CHAR(10) +
N'<meta http-equiv=Content-Type content="text/html; charset=iso-8859-1">' +
CHAR(13) + CHAR(10) +
N'</head>' + CHAR(13) + CHAR(10) +
N'<body>' + CHAR(13) + CHAR(10) +
N'<div class="banner">' + CHAR(13) + CHAR(10) +
N'<table class="banner" width="100%" border="0" cellpadding="0" cellspacing="0">' +
CHAR(13) + CHAR(10) +
N'<tr><td align="left" valign="top">' + CHAR(13) + CHAR(10) +
N'<img alt="Benefits" src="http://hr.pinnacle.inside.pisd/Images/header_02.gif" />' +
CHAR(13) + CHAR(10) +
N'</td><td align="right"><h2>Benefits Confirmation</h2>' + CHAR(13) + CHAR(10) +
N'</td></tr></table>
</div>
' + CHAR(13) + CHAR(10) +
N'We have received your confirmation for the benefits selections you made for the ' +
CAST(@BenefitsYear AS NVARCHAR(MAX)) +
N' plan year. Your selections are indicated below. If you have further ' +
N'questions, please contact the Benefits and Risk Management Department.
' +
CHAR(13) + CHAR(10) +
N'<table cellspacing="0" border="0" style="width:98%;border-collapse:collapse;">' +
CHAR(13) + CHAR(10) +
N'<tr><th scope="col" style="font-weight:bold;text-decoration:underline;">' +
N'Plan Description
</th>' + CHAR(13) + CHAR(10) +
N'<th scope="col" style="font-weight:bold;text-decoration:underline;">' +
N'Coverage
</th>' + CHAR(13) + CHAR(10) +
N'<th scope="col" style="font-weight:bold;text-decoration:underline;">' +
N'Deduct Before Tax?
</th>' + CHAR(13) + CHAR(10) +
N'<th scope="col" style="font-weight:bold;text-decoration:underline;">' +
N'Monthly Payroll Deduction
</th></tr>' + CHAR(13) + CHAR(10);
SELECT @TotalBen = COUNT(*)
FROM @BenList;
WHILE @RowCounter - 1 < @TotalBen
BEGIN
SET @HTMLMessage = @HTMLMessage +
CAST((SELECT '<tr><td>' + ISNULL(BENTXT, '') + '</td>' + CHAR(13) + CHAR(10) +
'<td>' + ISNULL(BENCOV, '') + '</td>' + CHAR(13) + CHAR(10) +
'<td align="center">' + ISNULL(BENBTX, '') + '</td>' +
CHAR(13) + CHAR(10) +
'<td align="center">$' + ISNULL(CONVERT(VARCHAR, BENMPD), '0.00')
+ '</td></tr>' + CHAR(13) + CHAR(10)
FROM @BenList
WHERE (RowNumber = @RowCounter)) AS NVARCHAR(MAX));
SET @RowCounter += 1;
END;
SET @HTMLMessage = @HTMLMessage +
N'<tr><td></td>' + CHAR(13) + CHAR(10) +
N'<td></td>' + CHAR(13) + CHAR(10) +
N'<td align="center" style="font-weight:bold;">Total Monthly Deduction</td>' +
CHAR(13) + CHAR(10) +
N'<td class="tdTopBorder" align="center" style="font-weight:bold;">' +
CAST(@TotalDeduct AS NVARCHAR(MAX)) + N'</td></tr></table>' + CHAR(13) + CHAR(10);
SET @Subject = 'Benefits Confirmation Message';
SET @HTMLMessage = @HTMLMessage +
N'</body>' + CHAR(13) + CHAR(10) +
N'</html>';
SET @HTMLMessage = REPLACE(@HTMLMessage, '<', '<');
SET @HTMLMessage = REPLACE(@HTMLMessage, '>', '>');
SET @HTMLMessage = REPLACE(@HTMLMessage, '&', '&');
SET @FileAttach = 'D:\TempFiles\' + @RandomDir + '\' + @SSN + '.pdf';
EXECUTE AS LOGIN = 'PINNACLE\SQLAdmin';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Benefits',
@recipients = @Email,
@subject = @Subject,
@body = @HTMLMessage,
@body_format = 'HTML',
@file_attachments = @FileAttach;
REVERT;
END;
SET @Command = 'rd D:\TempFiles\' + @RandomDir + '\ /S /Q';
EXECUTE AS LOGIN = 'PINNACLE\SQLAdmin';
EXEC master.dbo.xp_cmdshell @Command;
April 30, 2010 at 8:16 am
I believe that xp_cmdshell is taking you out of the current security context which is causing your problem. I'm not even sure why you are using xp_cmdshell here.
Since this is a .NET application, why not use the email functionality in .NET to send the email? You can get the data you need from SQL and do all your processing and email in .NET. I think that would be a simpler, and a more secure implementation as you avoind the security risks associated with xp_cmdshell.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply