How to use store procedure sp_send_dbmail to send out a SECURE e-mail?

Question

Post reply

How to use store procedure sp_send_dbmail to send out a SECURE e-mail?

mary.wang

Say Hey Kid

Points: 702
More actions
September 28, 2010 at 4:01 pm

#238919

How to use store procedure sp_send_dbmail to send out a SECURE e-mail?
Mary Ming Wang

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

You may use the @sensitivity parameter. Please refer this for more information.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Lowell SSC Guru Points: 323518 More actions · Answer 2

Adiga (9/28/2010)
You may use the @sensitivity parameter. Please refer this for more information.

I think that parameters just adds a little used flag to the headers stating the "sensitivity", like confidential; similar to the "Importance" flag.

It does not encrypt the message or otherwise obfuscate the message;

I think the OP was asking how he can use Public/Private keys to create a message that NOONE except the person who receives it, and has the correct private key to decrypt it with.

In my experience i've seend email clients using things like PGP to handle that. I've seen posts here on how to use PGP to encrypt stuff, i suspect it's a combination of using the two techniques...build the <body> tag with an encryption method, and use sp_send_mail to send an encrypted body, with everything else (email address,subject, headers) as normal.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mary.wang Say Hey Kid Points: 702 More actions · Answer 3

Thank you Ten and Lowell:

When we send out e-mail using Microsoft Outlook, if we put the word ‘SECURE’ in subject line, the user will receive the e-mail notice and they need to log into our system to get the e-mail and the attachments.

That function is what I’m looking for, I wonder if the store procedure sp_send_dbmail will provide the similar functionality.

The @sensitivity parameter will add a line ‘Please treat this as Confidential’ at the top of the email but didn’t provide enough security for the e-mail message and the attachments.

Are the PGP encrypting the only way to get the ‘SECURE’ e-mail out? Can you please send the how to PGP encrypt link to me?

Mary Wang

Mary Ming Wang

Lowell SSC Guru Points: 323518 More actions · Answer 4

man this is really interesting, but the rabbit hole is getting deeper and deeper.

if you send an email via sp_send_mail with SECURE in the subject, does your Exchange Server encrypt the email for you?

i found this overview article on how Outlook 2007 is using #DES encryption, with public and private keys:

http://office.microsoft.com/en-us/outlook-help/encrypt-e-mail-messages-HP001230536.aspx

stating the obvious, if i knew the public key, i could then use a 3DES encryption method on SQL server to create an encrypted string to include int he body; at least from there we could test to see if it can be opened or not when it finally gets received.

I'll keep poking around till i find a code snippet or two, but until then google and i are running around together.

this article on SSC has a sql 2000 toolkit[/url] to add extended stored procedures to do encryption, and 2005 and up have some built in encryption tools, but it's the testing of compatibility that is important.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mary.wang Say Hey Kid Points: 702 More actions · Answer 5

Found one possible solution:

The SMTP server (set up in table msdb.dbo.sysmail_server) needs to be routed through Microsoft exchange server. Once the SMTP server has been routed through Microsoft exchange server, the ‘SECURE’ e-mail will be sent out.

Mary Ming Wang

frank.kress SSC Veteran Points: 281 More actions · Answer 6

Hello everyone,

this articles are over 10 years old.

Right now I'm asking myself the same question today; "How can I do this today?"

We use Outlook in the company and use "S / MIME" encryption when I send a secure email with attachments.

What is my goal: "Data delivery on demand" and it should only be sent to the recipient who requested the data.

But how can I use "S / MIME" encryption with msdb.dbo.sp_send_dbmail?

Have already searched Google empty. 🙂

Does anyone have a solution?

Best thanks in advance.

Frank

jy9230 Newbie Points: 1 More actions · Answer 7

jy9230

Newbie

Points: 1

May 3, 2023 at 5:39 pm

#4183682

Test