Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Date column Encryption

Author  Topic 

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-11 : 09:29:34
Hi,

I have one doubt regarding data type for encrypted date column...

I have date-of-birth column with DATE type... We want to encrypt this field through application and then store encrypted value into database... For that purpose what should be the data type for date-of-birth ?


--
Chandu

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-11 : 10:17:06
I believe that depends on the applciation (encryption tool's) ouput for a date value. The same approach has been used in one of my company, where encryption was performed via some encryption tool in the application and then storing it as nVarchar(255) in the database.

Cheers
MIK
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-11 : 12:18:48
The other option would be varbinary of an appropriate length.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-12 : 00:28:39
If we used VARCHAR(255)/varbinary types, there are some problems like
1) we have to search specific records based on DOB
2) Sometimes we have to calculate AGE from DOB

those operations might take few code changes... right?

--
Chandu
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-12 : 06:28:36

1) You may search specific record(s) for a DOB, where you'll need to pass/compare the encrypted version of date with stored information. e.g. when you select for records where DOB=1984-1-1, pass on encrypted version of information to the stored procedure (WHERE DOB = 'encryptedDOB'). However this might not work in the case when you'll be searching for records having DOB > 1984-1-1 - not sure but a CLR might help you here.

2) you may acheive it using the CLR function, which will decrypt the encrypted version and then calculate the age ..

May be someone can come up with a better solution if you provide a narrative of the business requirements for encrypting the DOB information. If this is just for sake to restrict normal users' access , you may want to set column level permission on this particular field so that only allowed person(s) have access to it.

Cheers
MIK
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-12 : 06:31:52
For security purpose we want to encrypt DOB column... Already developed 95% of application... We won't allow huge code changes as of now....
That's why I would like to know the other feasible solutions

--
Chandu
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-12 : 06:59:05
I believe there would be lesser/no changes in the application code and can be handled all alone via tSQL.

Another approch could be keep saving the DOB as date format but set column level permission over it. And if the "Security" factor is in context of "inside SQL", then I believe column level permision would be enough .. but if this is for outer mediums (data flow between DB and application) then implement security on both layers.
Layer1 - Inside DB
Layer2 - ouside the DB

Layer1: would be to set colum level permissions to a user/account other than the application one, and to perform any DML/Select operation on the particular table/column.
Layer2: would be to send the data in encrypted form from application to the DB, and vice versa.

Cheers
MIK
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-12 : 07:06:21
We just have database... no more DB activities...
They designed to do all through front-end only by using LINQ...
I think ours is Layer2 ( My concern is to store encrypted DOB to database)
Is there any possibility to hack person information by using DOB?

--
Chandu
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-12 : 07:37:02
quote:
Originally posted by bandi


Is there any possibility to hack person information by using DOB?
--
Chandu



Well Chandu, far as I know, its the matter of access either to the information repository or to the individual account or to the communication medium (network/domain - a rare one). In all three it's the credentials would be required.

I don't think one can hack personal information just by having DOB in hand. Such information is encrypted mostly due to an agreement between clients and company so that it cannot be disclosed to any one including companys' employees.

Cheers
MIK
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-12 : 07:45:08
yes I know DOB encryption is least priority....
Thank you so much for valuable information....
I will get back if needed

--
Chandu
Go to Top of Page
   

- Advertisement -