SQL Server Encryption/Decryption with C#

Question

I am working on a project and the next thing I want to learn is about adding sensitive data, and encrypting/decrypting it.

I Have done the basics on this already with the below code (Using SQL SERVER):

--STEP 1, CREATE DATABASE MASTER KEY
USE DatabaseTest
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD='securepassword'
GO

--STEP 2, CREATE A CERTIFICATE
CREATE CERTIFICATE creditcardcert WITH SUBJECT='creditcardcert'
GO

--STEP 3, CREATE SYMMETRIC KEY
CREATE SYMMETRIC KEY CCreditCard_KEY WITH ALGORITHM = triple_des ENCRYPTION BY CERTIFICATE creditcardcert
GO

--STEP 4, CREATE CCEncrypted ENCRYPTED COLUMN
ALTER TABLE Person ADD CCEncrypted varbinary(128)
GO

--STEP 5, ENCRYPT DATA
OPEN SYMMETRIC KEY CCreditCard_KEY 
DECRYPTION BY CERTIFICATE creditcardcert

UPDATE Person SET CCEncrypted = ENCRYPTBYKEY(KEY_GUID('CCreditCard_KEY'), CreditCardNumber)

CLOSE SYMMETRIC KEY CCreditCard_KEY
GO

Now I know I can use this to encrypt the data, and I have a query to decrypt it also with SQL Server, however I need to do this at C# level when the data is being added.

For example I have a C# Windows Form which can accept a Firstname, Surname, CredCardNumber). This data when added is saved into an SQL Server table, my question now is how can I encrypt this data as it is saved, and if I want to view it, can I decrypt it at the push of a button (for now)?

Thanks for any help

--IN ADDITION im a student doing my Final Year Project so using services like that would lower my marks for the programming considerably (although good for the research side) Essentially I want to be able to store the certain data in the SQL Server table as encrypted, so unless I write a decrypt statement i cannot see it in SQL Server. But as the application requires a password anyway and will have permission levels, then is there an alternate solution so I can still see/add/edit this data not encrypted in my application? if you get me?

and an example of adding the data in C#:

System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection("SomeConnectionString")

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandText = "Insert Into Person(Firstname, Surname, CCNumber) Values (@Firstname, @Surname, @CCNumber)";
cmd.Connection = cn;

cmd.Parameters.AddWithValue("@Firstname", txtFirstname.Text);
cmd.Parameters.AddWithValue("@Surname", txtSurname.Text);
cmd.Parameters.AddWithValue("@CCNumber", txtCCNumber.Text); //ENCRYPT ME
try
{
   cn.Open();
   cmd.ExecuteNonQuery();
   lblStatus.Text = "Item Inserted";
}
catch(Exception ex)
{
   lblStatus.Text = ex.ToString();
}
finally
{
   cn.Close(); 
}

Can you add the code how you get/save the data? That would help in regards to putitng up a helpful post that takes your exact c# situation into account. — Thomas, Oct 29 '15 at 09:43
Tricky to do without sending it to the database, if thats what you mean? — Matt Evans, Oct 29 '15 at 09:46
i better write it then ;) I have done this before so wont take to long — Crezzer7, Oct 29 '15 at 09:47
well i can add the data, then encrypt it, but then the application cannot see any of the data.. what i want ideally is for the data to be encrypted in sql server but still visible within an application (which eventually would be password protected) — Crezzer7, Oct 29 '15 at 09:48
So in essence its not the c# based (the problem). But instead you want every select onto the database return the decrypted data intead of the encrypted while every update, insert uses the encrypted data? — Thomas, Oct 29 '15 at 09:52
Salt the card number: Append a string of characters from RNGCryptoServiceProvider to it before you encrypt it. It's far too easy for cyberthieves to brute-force decrypt 16-decimal-digit strings. Better yet, consider working with a payment processor like Stripe.com which gets you out of the business of having these card numbers in your system. PCI compliance is expensive and hard to get. — O. Jones, Oct 29 '15 at 09:52
How about you put your encryption logic in your application like using a class and whenever you need the data you can always decrypt — Prince Jea, Oct 30 '15 at 06:00
thats what I have gone for in JS, ill add it as an answer now — Crezzer7, Oct 30 '15 at 08:01

score 0 · Accepted Answer · answered Oct 30 '15 at 08:09

I took a different approach as this was becoming to complicated encoding and decoding all of the time and it would have slowed my system down. Instead I have gone for a JavaScript approach on the front end, and with the use of CryptoJS

https://code.google.com/p/crypto-js/

I have managed to encrypt my data using this code snippet below taken from the above link:

DES, Triple DES DES is a previously dominant algorithm for encryption, and was published as an official Federal Information Processing Standard (FIPS). DES is now considered to be insecure due to the small key size.

default.html code

<script src="http://crypto-js.googlecode.com/svn/tags/3.1.2/build/rollups/tripledes.js"></script>

screen code

var encrypted = CryptoJS.DES.encrypt("Message", "Secret Passphrase");
var decrypted = CryptoJS.DES.decrypt(encrypted, "Secret Passphrase");

Triple DES applies DES three times to each block to increase the key size. The algorithm is believed to be secure in this form.

default.htm file

<script src="http://crypto-js.googlecode.com/svn/tags/3.1.2/build/rollups/tripledes.js"></script>

screen created code

var encrypted = CryptoJS.TripleDES.encrypt("Message", "Secret Passphrase");

var decrypted = CryptoJS.TripleDES.decrypt(encrypted, "Secret Passphrase");

I have managed to save the data as encrypted, and then on the view, edit and browse screens, decrypt it either on a user permissions base, or it can be done on for all data in the application. the code is therefore safe (as can be) in the database and without the encryption key ("Secret Passphrase") in the example above, it cannot be decrypted

SQL Server Encryption/Decryption with C#

1 Answers1