?
Solved

Encrypting data between SQL 2000 and VB.NET Client?

Posted on 2009-05-18
4
Medium Priority
?
623 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 31

Accepted Solution

by:
RiteshShah earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

762 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