Hi all,

Have been encrypting some fields from some sensitive tables in an app. The app uses local views and the ones that have encrypted fields are created programmatically. The fields that are character fields are easy - extend the field to handle the increased size of the encrypted data and then decrypt on the fly and encrypt during the save process of the form.

I have written a wrapper function to make decryption really easy to incorporate into SQL strings e.g.

CREATE SQL VIEW v_Lawyers AS ;
SELECT Lastname, Firstname, ;
DecryptString(DirectLine) AS DirectLine...FROM Lawyers...

So for the character fields, the field gets decrypted in the above view, bound to the form control and then just before tableupdating the view, the pertinent fields are encrypted.

BUT, this does not work well for non-character data. For example, for birth dates I have to pull out the data using a function that converts the character encrypted date into a date field:

CREATE SQL VIEW v_Plaintiffs AS ;
SELECT Lastname, Firstname, Gender, ;
MakeDecryptedDate(Birthdate) AS Birthdate2, Birthdate FROM Plaintiffs...

So in this case, I bind to "Birthdate2" as it is in date format and carry along Birthdate which is the original unencrypted character type data. The user updates Birthdate2 and then in my save routine, I encrypt the data in Birthdate2 and overwrite Birthdate and post.

Okay, so having said all that - are there any other ways someone can think of that would make this easier? I am at the point in the project where I have to start encrypting a whole bunch of numeric type data and I will have to go through the same gymnastics on each of those fields.

BTW, this would have been transparent in the latest SQL server (I hear) where the back-end engine handles all the encrypting/decrypting - but that is not even a possibility at this point for this client.

Thanks,
Albert