Encryption and Security with C# and SQL

Question

I have seen the examples but I'm hoping to run this by other programmers. For encryption within my window forms app, I am generating two random numbers and saving them in an SQL Server table like thus:

       OPEN SYMMETRIC KEY SymmetricKeyName DECRYPTION BY CERTIFICATE CertificateName;

       insert into keyfile(encrypted_key1, encrypted_key2) values
       (EncryptByKey(Key_GUID('SymmetricKeyName'), **Key1**),
             EncryptByKey(Key_GUID('SymmetricKeyName'), **Key2**))

Then I am using the keys to encrypt a file using AES-256 as follows:

        var key = new Rfc2898DeriveBytes(**Key1, Key2**, 1000);
        RijndaelManaged AES = new RijndaelManaged();

        AES.KeySize = 256;
        AES.BlockSize = 128;

        AES.Key = key.GetBytes(AES.KeySize / 8);
        AES.IV = key.GetBytes(AES.BlockSize / 8);
        AES.Padding = PaddingMode.Zeros;

        AES.Mode = CipherMode.CBC;

        using (var output = File.Create(outputFile))
        {
            using (var crypto = new CryptoStream(output, AES.CreateEncryptor(), CryptoStreamMode.Write))
            {
                using (var input = File.OpenRead(inputFile))
                {
                    input.CopyTo(crypto);
                }
            }
        }

        etc.

In order to perform decryption both keys that were used to encrypt the file are required. Decryption is possible through software requiring two authenticated users. The keys change every day. The data and the database are sufficiently physically secure. The key table is in a separate database from the certificate.

The question is: Does this secure the data enough to not be readily be decrypted and, if now, why not and what changes might you suggest?

Answer 1

The problem here is that there is a high probability that anyone that is able to obtain the file is also able to obtain the data in the database (which includes the key). Once the data is compromised, it doesn't matter how often you change the key since the attacker would have a copy of the file encrypted with the key that matches it.

Common solutions to this problem are to use an external Hardware Security Module or something like a TPM.

Here is a very useful and related post that enumerates several options.

Answer 2

As suggested by others, you can store the key on a USB, alternatively a network share. However if on a network share you might need to change the Service Logon to an account with access to the network share.

Answer 3

"SYMMETRIC KEY" might be an issue here. They are similar to a xor operation, the same key works to both encrypt and decrypt.

For a 'cooler' method, use ASYMMETRIC keys instead, then the database can keep the 'how to encrypt' half, while your application can have the 'how to decrypt' half. It's a lot of effort, but "not even the DBAs can see the secret data" is a cool feature.