Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009
    Posts
    5

    Unanswered: Encrypting a field via SP or Trigger

    Hi,

    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.

  2. #2
    Join Date
    Oct 2003
    Location
    Belfast
    Posts
    16
    ---------------------------------------
    Make something idiot proof- and someone will make a better idiot
    ----------------------------------------

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Use Transparent Data Encryption
    Understanding Transparent Data Encryption (TDE)

  4. #4
    Join Date
    Aug 2009
    Posts
    5
    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

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    There are no built-in tools for encryption in SQL 2000.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The short answer to your question, is No

    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Aug 2009
    Posts
    5
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •