help needed with stored proc....

Question

help needed with stored proc....

raaj820

SSC Veteran

Points: 251
More actions
January 28, 2008 at 6:45 am

#183412

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.....

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

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

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

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 2

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

--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!

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

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?

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

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

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

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 5

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/