Corrupted Data Using Always Encrypted In Sql Server

Once we started to write data into an encrypted table, we noticed problems once we tried to read encrypted data. Symptoms are identical to the ones described HERE Here is a little

Solution 1:

UPDATE: Initially, I made a wrong judgment due to inaccuracy in test results. I will cross out wrong facts but will leave them here for historical purposes.

---

After a week of bloody debugging and testing, I came to a conclusion that the root of that behavior is somewhere inside of RSACryptoServiceProvider class in .NET Framework.

Facts which make me think that way:

• My app is configured to recycle every hour and I noticed that data corruption happens 1 out of 10 times after app restart; If app started to write corrupted data then it was doing it permanently until app is restarted again (or recycled)
• I used reflection in order to see the internals of objects, involved in AlwaysEncrypted feature:
  • Inside of "System.Data.SqlClient.SqlSymmetricKeyCache" I was watching for result of Column Key decryption ( private field _cache of another private static field _singletonInstance)
  • I replaced default implementation of SqlColumnEncryptionCertificateStoreProvider in order to log all requests to decrypt column encryption key
• I waited for another data corruption to happen and then looked into my patched provider and cache of decrypted keys. I discovered that decrypted column key, return by SqlColumnEncryptionCertificateStoreProvider was 0x0000000000000000... correct, but in cache appeared to be corrupted (0x0000000000000000...)

I also found this ARTICLE, which makes me think that high-loaded ASP.NET apps may have issues with RSACryptoServiceProvider class, once it is used in the multi-threaded environment. And that's exactly my case and SqlColumnEncryptionCertificateStoreProvider doesn't have any thread synchronization mechanism to avoid the problem, which happens inside of RSACryptoServiceProvider.

After looking at the source code of Always Encrypted related classes I found only ONE PLACE, where decrypted column key is used.

// Decrypt the CEK// We will simply bubble up the exception from the DecryptColumnEncryptionKey function.byte[] plaintextKey;
try {
    plaintextKey = provider.DecryptColumnEncryptionKey(keyInfo.keyPath, keyInfo.algorithmName, keyInfo.encryptedKey);
}
catch (Exception e) {
    // Generate a new exception and throw.stringkeyHex= SqlSecurityUtility.GetBytesAsString(keyInfo.encryptedKey, fLast: true, countOfBytes: 10);
    throw SQL.KeyDecryptionFailed(keyInfo.keyStoreName, keyHex, e);
}

encryptionKey = newSqlClientSymmetricKey(plaintextKey);

// If the cache TTL is zero, don't even bother inserting to the cache.if (SqlConnection.ColumnEncryptionKeyCacheTtl != TimeSpan.Zero) {
    // In case multiple threads reach here at the same time, the first one wins.// The allocated memory will be reclaimed by Garbage Collector.DateTimeOffsetexpirationTime= DateTimeOffset.UtcNow.Add(SqlConnection.ColumnEncryptionKeyCacheTtl);
    _cache.Add(cacheLookupKey, encryptionKey, expirationTime);
}

plaintextKey value is 100% correct, as I'm logging it before returning it from DecryptColumnEncryptionKey() method.

Key corruption inside of encryptionKey = new SqlClientSymmetricKey(plaintextKey) is very unlikely, as SqlClientSymmetricKey is a simple wrapper around a byte array.

Key corruption inside of _cache.Add(cacheLookupKey, encryptionKey, expirationTime) is also seems to me very unlikely.

That leaves me with only one logical explanation of how this happens. As byte array (our decrypted key) is passed everywhere as a reference, under particular circumstances, consumer of that key screws up the byte values in the array. But unfortunately, I can't find any place in code to prove that theory.

Workaround. Once I added simple read from an encrypted table before my app starts serving requests (inside of Global.asax), then the problem has gone away. Basically, the trick helps me to guarantee that only one non-concurrent read of data from DB is triggering column key decryption and SqlSymmetricKeyCache initialization.

Will be glad to hear some comments from Microsoft team on that very strange behavior.