Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

sp_describe_parameter_encryption() fails with SELECT FOR UPDATE

Try the following with SQL Server 2016 or 2017:

exec sp_describe_parameter_encryption N'SELECT cust_name FROM customer (UPDLOCK) WHERE cust_pkey = @P1 FOR UPDATE OF cust_name',N'@P1 nchar(5)'

You will get the error: "FOR UPDATE clause allowed only for DECLARE CURSOR"

We are using MS ODBC 13.1 on Linux and Windows (even 17.0 preview on Linux), with Column Encryption.

We use the following ODBC cursor attribute for the SELECT FOR UPDATE, to get a server-side cursor:

r = SQLSetStmtAttr(st->stmtHandle, SQL_ATTR_CONCURRENCY, (SQLPOINTER) SQL_CONCUR_LOCK, SQL_IS_UINTEGER);

When not using Column Encryption, the SELECT FOR UPDATE can be executed.

Thanks for considering this, it is a blocker for us and prevents to use Always Encrypted.

Sebastien FLAESCH Four Js Development Tools

1 vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Sebastien FLAESCH shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    2 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Sebastien FLAESCH commented  ·   ·  Flag as inappropriate

        We can manage to remove the FOR UPDATE [ OF ... ] clause from all SELECT statements goind through our database interface library, but I need to be sure that this clause is totally ignored by ODBC drivers and SQL Server, to not get a different behavior / locking semantics.

      • Sebastien FLAESCH commented  ·   ·  Flag as inappropriate

        To me there is a limitation in the sp_describe_parameter_encryption() stored procedure:

        It cannot manage SELECT FOR UPDATE and gives error

        FOR UPDATE clause allowed only for DECLARE CURSOR

        This happens when using Always Encrypted (ColumnEncryption=Enabled)

        My instinct is that this sp prepares the SQL to get param description (to know what columns/params are encrypted), but when it is a SELECT FOR UPDATE, it should use a
        DECLARE CURSOR internally.

        To me, from an ODBC client program point of view, no matter what ODBC cursor attribute is used, a SELECT FOR UPDATE (with ? params) should execute without errors, with or without ColumnEncryption=Enabled.

        Even if FOR UPDATE clause is not part of the T-SQL SELECT syntax (it is part of DECLARE CURSOR), it works (it's ignored I guess) when Always Encrypted is not used...

        Quite confusing...

      Feedback and Knowledge Base