We have been using the AlwaysEncrypted feature of Microsoft SQL for over a year now. Until now, it has worked flawlessly. Previously, we had only a very few columns encrypted (4). A week ago, we implemented encryption on another 11 columns, so we still make very light use of it.
Unfortunately, some of our "INSERT" commands on tables with encrypted columns, are failing, but only intermittently. The commands are being run by a web server (IIS server). I can run the command, have it fail, and immediately after the exception is thrown, run the same command, in the same process (the web server has simply handled the exception) and it succeeds.
The server binary is written in c#, and the commands are absolutely trivial. For example, execution of this command fails:
INSERT INTO [dbo].[someTable] ( [COL1], [COL2], [COL3] ) VALUES ( @COL1_0, @COL2_0, @COL3_0 )
where all three columns (COL1, COL2 and COL3) are encrypted, and [someTable] has only these three columns, all varchar(64), plus an integer identity column.
c# code (paraphrased) is:
SqlCommand cmd = new SqlCommand();
cmd.Connection = GetOpenConnection("Data Source=REDACTED;Integrated Security=SSPI;Column Encryption Setting=enabled;Initial Catalog=PRODUCTION");
cmd.CommandText = "INSERT INTO [dbo].[someTable] ( [COL1], [COL2], [COL3] ) VALUES ( @COL1_0, @COL2_0, @COL3_0 )";
cmd.CommandType = CommandType.Text;
cmd.Transaction = m_Transaction;
foreach(string[] paramData in inputParamsData) //3 of these
{
//@COLx_0
SqlParameter param = new SqlParameter(paramData[0], SqlDbType.VarChar, 64);
param.Value = paramData[1];
cmd.Parameters.Add(param);
}
string msg = "Preparing";
try
{
cmd.Prepare();
msg = "Executing";
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
if (ex.Message.Contains("Operand type clash"))
{
log.Error($"{msg} non-query", ex);
log.Error("Parameters in command");
foreach (var obj in command.Parameters)
{
SqlParameter param = obj as SqlParameter;
if (param != null)
{
log.ErrorFormat("Param name: {0}, length: {1}", param.ParameterName, param.Size);
}
}
}
throw;
}
Execution fails on ExecuteNonQuery() (although the exception message text says that it is unable to "Prepare" the query, examination of the logging messages makes it clear that the Prepare succeeds and the ExecuteNonQuery fails.)
The logging we get is:
2019-02-21 23:15:43,046 ERROR [15:xxx.DbConnectionWrapper`6] - Executing non-query
System.Data.SqlClient.SqlException (0x80131904): Operand type clash: varchar is incompatible with varchar(64) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'ColumnKey_20180605_123456', column_encryption_key_database_name = 'Production') collation_name = 'Latin1_General_BIN2'
Statement(s) could not be prepared.
...
Error Number:206,State:2,Class:16
2019-02-21 23:15:43,067 ERROR [15:xxx.DbConnectionWrapper`6] - Parameters in command
2019-02-21 23:15:43,069 ERROR [15:xxx.DbConnectionWrapper`6] - Param name: @FIGI_0, length: 64
One can see that the parameter size has indeed been set correctly. The only column that is incrypted it the "FIGI" column, which is a varchar(64) column, and the parameter appears to be correctly set.
We have a Microsoft SQL Server Enterprise (64-bit), version 13.0.5026.0. IIS server has .NET 4.6.1.
Has anyone experienced intermittent failures like this? Does anyone have any pointers where to look to find the problem?
I note that the class SqlParameter has a property ForceColumnEncryption, but there is very little documentation to indicate when, where or how this parameter should be used. Can anyone explain its use or point me to decent documentation?
Edit: it turns out the intermittent nature is worse that this. It fails only about once a week. Given comments below regarding parameter size, I added in some debug logging when it fails, with results as above.
