Entity Framework - Always Encrypted - AzureKeyVault

Question

I've used AzureKeyVault to encrypt some Social Security # columns in SQL. These column definitions are varchar(11) NULL.

My models in my code have the following attributes:

    [StringLength(11)]
    [Column(TypeName = "varchar")]
    [RegularExpression(RegExValidators.SSNRegex, ErrorMessage = "SSN must be a number")]
    public string SSN { get; set; }

However, I'll occasionally see this error in my database logs:

System.Data.SqlClient.SqlException: Operand type clash: varchar is incompatible with varchar(11) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto2', column_encryption_key_database_name = 'DB NAME') collation_name = 'Latin1_General_BIN2'
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

The odd thing is that this doesn't ALWAYS happen... just every once in a while. I run this code in my Global.asax Application_Start() function:

public static class AzureKeyVaultInit
    {
        private static string _clientId = ConfigurationManager.AppSettings["AzureKeyVaultAppClientId"];
        private static string _clientSecret = ConfigurationManager.AppSettings["AzureKeyVaultAppSecret"];
        private static ClientCredential _clientCredential;
        private static bool _isInitialized = false;
        private static readonly object _isInitializedLock = new object();

        public static void InitializeAzureKeyVaultProvider()
        {

            if (string.IsNullOrEmpty(_clientId)) return;

            lock (_isInitializedLock)
            {
                if (!_isInitialized)
                {
                    _clientCredential = new ClientCredential(_clientId, _clientSecret);

                    SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);

                    Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers = new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();
                    providers.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider);

                    SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);

                    _isInitialized = true;

                    Core.Log.Info($"Initialized Azure Key Vault");
                }
            }
        }

Anything glaring on why I'd get this error every once in a while?

see [this](https://stackoverflow.com/questions/45103280/insert-into-table-via-stored-procedure-from-client-application-using-always-encr) and [this](https://stackoverflow.com/questions/40266502/operand-type-clash-varchar-is-incompatible-with-varchar50-trying-to-insert-in) — Jayendran, Dec 19 '18 at 11:19

score 0 · Answer 1 · answered Dec 19 '18 at 08:42

Any value that targets an encrypted column needs to be encrypted inside the application. An attempt to insert/modify or to filter by a plaintext value on an encrypted column will result in an error like you.

To prevent such errors, make sure:

1.Always Encrypted is enabled for application queries targeting encrypted columns (Set Column Encryption Setting=enabled in the connection string or in the SqlCommand object for a specific query).

2.Use SqlParameter to send data targeting encrypted columns.

For more details, you could refer to this article.

Thanks, I do have the Column Encryption Setting=enabled in the connection string. I'm currently using Entity Framework which I thought paramertized queries automatically... but perhaps I'm mistaken. — mint, Dec 19 '18 at 14:15

Entity Framework - Always Encrypted - AzureKeyVault

1 Answers1