Encryption question

Hello Everybody,
I have a Encryption - Decription Question.
In my project we are getting an XML File from a vendor which has a credit
card number in clear text. We use XML bulk load process to load table from
xml file.
How can i encrypt credit card number while storing into table.
Also i will have to decrypt CC number while i create comma separated file
for another vendor ?
Pls let me know.
thxA credit card number is typically not the type of attribute used for
indexing, sorting, grouping, or composing a primary key, so I see no reason
why it would not be a good candidate for encryption. It would be reasonable
and fairly simple to store it encrypted and decrypt it at the application
level only when needed for display on a customer information form or to
complete a business transaction.
However, a social security number is a different issue.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:81898E3B-37E1-4B90-9F83-2E522A29D064@.microsoft.com...
> Hello Everybody,
> I have a Encryption - Decription Question.
> In my project we are getting an XML File from a vendor which has a credit
> card number in clear text. We use XML bulk load process to load table from
> xml file.
> How can i encrypt credit card number while storing into table.
> Also i will have to decrypt CC number while i create comma separated file
> for another vendor ?
> Pls let me know.
> thx|||I have a few line sof VB.Net code that Encrypts or Decrypts a string passed
to it; If interested then let me know and I will post the code for you.
"mvp" wrote:

> Hello Everybody,
> I have a Encryption - Decription Question.
> In my project we are getting an XML File from a vendor which has a credit
> card number in clear text. We use XML bulk load process to load table from
> xml file.
> How can i encrypt credit card number while storing into table.
> Also i will have to decrypt CC number while i create comma separated file
> for another vendor ?
> Pls let me know.
> thx|||Thanks for the reply
But can i know how can i do this encryption-decryption..
thanks
"JT" wrote:

> A credit card number is typically not the type of attribute used for
> indexing, sorting, grouping, or composing a primary key, so I see no reaso
n
> why it would not be a good candidate for encryption. It would be reasonabl
e
> and fairly simple to store it encrypted and decrypt it at the application
> level only when needed for display on a customer information form or to
> complete a business transaction.
> However, a social security number is a different issue.
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:81898E3B-37E1-4B90-9F83-2E522A29D064@.microsoft.com...
>
>|||I want to read from XML file (which has CC number in clear text, i use sql
bulk load to load xml into table) and load into sql server table in encrypt
form and then decrypt again when i create comma seperated file from table
again to provide feed to another vendor ?
Let me know, do u have similar thing ?
"Shariq" wrote:
> I have a few line sof VB.Net code that Encrypts or Decrypts a string passe
d
> to it; If interested then let me know and I will post the code for you.
> "mvp" wrote:
>|||If your entire input and output processing is done with SQL server then I
haven't done similar thing but you could possibly use ActiveX script to
Encrypt/Decrypt.
In a similiar type of situation when I had to encrypt/decrypt for input and
output; I use VB.Net application to re-process XML file as input, encrypted
the Credit Card numbers and output to an XML file. Then I executed a DTS
package to upload the XML formatted output file to a SQL Server.
When sending data to a client with decrypted CC numbers; I also used the VB
application to rad data from the SQL Server and during the data read,
decrypted the CC numbers and produced XML file.
"mvp" wrote:
> I want to read from XML file (which has CC number in clear text, i use sq
l
> bulk load to load xml into table) and load into sql server table in encry
pt
> form and then decrypt again when i create comma seperated file from table
> again to provide feed to another vendor ?
> Let me know, do u have similar thing ?
> "Shariq" wrote:
>|||Hello Shariq,
I'm Interesting about the encryption/decryption code that you wrote in VB.NE
T.
Would you like to post it me?
Thank you!
"Shariq" wrote:
> If your entire input and output processing is done with SQL server then I
> haven't done similar thing but you could possibly use ActiveX script to
> Encrypt/Decrypt.
> In a similiar type of situation when I had to encrypt/decrypt for input an
d
> output; I use VB.Net application to re-process XML file as input, encrypte
d
> the Credit Card numbers and output to an XML file. Then I executed a DTS
> package to upload the XML formatted output file to a SQL Server.
> When sending data to a client with decrypted CC numbers; I also used the V
B
> application to rad data from the SQL Server and during the data read,
> decrypted the CC numbers and produced XML file.
> "mvp" wrote:
>|||If you are taking about decrypting data coming in from the source data or
encrypting data as it extract to another format, then this would be
implemented at the application level not the database level. It depends on
what application programming tool your are using.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:8470213F-47A3-417A-B920-BB7759AC0738@.microsoft.com...
> Thanks for the reply
> But can i know how can i do this encryption-decryption..
> thanks
> "JT" wrote:
>|||x-rays,
You might be able to better technique but the technique I use works great
for me.
This function will either encrypt or decrypt depanding on what is passed to
it.
Public Function MyCryption(ByVal strInput As String) As String
' Encrypts/decrypts the passed string using
' a simple ASCII value-swapping algorithm
Dim strTempChar As String, i As Integer
For i = 1 To Len(strInput)
If Asc(Mid$(strInput, i, 1)) < 128 Then
strTempChar = CType(Asc(Mid$(strInput, i, 1)) + 128, String)
ElseIf Asc(Mid$(strInput, i, 1)) > 128 Then
strTempChar = CType(Asc(Mid$(strInput, i, 1)) - 128, String)
End If
Mid$(strInput, i, 1) = Chr(CType(strTempChar, Integer))
Next i
Return strInput
End Function
"x-rays" wrote:
> Hello Shariq,
> I'm Interesting about the encryption/decryption code that you wrote in VB.
NET.
> Would you like to post it me?
> Thank you!
> "Shariq" wrote:
>|||Keep in mind that whatever encryption you end up using, the encrypted data
is only as secure as the code you use for the encryption. If your
encryption code and key are stored in a source control system, everyone with
access to that system will be able to decrypt the data, provided they have
access to the table where the data is stored.
If your programmers do not have access to the production database, and your
DBAs do not have access to your application code, then you should be ok.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:81898E3B-37E1-4B90-9F83-2E522A29D064@.microsoft.com...
> Hello Everybody,
> I have a Encryption - Decription Question.
> In my project we are getting an XML File from a vendor which has a credit
> card number in clear text. We use XML bulk load process to load table from
> xml file.
> How can i encrypt credit card number while storing into table.
> Also i will have to decrypt CC number while i create comma separated file
> for another vendor ?
> Pls let me know.
> thx