Post reply

Data type for Encrypted value

  • June 29, 2011 at 6:42 am

    #242880

    Which data type should we use after encryption of data using data encryption logic? currently data type is defined as nvarchar(max).

  • June 29, 2011 at 6:51 am

    #1345881

    That's right! nvarchar is the data type you should use for storing encrypted value as it supports to store unicode charachters.

    Abhijit - http://abhijitmore.wordpress.com

  • June 29, 2011 at 7:10 am

    #1345901

    it depends on the encryption algorythm;

    many encrypt to varbinary, but some others can encrypt to varchar/nvarchar for the encrypted values (AES =varchar, for example),

    SQL's built in Encrypt By Assymetrickey/passphrase/certificate requires varbinary(max) columns for the encrypted values.

    --http://www.databasejournal.com/features/mssql/article.php/3714031/SQL-Server-2005-Encryption-types.htm

    -- EncryptByPassPhrase(@password,@DataToEncrypt )

    select EncryptedData = EncryptByPassPhrase('PEPE', '123456789' )

    -- DecryptByPassPhrase(@password,@DataToDecrypt )

    declare @val varbinary(max)

    SET @val = 0x010000004A298120E629F7F4DC4AD09EB0E380C9E60040BDAE1DB6D8DA98C2A4D249EB71

    select convert(varchar(100),DecryptByPassPhrase('PEPE',@val))

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 29, 2011 at 7:53 am

    #1345933

    Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply