storing an integer value encrypted 1

[rhofing]

Hello, I have an application that will need to store a confidential PIN # (much like an ATM card). I want to store this value encrypted. However, sometimes customers forget their PIN and I need to be able to tell them what it is (after they have passed the authentication process of course).

Customers will need their PIN each time they log in. Can someone give me some suggestions on how to do this?

Thanks everyone!

Ric

 

[JamesLean]

You can't set the actual columns to store the data in encrypted form. I would imagine the only way to do it would be to encrypt the data in your front-end app and then store the result in the DB.

I don't know if there are any 3rd party components that offer encryption routines or whether you could write one yourself? --James

 

[sguslan]

use Encrypt function to encrypt data before storing a value in the sql table in question.
Examples:

To encypt data:

declare @pin varchar(20)
set @pin='2992'
insert into PinTable(CustomerID,PinNumber)
values(1000, encrypt(@pin))

But you will not be able to display data back in clear text.
The only thing you can do is to reset the pin number with a new value and store it again.

To check the validity of a pin number entered by a user, do something like:

declare @Customer varchar(10)
declare @CustomerClearPin varchar(10) -- to be entered by customer some how

declare @EncryptedPin varchar(10)

set @Customer='1000'
set @CustomerClearPin='2200' -- should be entered by application

SELECT @EncryptedPin=PinNumber from PinTable where CustomerID = @Customer

if encrypt( @CustomerClearPin) = @EncryptedPin
begin
print 'customer entered the correct pin number'
end


________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[JamesLean]

Sorry, I was unaware of this function.

sguslan, excellent post. Do you have a link to any documentation on this function? --James

 

[sguslan]

http://www.sqlservercentral.com/columnists/bknight/encryptfunction.asp

http://www.swynk.com/friends/knight/encryptfunction.asp

________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[JamesLean]

Thanks!

Ric, you may want to check out the second link for things to be aware of (if you haven't already!). --James