Encryption a Date Field

Question

Encryption a Date Field

krishna.kumar

Right there with Babe

Points: 775
More actions
July 24, 2009 at 6:45 am

#97848

Hi All,
How do we encrypt a date datatype field. Please help.
If poss with an example
regards
krishna

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

encrypted fields have to be nvarchar or sometimes varbinary data types for any encryption to work, depending on the encryption method. they also have to typically be much larger than the original field.

from there, you'd have to decrypt to back to nvarchar, and then convert the results to datetime.

the same thing would have to happen to any field you want to encrypt, whether int or any other datatype...you store the encrypted values as nvarchar, usually.

there is a great example of using the SQL 2005 encryption techniques here:

http://www.databasejournal.com/features/mssql/article.php/3483931/SQL-Server-2005-Security---Part-3-Encryption.htm

it took me all of 2 minutes to read the article and get the example to work, and i had to create the master key as well.

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!

Paul White SSC Guru Points: 150467 More actions · Answer 2

Please see my reply to your other question about encryption.

Your basic error is trying to store the encrypted data as the original datatype rather than using VARBINARY.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

You pay a price with encryption. Are you sure that you need to encrypt dates?

krishna.kumar Right there with Babe Points: 775 More actions · Answer 4

Hi All,

Thank you so much for your inputs and it has helped me immensely. The requirement is such. Our client needs data encryption to be done on date data type.