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?

Luke · Accepted Answer · 2018-03-16 19:19:42Z

5

+50

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.

answered Mar 16, 2018 at 19:19

Luke

3,8724 gold badges33 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Missy Over a year ago

Thank you for your feedback. The key table and the certificates are in two different databases. Is that a better solution?

Luke Over a year ago

@Missy If the 2 databases share accounts/passwords it wouldn't be any better. If they have different credentials, that would be slightly better. You may want to take a serious look at using a TPM or a USB device. I really believe that would be the right way to solve your problem.

Neil Weicher · Accepted Answer · 2018-03-19 14:31:59Z

4

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.

edited Mar 19, 2018 at 14:31

answered Mar 18, 2018 at 20:33

Neil Weicher

2,5327 gold badges39 silver badges57 bronze badges

2 Comments

Neil Weicher Over a year ago

If you decide to use a network share, make sure to specify it by UNC not by drive letter, since the Service will not have access to that drive mapping.

Luke Over a year ago

Putting the key on a network share wouldn't be any more secure than putting it in a database on another server.

Dave · Accepted Answer · 2018-03-23 11:48:43Z

1

"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.

answered Mar 23, 2018 at 11:48

Dave

111 bronze badge

Collectives™ on Stack Overflow

Encryption and Security with C# and SQL

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related