Decryption a nchar data field returns null

• krishna.kumar

  Right there with Babe

  Points: 775

  More actions

  July 24, 2009 at 12:32 am

  #97847

  Hi All,

  I am using SQL server 2005 encryption and decryption to add security to my data. I am able to encrypt and decrypt varchar and nvarchar data type but my nchar data field returns null when i decrypt them.

  My Create Table Script.

  CREATE TABLE [UserDetails](

  [UID] [bigint] NOT NULL,

  [FName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

  [LName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

  [PetName] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

  ) ON [PRIMARY]

  Then I am opening my symmetric key and doing the insertion operation.

  INSERT INTO UserDetails

  (UID, FNAME, LNAME, PetName)

  VALUES

  (1, EncryptByKey(Key_GUID('Sym_PatriotData'), 'Tony'), EncryptByKey(Key_GUID('Sym_PatriotData'), 'Thomas'),

  EncryptByKey(Key_GUID('Sym_PatriotData'), 'Tommy'));

  The query executes perfectly.

  Then i again decrypt to select the records

  SELECT UID, CONVERT(VARCHAR(100),DECRYPTBYKEY(FNAME)) AS FNAME,

  CONVERT(VARCHAR(100),DECRYPTBYKEY(LNAME)) AS LNAME,

  CONVERT(nCHAR(10),DECRYPTBYKEY(PetName)) AS PName

  FROM UserDetails

  When i execute this query, I am able to retrieve the values for Fname and Lname fields but the PetName field returns null. I am executing the query keeping the symmetric key open.

  Any help wud be greatly appreciated.

  Warm Regards

  krishna

• Paul White

  SSC Guru

  Points: 150467

  More actions

  July 25, 2009 at 8:23 am

  #1030152

  The encryption functions, such as EncryptByKey, return VARBINARY, with a maximum size of 8000.

  Your table attempts to store these binary values as VARCHAR or NCHAR. This is wrong.

  The decrytpion functions, such as DecryptByKey, expect VARBINARY input, with a maximum size of 8000.

  These functions also return VARBINARY, which you then need to CAST or CONVERT to the correct datatype.

  Paul

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• krishna.kumar

  Right there with Babe

  Points: 775

  More actions

  July 27, 2009 at 1:11 am

  #1030387

  Hi Paul,

  Thank you. I started with Varbinary Data types for the fields that needed encryption. Then i learnt that NVarchar can also be used. I thought that I can use any data type for encryption. Thanks once again letting me know that encryption can be done only on Varbinary and NVarchar. Lesson learnt. 🙂

  Warm regards

  krishna