Avatar of rito1
rito1
 asked on

Encrypt/decrypt string data stored within sql server 2008

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
ASP.NETC#Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Robert Schutt

8/22/2022 - Mon
SOLUTION
Imran Javed Zia

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
rito1

ASKER
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
Imran Javed Zia

then you may need encription and decription on application level rather than database level.
rito1

ASKER
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SOLUTION
Anthony Perkins

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
rito1

ASKER
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
rito1

ASKER
... 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.
Anthony Perkins

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Robert Schutt

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...
Robert Schutt

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...
ASKER CERTIFIED SOLUTION
Robert Schutt

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
rito1

ASKER
Thanks Robert, I am checking out that article now.

Yours and everyone's help is much appreciate

Rit
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
rito1

ASKER
Thanks Robert, this is great. I will implement AES and ask the end user for the password to use.
Robert Schutt

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.