I set up a SQL 2016 always encrypted database with various columns that are encrypted. I am able to successfully insert, update and read values using c# ADO.NET through the use of SqlParameters that specify the exact type, precision and length for each parameter.
e.g.:
Not explicitly specifying the precision and scale of the parameter would cause the sql update to fail, due to the column level encryption requiring this level of specificity.
Problem:
While I am able to read the encrypted data and display it on a data grid, using a SqlDataSource I am unable to update values back to the database.
I can see that this is because of a data-type miss-match between the parameter value and the database type:
Uncaught Error: Sys.WebForms.PageRequestManagerServerErrorException: Operand type clash: decimal(1,0) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'HRDBCEK1',
column_encryption_key_database_name = 'HRDB') is incompatible with decimal(18,2) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'HRDBCEK1', column_encryption_key_database_name
= 'HRDB') Statement(s) could not be prepared.
I believe this to be the case because SqlDataSource.UpdateParameters implements a Parameter collection (not SqlParameter).
System.Web.UI.WebControls.Parameter does not have a property for "scale". Only "DBType" and "Size".
When I switch the DBType from "Decimal" to "String", I can indeed see the "decimal(1,0)" portion of the above exception change to "nvarchar(1)".
Specifying the Size as "18" does not help.
Is there any way to get these updates to work via the use of a SqlDataSource?
My only option seems to be to intercept the OOTB batchEdit event of the grid, cancel the action and to write a complete manual implementation of the update using a SqlCommand object, which does support the SqlParameter object where I am allowed to define
the "scale".
All that I see in your example is the defining and setting of a parameter value. It doesn't appear you have specified the parameter type or scale/precision, which is the issue at hand.
-Thank you for the response though!
Due to time constraints, I ended up removing the UpdateParameters from the SqlDataSource, intercepted the updating of the DataGrid and replaced it with my own implementation that utilizes a SqlCommand instead.
For sanity reasons it would still be nice to find out if there is a way this could have been achieved with a SqlDataSource. I was under the impression that it should fully support all aspects of SQL Always Encrypted.
None
0 Points
2 Posts
Decimal Scale in asp:parameter
Apr 19, 2018 06:20 PM|TMK___|LINK
Background:
I set up a SQL 2016 always encrypted database with various columns that are encrypted. I am able to successfully insert, update and read values using c# ADO.NET through the use of SqlParameters that specify the exact type, precision and length for each parameter. e.g.:
SqlParameter paramAnnualSalaryLocal = cmd.CreateParameter(); paramAnnualSalaryLocal.ParameterName = @"@AnnualSalaryLocal"; paramAnnualSalaryLocal.DbType = DbType.Decimal; paramAnnualSalaryLocal.Direction = ParameterDirection.Input; paramAnnualSalaryLocal.Value = HREmployeeRow["AnnualSalaryLocal"]; paramAnnualSalaryLocal.Precision = 18; paramAnnualSalaryLocal.Scale = 2; cmd.Parameters.Add(paramAnnualSalaryLocal);Not explicitly specifying the precision and scale of the parameter would cause the sql update to fail, due to the column level encryption requiring this level of specificity.
Problem:
While I am able to read the encrypted data and display it on a data grid, using a SqlDataSource I am unable to update values back to the database.
I can see that this is because of a data-type miss-match between the parameter value and the database type:
Uncaught Error: Sys.WebForms.PageRequestManagerServerErrorException: Operand type clash: decimal(1,0) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'HRDBCEK1', column_encryption_key_database_name = 'HRDB') is incompatible with decimal(18,2) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'HRDBCEK1', column_encryption_key_database_name = 'HRDB')
Statement(s) could not be prepared.
I believe this to be the case because SqlDataSource.UpdateParameters implements a Parameter collection (not SqlParameter).
System.Web.UI.WebControls.Parameter does not have a property for "scale". Only "DBType" and "Size".
When I switch the DBType from "Decimal" to "String", I can indeed see the "decimal(1,0)" portion of the above exception change to "nvarchar(1)".
Specifying the Size as "18" does not help.
Is there any way to get these updates to work via the use of a SqlDataSource?
My only option seems to be to intercept the OOTB batchEdit event of the grid, cancel the action and to write a complete manual implementation of the update using a SqlCommand object, which does support the SqlParameter object where I am allowed to define the "scale".
Thank you all.
All-Star
52142 Points
15463 Posts
Re: Decimal Scale in asp:parameter
Apr 20, 2018 03:09 AM|oned_gk|LINK
Using sqldatasource wizard maybe you can generate insert/update/delete commands
You can use code behind to set the parameter values like this
SqlDataSource1.UpdateParameters["id"].DefaultValue = "1"; SqlDataSource1.UpdateParameters["name"].DefaultValue = "someone"; SqlDataSource1.Update();Suwandi - Non Graduate Programmer
None
0 Points
2 Posts
Re: Decimal Scale in asp:parameter
Apr 20, 2018 03:39 PM|TMK___|LINK
All that I see in your example is the defining and setting of a parameter value. It doesn't appear you have specified the parameter type or scale/precision, which is the issue at hand.
-Thank you for the response though!
Due to time constraints, I ended up removing the UpdateParameters from the SqlDataSource, intercepted the updating of the DataGrid and replaced it with my own implementation that utilizes a SqlCommand instead.
For sanity reasons it would still be nice to find out if there is a way this could have been achieved with a SqlDataSource. I was under the impression that it should fully support all aspects of SQL Always Encrypted.
All-Star
52142 Points
15463 Posts
Re: Decimal Scale in asp:parameter
Apr 21, 2018 09:31 AM|oned_gk|LINK
Try this in sql
Suwandi - Non Graduate Programmer