locked
Encryption Success or Failure. how do I know? RRS feed

  • Question

  • Learning 101:    In t-sql I have used ENCRYPTBYPASSPHRASE to encrypt a column of data.  How, programatically, can I tell if the encrpyiton attempt succeeded?  Will t-sql TRY...CATCH handle tell me?  If so,  what is the "Catch" error I need to check for? If TRY..CATCH doesn't catch such errors, what then?


    TIA,

    barkingdog

    Thursday, March 19, 2009 10:12 PM

Answers

  •  If the call to encryptby* family of builtins fails to encrypt the data, the function should return null. Given the behavior of the language, there are some errors (such as data truncation) that will fail with an explicit exception such as error 8152.

      Checking for null return value may be enough in many cases, but depending on your scenario, using TRY/CATCH in addition to the null check to handle other errors (i.e. the user does not have permission to access the table where the encrypted data is stored) may be highly recommended.

      I hope this information helps.

     -Raul Garcia
      SDE/T
      SQL Server Engine

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, March 20, 2009 2:35 AM