My SQL Server CLR function is very slow

Question

I have created two methods in C#:

public static string Encrypt(string clearText, string encryptionKey)
{
        byte[] clearBytes = Encoding.Unicode.GetBytes(clearText);

        using (Aes encryptor = Aes.Create())
        {
            var pdb = new Rfc2898DeriveBytes(encryptionKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
            encryptor.Key = pdb.GetBytes(32);
            encryptor.IV = pdb.GetBytes(16);

            using (var ms = new MemoryStream())
            {
                using (var cs = new CryptoStream(ms, encryptor.CreateEncryptor(), CryptoStreamMode.Write))
                {
                    cs.Write(clearBytes, 0, clearBytes.Length);
                    cs.Close();
                }

                clearText = Convert.ToBase64String(ms.ToArray());
            }
        }

        return clearText;
    }

    public static string Decrypt(string cipherText, string encryptionKey)
    {
        try
        {
            byte[] cipherBytes = Convert.FromBase64String(cipherText);

            using (Aes encryptor = Aes.Create())
            {
                var pdb = new Rfc2898DeriveBytes(encryptionKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
                encryptor.Key = pdb.GetBytes(32);
                encryptor.IV = pdb.GetBytes(16);

                using (var ms = new MemoryStream())
                {
                    using (var cs = new CryptoStream(ms, encryptor.CreateDecryptor(), CryptoStreamMode.Write))
                    {
                        cs.Write(cipherBytes, 0, cipherBytes.Length);
                        cs.Close();
                    }

                    cipherText = Encoding.Unicode.GetString(ms.ToArray());
                }
            }
        }
        catch (Exception)
        {
        }

        return cipherText;
    }

By following the steps given in This Link, I have created a CLR function in SQL Server and I am trying to call it like:

SELECT dbo.Decrypt(MyEncrypted, EncryptionKey)
FROM   MyTable

The problem is, it is taking TOO MUCH TIME. Like for only 1000 rows, it took 1.5 minutes. If I call my query without the CLR function, it took less than 1 second.

Is there any thing I can do to improve the performance of the CLR functions?

can you use sql server's encryption? why are you saving it as a string when you could save the raw bytes? — Daniel A. White
– Daniel A. White, Commented Aug 16, 2015 at 11:28
What is the size of data in the "MyEncrypted" and "EncryptionKey" columns? How long does that code take when running in a C# console app with the exact same inputs? — Martin Smith
– Martin Smith, Commented Aug 16, 2015 at 12:16
@jdweng the OP is using this code as a CLR function, so it runs on the sqlserver, in the same process but in a separate app-domain, no firewalls are involved... — rene
– rene, Commented Aug 16, 2015 at 13:30

Community · Accepted Answer · 2017-05-23 12:29:43Z

1

I've analyzed your Decrypt method with the Performance Analyzer in VS2010 by running it 100 times:

As you can see the GetBytes method of the Rfc2898DeriveBytes instance take the most time.

I'm not sure why you have these specific encryption/decryption requirements but one way influence the time the GetBytes method takes is to instantiate the Rfc2898DeriveBytes using the constructor that takes iterations as the third parameter. Default it is on 1000, I can set it as low as 1. BUT THIS IS STRONGLY ADVISED AGAINST

var pdb = new Rfc2898DeriveBytes(encryptionKey, salt, 10);

This iterations does need to be the same for both the Encrypt and Decrypt so you'll have to Decrypt\Encrypt your current values if you want to change that.

One other option might be to cache the IV values as seems to recommended in this answer. I'm not enough of an expert if they talk there about using the same Key as well but if that is an option you might cache the call to GetBytes for the key as well.

All the described change have impact on how your data is encrypted and the strength of your encryption. Consider both impacts when testing the solutions.

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Aug 16, 2015 at 14:59

rene

42.6k78 gold badges122 silver badges166 bronze badges

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

6 Comments

Remus Rusanu Over a year ago

Reducing the number of iteration on key derivation functions has cryptographic consequences. Don't recommend it for perf gain.

rene Over a year ago

OK noted. and the caching part, store the IV in the table and read the KEY from a config file/ registry @RemusRusanu so you won't need the GetBytes calls on decrypt?

Remus Rusanu Over a year ago

... or use SQL Server built-in ENCRYPTBYPASSPHRASE

Usman Khalid Over a year ago

@rene Thanks for your effort but changing the algorithm is not an option for us.

Usman Khalid Over a year ago

@RemusRusanu Also database encryption is not an option. :(

|

Collectives™ on Stack Overflow

My SQL Server CLR function is very slow

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related