Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

help needed with stored proc.... Expand / Collapse
Author
Message
Posted Monday, January 28, 2008 6:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 28, 2008 10:59 AM
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.....
Post #448273
Posted Monday, January 28, 2008 7:42 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 41,517, Visits: 34,432
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 2008, MVP
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

Post #448309
Posted Thursday, January 31, 2008 8:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:29 PM
Points: 12,741, Visits: 31,053
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #449966
Posted Thursday, January 31, 2008 9:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,081, Visits: 14,673
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?
Post #450030
Posted Thursday, January 31, 2008 10:23 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 41,517, Visits: 34,432
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 2008, MVP
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

Post #450307
Posted Friday, February 01, 2008 1:57 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 05, 2013 4:43 PM
Points: 1,473, Visits: 1,314
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/
Post #450768
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse