Encripting seconds value

Question

Encripting seconds value

venkatesh.kuppili

Mr or Mrs. 500

Points: 579
More actions
December 29, 2008 at 11:54 am

#203381

We developed some applications with games like instant win and those are in production.
For the instant win, there is a table with prize_id(1,2,..), time(in seconds) and status code(available, used..) and in this table the Time field is calculated from the “compare date” for example if the date is 11/01/2008 ( this date is randomly set in .net application) and the start date of the instant win game is from 11/15/2008 there is a stored proc for calculating the time that users can win the prize in seconds from start date to end date. So in the table we are storing the time values that the users can win the prizes. So this time field should not be displayed to the outside world so we want to encrypt the time field value so that db administrator or the client should not know these values.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

If you don't want the DBA OR the client to understand the values - then I'd recommend you encrypt the value using something external (say, in .NET before it even gets passed in), and simply store the encrypted value in a varbinary column.

Using .NET to implement the encryption will give you lots of options. it has 4 or five different built-in routines, and has the option to hook into a series of other premium ones (if you don't think the built-in ones are secure enough).

This way - the only thing that can decrypt it will be your external app. If you use a concept like asymmetric keys, the server would never know how to decrypt unless it is presented with the private key (which you then have to figure out how to secure appropriately.

That being said - you might care to use something more random than the time. That would we a reasonably obvious thing to go after if you were trying to crack the encryption/decryption.

Finally - for what it's worth = you might care to check out your colleague's post. You're apparently working on the same project.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

The highly similar post is located here:

http://www.sqlservercentral.com/Forums/Topic626691-359-1.aspx

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

venkatesh.kuppili Mr or Mrs. 500 Points: 579 More actions · Answer 3

Thank you very much for the reply. we do not want to implement encritpiton through .net (using external way). We want to implement encrytping this time field value through sql2005.

Any suggestions!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Then you will not be able to deny the DBA access to the data. He sets up the encryption, after all.

That being said - here's the microsoft white Paper on the topic:

http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/SQLEncryption.doc

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

venkatesh.kuppili Mr or Mrs. 500 Points: 579 More actions · Answer 5

i created a stored procedure

in order to display decrypted value..as below

declare @pass VARCHAR(50)

SET @pass = 'WOhPorN5VIKZl$Eqcz4CR104O'

SELECT seed_time,seed_encrypt,

CONVERT(VARCHAR(50),DecryptByPassphrase(@pass, encrypt_time)) AS Decrypt_time

FROM TestTable

i dont know how secured "DecryptByPassphrase" is.. can you please help on this..

i want to know is it possible to track password

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

venkatesh.kuppili (12/30/2008)
i dont know how secured "DecryptByPassphrase" is.. can you please help on this..

Have you read through the section on that function and encryption in general in books Online?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

payal.mitra86 SSC Enthusiast Points: 114 More actions · Answer 7

plz see d related topic,

http://www.sqlservercentral.com/Forums/Topic627976-359-1.aspx