none
Problem with SqlDataAdapter and Always Encrypted feature RRS feed

  • Question

  • Hi all,

    I want to use a SqlDataAdapter/SqlCommandBuilder combination to update a table that contains a column with randomized encryption (e.g table dbo.employee and encrypted column birthdate of datetime type).

    The Column Encryption Setting of the connection has been set to Enabled and the application has access to the encryption keys.

    The SelectComamnd of the SqlDataAdapter has been set to "select * from employee".

    When I call SqlDataAdapter.Update(tblEmployee) to update the table from a DataTable, the following SQL statement is recorded in SQL Server Profiler:

    "exec sp_describe_parameter_encryption N'UPDATE [employee] SET [name] = @p1 WHERE (([id] = @p2) AND ([code] = @p3) AND ((@p4 = 1 AND [name] IS NULL) OR ([name] = @p5)) AND ((@p6 = 1 AND [birthdate] IS NULL) OR ([birthdate] = @p7)) AND ((@p8 = 1 AND [salary] IS NULL) OR ([salary] = @p9)))',N'@p1 varchar(18),@p2 int,@p3 varchar(5),@p4 int,@p5 varchar(19),@p6 int,@p7 datetime,@p8 int,@p9 money'"

    and the following SqlException is raised on the client:

    "Encryption scheme mismatch for columns/variables 'birthdate', '@p7'. The encryption scheme for the columns/variables is (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'TESTDB_ALWAYSENCRYPTED') and the expression near line '1' expects it to be (encryption_type = 'DETERMINISTIC') (or weaker). 
    Statement(s) could not be prepared."

    It appears that SqlCommandBuilder includes the ([birthdate] = @p7) condition in the WHERE clause, although this is not supported for a column with randomized encryption.

    What is the appropriate way to use SqlDataAdapter/SqlCommandBuilder in this case?

    Best Regards,

    Theodore Christofis


    Thursday, November 30, 2017 1:15 PM

Answers

  • Hi Zhanglong,

    Thanks for your suggestion. Unfortunately, this approach requires the construction of the update SQL statement which is not quite easy in my scenario because the DataTable is displayed in a grid for editing by the user.

    What I am thinking to do is to use the ConflictOption property of SqlComamndBuilder. Whenever a table contains encrypted columns with randomized encryption, I will have to use either ConflictOption.CompareRowVersion or ConflictOption.OverwriteChanges enumeration value. It appears that the default value of ConflictOption.CompareAllSearchableValues is not compatible with columns encrypted with randomized encryption because it adds all columns in the WHERE clause of the update SQL statement. 

    Best Regards,

    Theodore Christofis

    Monday, December 4, 2017 9:31 AM

All replies

  • Hi Theodoros Christofis,

    >>What is the appropriate way to use SqlDataAdapter/SqlCommandBuilder in this case?

    I would suggest that you could special update SQL statement and related parameters. like this:

    string connstr = "Data Source=.;Initial Catalog=EFDemo;Integrated Security=True";
                DataTable dt = new DataTable();
                string sql = "select * from employee";
                using (var conn = new SqlConnection(connstr))
                {
                    using (var comm = new SqlCommand(sql,conn))
                    {
                        SqlDataAdapter adapter = new SqlDataAdapter(comm);
                        adapter.UpdateCommand = new SqlCommand(
                           "UPDATE employee SET name = @name " +
                           "WHERE id = @id", conn);
                        adapter.UpdateCommand.Parameters.Add(
                            "@name", SqlDbType.NVarChar, 50, "name");
                        SqlParameter parameter = adapter.UpdateCommand.Parameters.Add(
                          "@id", SqlDbType.Int);
                        parameter.SourceColumn = "id";
                        parameter.SourceVersion = DataRowVersion.Original;
                        adapter.Fill(dt);
                        DataRow categoryRow = dt.Rows[0];
                        categoryRow["name"] = "NewName";
                        adapter.Update(dt);
                    }
                }

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 1, 2017 8:50 AM
    Moderator
  • Hi Zhanglong,

    Thanks for your suggestion. Unfortunately, this approach requires the construction of the update SQL statement which is not quite easy in my scenario because the DataTable is displayed in a grid for editing by the user.

    What I am thinking to do is to use the ConflictOption property of SqlComamndBuilder. Whenever a table contains encrypted columns with randomized encryption, I will have to use either ConflictOption.CompareRowVersion or ConflictOption.OverwriteChanges enumeration value. It appears that the default value of ConflictOption.CompareAllSearchableValues is not compatible with columns encrypted with randomized encryption because it adds all columns in the WHERE clause of the update SQL statement. 

    Best Regards,

    Theodore Christofis

    Monday, December 4, 2017 9:31 AM
  • Hi Theodore,

    Using Stored Procedures and passing parameters would most likely solve your problem. And, it *is* the preferred (best practice) methodology to access your database anyway. And then, you don't even need the SQLCommandBuilder. I use what I call PUT Stored Procs that will either Insert or Update depending on whether or not the Primary Key (PK) is included in the parameter. I've been meaning to write a blog post about this kind of PUT Stored Proc ... maybe I'll do that soon.

    For now, here's a simple example of a PUT Stored Proc:

    CREATE PROCEDURE [dbo].[bsp_StatusPut]
     @statuskey      bigint = NULL OUTPUT,
     @statusdatetime  datetime = NULL,
        @status             varchar(max) = NULL,
     @agency    varchar(100) = NULL,
     @messagetype  varchar(128) = NULL,
     @id     uniqueidentifier = NULL
    AS
     SET NOCOUNT ON
    
     SET XACT_ABORT ON
    
     BEGIN TRANSACTION
     IF (@statuskey <= 0 OR @statuskey IS NULL)
     BEGIN
      -- Insert Values into the Cadstatus table
      INSERT Cadstatus
       (statusdatetime,
                @status,
       agency,
       messagetype,
       id)
      SELECT @statusdatetime,
                @status,
       @agency,
       @messagetype,
       @id
    
      SELECT @statuskey = SCOPE_IDENTITY()
    
     END
     ELSE
     BEGIN
      -- Update the Cadstatus table
      UPDATE Cadstatus
      SET statusdatetime = ISNULL(@statusdatetime, statusdatetime),
                status = ISNULL(@status, status),
       agency = ISNULL(@agency, agency),
       messagetype = ISNULL(@messagetype, messagetype),
       id = ISNULL(@id, id)
      WHERE statuskey = @statuskey
     END
    
     COMMIT TRANSACTION
    
     RETURN 0
    
    GO
    

    Now, as you can see, this SP doesn't have many parameters, and it would be easy to add them one-by-one. However, there's an easier way when you have a lot of columns in your table. Here are a set of methods you can use to automatically add all the Parameters (these are methods in my DataAccess base class).

    public void SetAllParms(DataRow Row)
    {
        this.oCommand.Parameters.Clear();
        for (int i = 0; i < Row.Table.Columns.Count; i++)
        {
            this.AddParm(Row.Table.Columns[i].ColumnName, Row[i]);
        }
    }
    public void AddParm(DataRow row, string ColumnName)
    {
        if (row.Table.Columns.Contains(ColumnName))
            this.AddParm(ColumnName, row[ColumnName]);
    }
    public void AddParm(string ParmName, object ParmValue)
    {
        if (ParmName.StartsWith("@") == false)
            ParmName = "@" + ParmName;
    
        if (ParmValue != DBNull.Value && ParmValue != DBNull.Value)
        {
            if (this.oCommand.Parameters.IndexOf(ParmName) > -1)
                this.oCommand.Parameters[ParmName].Value = ParmValue;
            else
                this.oCommand.Parameters.AddWithValue(ParmName, ParmValue);
        }
        else 
            this.RemoveParm(ParmName);
    }
    public void AddParm(string ParmName, object ParmValue, ParameterDirection direction)
    {
        this.AddParm(ParmName, ParmValue);
        this.SetParmDirection(ParmName, direction);
    }
    public void RemoveParm(string ParmName)
    {
        if (ParmName.StartsWith("@") == false)
            ParmName = "@" + ParmName;
    
        if (this.oCommand.Parameters.IndexOf(ParmName) > -1)
            this.oCommand.Parameters.RemoveAt(ParmName);
    }
    public void SetParmDirection(string ParmName, ParameterDirection direction)
    {
        if (ParmName.StartsWith("@") == false)
            ParmName = "@" + ParmName;
    
        if (this.oCommand.Parameters.IndexOf(ParmName) > -1)
            this.oCommand.Parameters[ParmName].Direction = direction;
    }
    

    Here is how you might use these in your own DataAccess classes. My example uses a Typed DataSet, but it could just as easily be used with a plain old DataSet (this is based on my SP above):

    public void UpdateStatus(StatusDataSet ds)
    {
        using (DataAccess da = new DataAccess(this.MyConnectionString))
        {
            da.SetCommand("bsp_StatusPut");
            foreach (StatusDataSet.StatusRow call in ds.Status)
            {
                da.SetAllParms(call);
                da.SetParmDirection("statuskey", ParameterDirection.InputOutput);
            }
            da.ExecuteCommand();
        }
    }

    That's it. I didn't include the da.SetCommand() or da.ExecuteCommand() with the methods I showed for the Parameters, but that's easy enough to figure out. BTW, some of this stuff is in my blog (not all of it). To see all of my DataAccess blog posts, just use this search:

    http://geek-goddess-bonnie.blogspot.com/search?q=DataAccess

    Some of the earlier posts are incomplete, but get "fleshed out" in later posts.

    Hope this helps ...


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, December 4, 2017 5:04 PM