Solved

Encrypt/decrypt string data stored within sql server 2008

Posted on 2012-12-29
14
2,403 Views
Last Modified: 2013-01-01
Hi All,

I am pretty new to encryption, as to date I have only had to hash password data without the threat of it being decrypted.

I have done some top level research and understand that there are different algorithms available including both symmetric and asymmetric, and that the .net framework provide excellent security classes.

Within my current solution I have been asked to encrypt only certain table columns within sql server so that the data can only be decrypted by the end user when requesting the data from the asp.net web forms interface.

Can anyone share with me examples or helper class that will provide a sound encryption method that will stop even the nosey web developer from decrypting an end users data?

Should I be encrypting/decrypting using sql server or is it better to using C#?

Thanks in advance for you help and happy new year to you all!

Rit
0
Comment
Question by:rito1
[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
  • 6
  • 4
  • 2
  • +1
14 Comments
 
LVL 16

Assisted Solution

by:Imran Javed Zia
Imran Javed Zia earned 100 total points
ID: 38729076
Hi,
 you can use both ways: dot net encription/decryption or database column level. Practically i have not used sql server encription, but it is said that sql server encription should be prefered ove dot net encription but you may find following articals helpful:
http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/
http://www.databasejournal.com/features/mssql/article.php/3922881/Column-Level-Encryption-in-SQL-Server.htm
http://stackoverflow.com/questions/5247797/sql-server-vs-net-encryption
Thanks
0
 
LVL 1

Author Comment

by:rito1
ID: 38729111
Thanks IJZ for a quick response. It's much appreciated.  

The issue I see with your examples is that the developer would be able to use the keys to decrypt the data as the developer would have created them. In my solution it is vital that the developer would be able to decrypt the data. The data should only be decrypted by the end user it belongs to.

Thanks,

Rit
0
 
LVL 16

Expert Comment

by:Imran Javed Zia
ID: 38729146
then you may need encription and decription on application level rather than database level.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 1

Author Comment

by:rito1
ID: 38729343
Thanks IJZ, do you or anyone have examples of encrypting/decrypting using C# with user defined keys that will fulfil my requirements of the developer not setting or knowing the key?

Thanks,

Rit
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 38730058
The question you need to ask yourself is as follows:
Are the columns that require encryption indexed for searching (used in JOIN and/or WHERE clause).  If the answer is yes, then SQL Server's column encryption is not a good fit.  

Also, keep in mind that if you have to encrypt the data stored, then you will have to re-design your database in order to use wider varbinary columns instead of for example varchar columns.
0
 
LVL 1

Author Comment

by:rito1
ID: 38731361
Thanks acperkins, I have considered this to a certain degree in my design. The columns are not used within joins or indexes.

What I am struggling to find is a sound solution that would stop anyone including myself from decrypting the users data. It's really important that only the end user can encrypt/decrypt their own data.

Can you share with my any examples of this type of solution?

Thank you

Rit
0
 
LVL 1

Author Comment

by:rito1
ID: 38731437
... Would it be unusual for the end user to supply a user defined key to encrypt with as part of the save process?... They would then need to supply the same key when retrieving the data too.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38731692
I suspect that would be the way to go about it, however I have no experience there, so I am going to leave this to smarter members to help you out.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38732361
Sounds to me like you could use AES. I haven't tried to implement it but there is a (rather old but) quite elaborate explanation with C# source code available here: http://msdn.microsoft.com/en-us/magazine/cc164055.aspx 

It may be a bit overwhelming (as the author of the article also states) but skimming the article it seems to me you can just add the Aes class file to your project and then use:
Aes a = new Aes(the key size, the seed key);
a.Cipher(plainText, cipherText);

Open in new window

which would be the content to be encrypted and the user supplied password. The arguments for the constructor are explained in the article but I haven't gone that deep yet...
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38732366
Ah, looking at the source I found a glitch in my story; the cipherText is the output which leaves me wondering how to set a password or if that's even possible the way I thought it would be...
0
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 300 total points
ID: 38733103
I made a number of adjustments to the project I found on the page I posted earlier. Not sure if this is the way you want to go, but I made it now so I better post it ;-)
captureThe picture shows a 4th textBox I added for the password as the original code used an all-zeroes password. I also borrowed the idea from a question I worked on earlier to convert the encrypted string to Base64 to make sure it shows without problem in a textBox/web page. That may be overkill when you just want to store the value in a database field. The new code:
private void button1_Click(object sender, System.EventArgs e) {
    byte[] cipherText = Encoding.Unicode.GetBytes(textBox1.Text.PadRight(textBox1.Text.Length + 7 - ((textBox1.Text.Length + 7) % 8), ' '));
    byte[] cipherTextOut = new byte[cipherText.Length];
    byte[] cipherText16 = new byte[16];
    byte[] cipherTextOut16 = new byte[16];
    byte[] passwordBytes = Encoding.Unicode.GetBytes(textBox4.Text.PadRight(8, ' '));
    AesLib.Aes a = new Aes(radioButton1.Checked ? Aes.KeySize.Bits128 : radioButton2.Checked ? Aes.KeySize.Bits192 : Aes.KeySize.Bits256, passwordBytes);
    for (int i = 0; i < cipherText.Length; i += 16) {
        System.Buffer.BlockCopy(cipherText, i, cipherText16, 0, 16);
        a.Cipher(cipherText16, cipherTextOut16);
        System.Buffer.BlockCopy(cipherTextOut16, 0, cipherTextOut, i, 16);
    }
    textBox2.Text = Convert.ToBase64String(cipherTextOut);
}

private void button2_Click(object sender, System.EventArgs e) {
    byte[] cipherText = Convert.FromBase64String(textBox2.Text);
    byte[] decipheredText = new byte[cipherText.Length]; // should always be a multiple of 16
    System.Diagnostics.Debug.Print(cipherText.Length.ToString());
    byte[] cipherText16 = new byte[16];
    byte[] decipheredText16 = new byte[16];
    byte[] passwordBytes = Encoding.Unicode.GetBytes(textBox4.Text.PadRight(8, ' '));
    AesLib.Aes a = new Aes(radioButton1.Checked ? Aes.KeySize.Bits128 : radioButton2.Checked ? Aes.KeySize.Bits192 : Aes.KeySize.Bits256, passwordBytes);
    for (int i = 0; i < cipherText.Length; i += 16) {
        System.Buffer.BlockCopy(cipherText, i, cipherText16, 0, 16);
        a.InvCipher(cipherText16, decipheredText16);
        System.Buffer.BlockCopy(decipheredText16, 0, decipheredText, i, 16);
    }
    textBox3.Text = Encoding.Unicode.GetString(decipheredText).TrimEnd();
}

Open in new window

0
 
LVL 1

Author Comment

by:rito1
ID: 38734404
Thanks Robert, I am checking out that article now.

Yours and everyone's help is much appreciate

Rit
0
 
LVL 1

Author Closing Comment

by:rito1
ID: 38734942
Thanks Robert, this is great. I will implement AES and ask the end user for the password to use.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38734958
Ok, great, be aware that as far as I can tell what I did with the text, process per 16 byte block, doesn't work for the password, so that's always going to be max 8 (unicode) characters.
0

Featured Post

Technology Partners: 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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

615 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