Solved

Encrypting data between SQL 2000 and VB.NET Client?

Posted on 2009-05-18
4
590 Views
Last Modified: 2013-11-07
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!
0
Comment
Question by:Gweep
  • 3
4 Comments
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
Comment Utility
there is a good example of ASYMMETRIC key, SYMMETRIC key and certificate in below link. have a look.


http://www.databasejournal.com/features/mssql/article.php/3483931/SQL-Server-2005-Security---Part-3-Encryption.htm
0
 

Author Comment

by:Gweep
Comment Utility
Thanks for the link, but like all the other articles i've read, it doesn't explain how to encrypt using the CLIENT's public key and pass the data back.

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.
0
 

Author Comment

by:Gweep
Comment Utility
 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.GetSQLAsymmetricPublicKey("MY_KEY")

            'Mark the key as encryptable
            PublicKey(5) = PublicKey(5) Or &H80

            Dim RSA As New Security.Cryptography.RSACryptoServiceProvider()

            'import the public key blob
            RSA.ImportCspBlob(PublicKey)

            '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(EncryptedData)

            Dim s As String = SQL_RSA_Encryption.Test(EncryptedData)
            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_ID('MY_KEY') , @BYT , N'password1@') as msg



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!
0
 

Author Closing Comment

by:Gweep
Comment Utility
RiteshShah - You can have the points seeing as you gave it a go.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now