January 28, 2008 at 6:45 am
Hi guys,
I am using sql server 2000
I am having an excel attachment which i need to import into my database (i have done this part by using import/export wizard)...
so now i am having a tbl in my database named 'processingtbl' having 5 columns named col1,col2,col3,col4 and cardnumber as follows..
col1 col2 col3 col4 cardnumber
1001 24653 10 298
1002 74839 20 733
1003 34690 11 988
Now, I need to write a stored proc which when i call should generate the cardnumber(by concatenating col1,col2,col3,col4) and shud encrypt it along with the import.
this is wht i tried...
create proc sp_getcardnumber
as begin
update processingtbl
set cardnumber = col1+col2+col3+col4
select * from processingtbl
end
--exec sp_getcardnumber
when i call the above stored proc, i can generate the cardnumbers successfully..but i am not able to understand how to encrypt them along with the import..(i was also asked to think about secure passphrase to use to encrypt the card numbers)...
so whts this passphrase and how to encrypt this cardnumber?????
any help is really appreciable....
thnx in advance....
raaj.....
January 28, 2008 at 7:42 am
Unfortunately SQL 2000 does not have any built-in encryption routines.
Your best bet is probably to write a front end and use the windows cryptography APIs to encrypt the card numbers. Or write/locate an extended stored proc that uses the same APIs
It's not something I've done myself.
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
January 31, 2008 at 8:11 am
don't forget the SQL DBA Toolkit here on SSC: it contains stored procedures to encrypt/decrypt data, as well as other goodies:
http://www.sqlservercentral.com/articles/Security/sql2000dbatoolkitpart1/2361/
Lowell
January 31, 2008 at 9:30 am
I don't know about what your process is - but all of the built-in encryption functions, even the ones in 2005, are wholely inadequate for storing Credit card info.
You're going to want to invest in some third-party encryption process for that (say - PGP, or some of the higher-end ones).
If your goal is to secure these numbers, then arguably your best bet is for the database to store the encrypted version, and not have any knowledge as to how to decrypt it. In other words - use an external API to decrypt the card numbers. The algorithms built-in are IMO not worthy or strong enough to store that kind of stuff.
Of course- if you're USING that actual info somewhere in the DB (shudder), then obviously this becomes not workable rather quickly....
----------------------------------------------------------------------------------
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?
January 31, 2008 at 10:23 pm
Matt Miller (1/31/2008)
I don't know about what your process is - but all of the built-in encryption functions, even the ones in 2005, are wholely inadequate for storing Credit card info.
I'm curious. Why do you say that? Because the decryption mechanisms and keys are within the DB as well?
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
February 1, 2008 at 1:57 pm
I agree with GilaMonster.
In SQL 2000, we can encrypt/decrypt stored procedures, but not saved data.
In SQL 2005, the function of encrypting data is added. Yes, we can encypt data by ourselves, although it is not safe. Here is a link to have a brief understanding of how to encypt data in SQL 2005.
http://vadivel.blogspot.com/2005/08/encrypt-and-decrypt-data-in-sql-server.html
To have more on the built-in encryption SQL 2005, we may reference a link in this forum,
http://www.sqlservercentral.com/articles/SQL+Server+2005+-+Security/sql2005symmetricencryption/2291/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply