Home Forums SQL Server 2008 SQL Server 2008 - General Data in certain fields in a table needs to be encrypted and later on decrypted to be used in sp. What should be the appropriate data type that can be used so that the same column can hold both the encrypted form and decrypted form of data? RE: Data in certain fields in a table needs to be encrypted and later on decrypted to be used in sp. What should be the appropriate data type that can be used so that the same column can hold both the encrypted form and decrypted form of data?

  • April 18, 2014 at 10:19 am

    #1707131

    Encrypted data needs to be stored as varbinary. You cannot also store the decrypted value.

    However, if you also store the decrypted value, there's arguably no reason for you to encrypt the data.

    If you wish to decrypt for calculations, you can do that on the fly in queries, however be aware that encryption/decryption uses lots of CPU.

    I assume you mean you will use a symmetric key for encryption/decryption and protect that with a certificate. That's what I'd recommend. Note that your users will need VIEW DEFINITION on the symmetric key and CONTROL on the certificate.