Gweep
asked on
Encrypting data between SQL 2000 and VB.NET Client?
Hi Experts,
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
Thanks Experts!
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
Thanks Experts!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, I've actually managed to do what i needed ...
The following article was the greatest help:
http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/cf9e7022-47aa-40ee-85b5-525419a14e6a
I based my code on the above and wrote a dirty stored procedure to grab the public key using the following:
CREATE ASYMMETRIC KEY MY_KEY
WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = 'password1@'
SELECT public_key FROM sys.asymmetric_keys WHERE [name] = '" & Name & "'
After i had the key (in byte array form) i then used the following VB code:
'Get the public key from SQL
Dim PublicKey() As Byte
PublicKey = SQL_RSA_Encryption.GetSQLA
'Mark the key as encryptable
PublicKey(5) = PublicKey(5) Or &H80
Dim RSA As New Security.Cryptography.RSAC
'import the public key blob
RSA.ImportCspBlob(PublicKe
'Encoder
Dim encoder As New System.Text.UTF8Encoding
'encrypt the data
Dim sData As String = "testing"
Dim bytData() As Byte = encoder.GetBytes(sData)
Dim EncryptedData As Byte() = RSA.Encrypt(bytData, False)
'change the byte order to accommodate CLR quirk
Array.Reverse(EncryptedDat
Dim s As String = SQL_RSA_Encryption.Test(En
s = s
The Test procedure on the last line basically runs a SP scaler passing in the EncryptedData as a varbinary(max) and runs the following SP:
ALTER PROCEDURE [DBO]._SP_TEST
@BYT VARBINARY(MAX)
AS
SELECT DecryptByAsymKey(AsymKey_I
As i write this, the SQL successfully unencrypted the data and passed it back. Its only my test code and i'm going to tidy it all up now that i've got it working, but its enough to show people how it all works.
Remember this should only be used for passing data TO sql as the public key is freely available in the SQL database. The next thing i'm working on is how to retrieve data from SQL which will mean submitting the clients public key to a SQL stored procedure and using it to encrpt data and then using the clients private key to decrypt when the data is returned.
Hope this helps someone!
The following article was the greatest help:
http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/cf9e7022-47aa-40ee-85b5-525419a14e6a
I based my code on the above and wrote a dirty stored procedure to grab the public key using the following:
CREATE ASYMMETRIC KEY MY_KEY
WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = 'password1@'
SELECT public_key FROM sys.asymmetric_keys WHERE [name] = '" & Name & "'
After i had the key (in byte array form) i then used the following VB code:
'Get the public key from SQL
Dim PublicKey() As Byte
PublicKey = SQL_RSA_Encryption.GetSQLA
'Mark the key as encryptable
PublicKey(5) = PublicKey(5) Or &H80
Dim RSA As New Security.Cryptography.RSAC
'import the public key blob
RSA.ImportCspBlob(PublicKe
'Encoder
Dim encoder As New System.Text.UTF8Encoding
'encrypt the data
Dim sData As String = "testing"
Dim bytData() As Byte = encoder.GetBytes(sData)
Dim EncryptedData As Byte() = RSA.Encrypt(bytData, False)
'change the byte order to accommodate CLR quirk
Array.Reverse(EncryptedDat
Dim s As String = SQL_RSA_Encryption.Test(En
s = s
The Test procedure on the last line basically runs a SP scaler passing in the EncryptedData as a varbinary(max) and runs the following SP:
ALTER PROCEDURE [DBO]._SP_TEST
@BYT VARBINARY(MAX)
AS
SELECT DecryptByAsymKey(AsymKey_I
As i write this, the SQL successfully unencrypted the data and passed it back. Its only my test code and i'm going to tidy it all up now that i've got it working, but its enough to show people how it all works.
Remember this should only be used for passing data TO sql as the public key is freely available in the SQL database. The next thing i'm working on is how to retrieve data from SQL which will mean submitting the clients public key to a SQL stored procedure and using it to encrpt data and then using the clients private key to decrypt when the data is returned.
Hope this helps someone!
ASKER
RiteshShah - You can have the points seeing as you gave it a go.
ASKER
The problem i am having is i want to encrypt asymmetrically WITHOUT using the stored keys on the SQL database because the client will not have access to them.