Trigger to encrypt field before write

[rcamarda]

Hello,
While working through my encryption questions from preivous posts, I am
finding that I may have to resort to use triggers to do the encryption
(not that this is the only way, but might be the best way for my
circumstances).
I would like to create a trigger that will encrypt the field before the
write is committed.
I've found serveral posts about triggers, but nothing that fits what I
wish to do.
Upon an insert or update I want to modify the ssn field with this:

cast(EncryptByA symKey(AsymKey_ ID('Student_aKe y'), cast(SSN as
nvarchar(11))) as nvarchar(40))

so, ssn '123456789' in SSN would become <something encrypted> in SSN

This is the trigger I have so far, but it is generating an error:

CREATE TRIGGER F_Student_SSN.e SSN
ON F_STUDENT_SSN
INSERT, UPDATE
AS SELECT cast(EncryptByA symKey(AsymKey_ ID('Student_aKe y'), cast(SSN as
nvarchar(11))) as nvarchar(40))

TIA
Rob

[rcamarda]

Re: Trigger to encrypt field before write

more research I have:
CREATE TRIGGER F_Student_SSN.e SSN
ON ds_v6_staging.F _STUDENT_SSN
update, insert
AS
update f_student_ssn
set essn = cast(EncryptByA symKey(AsymKey_ ID('Student_aKe y'),
cast(eSSN as nvarchar(11))) as nvarchar(40))

But still get error

[Erland Sommarskog]

Re: Trigger to encrypt field before write

rcamarda (robc390@hotmai l.com) writes:[color=blue]
> more research I have:
> CREATE TRIGGER F_Student_SSN.e SSN
> ON ds_v6_staging.F _STUDENT_SSN
> update, insert
> AS
> update f_student_ssn
> set essn = cast(EncryptByA symKey(AsymKey_ ID('Student_aKe y'),
> cast(eSSN as nvarchar(11))) as nvarchar(40))
>
> But still get error[/color]

I realise that you are working with sensitive data, and may want to
disclose much. However, with knowing what error you get, it's difficult
to assist.

Of course the trigger as such is not a good one, since you are updating
the entire table, you will encrypt already encrypted data on each
INSERT or UPDATE.

You need to add:

update f_student_ssn
set essn = cast(EncryptByA symKey(AsymKey_ ID('Student_aKe y'),
cast(eSSN as nvarchar(11))) as nvarchar(40))
from f_student_ssn f
join inserted i ON f.pkcol = i.pkcol

The table "inserted" holds an after-image of the inserted/updated rows.
Note the plural: a trigger fires one per statement.

But the above only makes sense for an INSERT trigger. For an UPDATE
it's tricker. You could add:

IF UPDATE(eSSN)
BEGIN
UPDATE ...
END

But if someone for some reason says:

UPDATE tbl SET eSSN = eSSN

you will end up encrypting the encrypted value.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[rcamarda]

Re: Trigger to encrypt field before write

OH...thanks for the Help Erland!
You bring up some very valid points. I may luck out because my plan was
to truncate the table after I am done. Once the data is encrypted, I
can then processed it 'normally', so I wont have to worry about
encypting encrypted data. I am tying to minimize the exposure of clear
text data that I wish to encrypt.
I tried to load the text and encypted using Integration Services, but
could spit that atom yet. (I got data to load, but was stumped when I
needed to add the encrypted functions)
Rob