January 14, 2011 at 12:45 pm
I have a table with various columns. when user enters data from application, the values get inserted into this table. one of the column is 'password'.
So by default, whatever password the user will enter, is going into the table.
So i have created a trigger that fires after every insert/updates and changes the value of password.
UPDATE table
SET Password = EncryptByKey(Key_GUID('iie_users_test_key_01'),password)
Where user_logon_id IN (Select user_logon_id from Inserted)
The trigger is created. BUT When I try to insert something it never changes the value but it appears as a blank field. So wht I am thinking is if user enters 'texas' in the field of password after that I need to capture it inside some variable and then update the variable so that it becomes encrypted.
but i dont know how to write that code here. plz advise me. thanks a lot
January 14, 2011 at 2:34 pm
I have a business need that when user enters data from application, some sensitive fields should get encrypted. Like values for password column should be encrypted. What I have done is,
--First of all Create a Master Key
--If there is no master key, create one now.
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'Password1@'
GO
--Then Create a certificate
CREATE CERTIFICATE test_certificate
WITH SUBJECT = 'test certificate;
GO
--Then Create a SYMMETRIC KEY
CREATE SYMMETRIC KEY test_key_01
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE test_certificate;
GO
So now when user enters data from outside, the password filed should get encrypted. So I wrote this trigger,
CREATE TRIGGER encrypt_password
ON test_table
AFTER INSERT
AS
OPEN SYMMETRIC KEY test_key_01
DECRYPTION BY CERTIFICATE test_certificate
Declare @Password varchar(800)
Select @password = password from inserted
OPEN SYMMETRIC KEY iie_users_test_key_01
DECRYPTION BY CERTIFICATE iie_users_test;
UPDATE iie_users_test
SET Password = EncryptByKey(Key_GUID('iie_users_test_key_01'),@password)
Where user_logon_id IN (Select user_logon_id from Inserted)
this trigger, So after an insert when I check the password column for this record, there is blank field there (not even NULL or any other value) but its blank.
Is there any problem with my trigger??
So I tried another method, which gives me the same result. BLANK FIELD.
declare @password varchar(800)
Set @password = 'newyork'
Insert into dbo.test_table(user_logon_id,password,last_name,first_name,creation_date)
values('email_56',EncryptByKey(Key_GUID('iie_users_test_key_01'), @password),'steve','berry',getdate())
How can I see encrypted value inside the column? Or let me know if any other method to do this or not.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply