Solved

Encrypting data between SQL 2000 and VB.NET Client?

Posted on 2009-05-18
4
591 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
ID: 24410619
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
ID: 24410696
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
ID: 24430556
 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
ID: 31582524
RiteshShah - You can have the points seeing as you gave it a go.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Unable to upgrade to .Net 4.6.2 on Server 2012r2 2 42
Runtime Error 2 27
fso.FolderExists("\\server\HiddenFolder$") 4 46
ASP.NET 5 Templates 2 65
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 demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

930 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

14 Experts available now in Live!

Get 1:1 Help Now