Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


help needed with stored proc....


help needed with stored proc....

Author
Message
raaj820
raaj820
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 53
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.....
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47133 Visits: 44346
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
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14912 Visits: 38899
don't forget the SQL DBA Toolkit here on SSC: it contains stored procedures to encrypt/decrypt data, as well as other goodies:
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)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7624 Visits: 18043
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?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47133 Visits: 44346
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
SQL ORACLE
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1487 Visits: 1314
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search