By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

Trigger to encrypt field before write

P: n/a
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(EncryptByAsymKey(AsymKey_ID('Student_aKey'), 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.eSSN
ON F_STUDENT_SSN
INSERT, UPDATE
AS SELECT cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'), cast(SSN as
nvarchar(11))) as nvarchar(40))

TIA
Rob

Apr 28 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
more research I have:
CREATE TRIGGER F_Student_SSN.eSSN
ON ds_v6_staging.F_STUDENT_SSN
update, insert
AS
update f_student_ssn
set essn = cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'),
cast(eSSN as nvarchar(11))) as nvarchar(40))

But still get error

Apr 28 '06 #2

P: n/a
rcamarda (ro*****@hotmail.com) writes:
more research I have:
CREATE TRIGGER F_Student_SSN.eSSN
ON ds_v6_staging.F_STUDENT_SSN
update, insert
AS
update f_student_ssn
set essn = cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'),
cast(eSSN as nvarchar(11))) as nvarchar(40))

But still get error


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(EncryptByAsymKey(AsymKey_ID('Student_aKey'),
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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 28 '06 #3

P: n/a
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

Apr 28 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.