I am trying to work out how to implement asymmetric encryption between my client and database when transferring sensitive data.
I'll give an example of my problem:
I have a table full of credit card numbers called Tbl_CreditCardNumbers with a columns called [number]. This columns has Unencrypted creditcard numbers inside it.
The first thing i am going to do is encrypt these numbers via 'EncryptByPassPhrase' function in SQL, every time i want to search or do anything with these numbers the CLIENT will pass the pass phrase through into the stored procedure so i don't have to store the passphase in the database to enable searching by credit card number etc.
When i have found the creditcard number the stored procedure needs to return details about, i want to encrypt the data that i return and decrypt it at the clientside using asymmetric encryption.
This is the bit that is confusing me ... from my understanding the way this should work is that the client application should pass its public key to the store procedure, the stored proc should then use this key to encrypt the data ... Then when the data is returned, the client app will decrypt the data using its private key.
The only problem is i can't figure out a way to do this at the database end? Or for that matter find anything at all on the web regarding encrypting data in transit?!
Can anyone show me examples or provide me a method to do this?
By the way, i CANNOT use certificates as the client does not want to use them