I have a requirement given to me to encrypt a fields data within an existing table. We need to be able to read and write the data (i.e. encrypt it into the database and decrypt it back out - so only the storage of the data is encrypted).
I do not have any control over the application layer - so I cannot encrypt the data before it reaches the database, but when the data reaches the DB then it needs to be encrypted. I realise this is not an ideal situation - but it is the one I am faced with! Ideally I would be able to implement the encryption in the .NET code and pass it through 'as is' to the DB for storage... but I can't.
Does anyone know any third party (ideally freely available) algorythms that can be called from a Proc or Trigger - i.e. drop into the shell and execute an exe or similar and then insert the data. Would i be able to use RC4 or similar for example directly with SQL?
Any help appreciated - if I am barking up the wrong tree (or just barking mad) please let me know.
Hi - the encrption tools link looks potentially useful - though I really 'just' want an exe I can call rather than installing a full blown 3rd party solution (like XP_CRYPT -which looks good) ... maybe something in there will do the job though
"Understanding Transparent Data Encryption (TDE)" - is only for SQL2008 - sorry my fault for not saying I'm on SQL 2000... thanks for the time though
If you don't have access to the app, AND they aren't using stored procs for data access only, then you have no control ..and even if they did, opening them up and changing them would mostly likely void any support contract
Thanks Brett for the answer - yes it sounds a tricky one, as the set up of the code is that I don't have access to the insert code (you are right it is not stored procedures) but I do have access to the output code.
In short I am doing the reporting side of things and the data capture is offshore - no support contract to worry about but lifecycles of development and change requests mean that it is not feasible to get it changed in the timescales (sigh).
I am exploring the possibility of using a trigger on the insert which theoretically should work (in my eyes!) - but still need a steer on an exe or method to call to make the encoding and decoding call... it's a hack I know, but then ours is not to reason why...