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

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

    Mary Ming Wang

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

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • 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!

  • 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

  • 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!

  • 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

  • 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

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

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