Encryption Data - Table Design

[vbkris]

Hi All,

I am in the process of designing the table structure for storing encrypted data. Encryption will happen on the application side (ASP.NET). We are using AES-256 to encrypt the data.

Have the following queries:
1. Should I use Varbinary or Varchar for storing encrypted columns? Am I correct in my assumption that if I use varbinary then I can directly save my encrypted data in bytes into the column without need for converting to string?

2. On what basis do I decide the column sizes?

Thanks in advance...

Known is handfull, Unknown is worldfull

 

[gmmastros]

I suggest you try using varbinary(max) as your data type. This assumes you are using SQL2005 or greater. varbinary(max) does not exist for SQL2000. By using varbinary(max), you do not need to worry about the size of the data. Do not use (max) for everything though because it will eventually hurt your performance. If you are encrypting just a couple columns (Credit Card, SSN, etc...) then you should be fine.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

If you do not want to use max see Example

I found it helpful in determining size of the VARBINARY.

Good Luck,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[vbkris]

thanks for the responses.

gmmastros:
All the data that the user is going to save into my system would typically be sensitive data .

djj:
While some of the data would be financial (numbers) others would be textual. The encryption algorithm that I have written is such that the ecnrytpion key would be different for each user. Therefore I cannot use the method that has been suggested in the link to estimate a size as i dont know the key nor the IV.
Another issue that I have is that I have multiple levels of encryption. Some data I encrypt using SQL Server method as the key would be same across all users. I would need to also estimate the sizes for these values.

I guess its varbinary(max) for me.

Am still working on this. Will get back to you guys on what I finally managed to do...

Known is handfull, Unknown is worldfull