Always Encrypted – Lock request time out period exceeded

always-encryptedazure-sql-database

I'm trying to encrypt a column and am getting the following error: Set-SqlColumnEncryption : Lock request time out period exceeded. Are there parameters to specify the lock timeout? What could be locking it? The table has about 10k records. It works fine with empty tables or tables with hundreds of records.

Best Answer

Apparently, you can't have certain options turned on for the table, like "large value types out of row". Here's what I did:

exec sys.sp_tableoption @TableNamePattern = 'dbo.SomeTableName' ,@OptionName = 'large value types out of row' ,@OptionValue = 'off';

Related Solutions

Sql-server – Error 71501, 71561 while encrypting columns using always encrypted feature of SQL Server 2016

Regarding your first question, which version of SSMS, are you using? If it's older than 17.0, I recommend trying the newest version (https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms).

Based on your description, I suspect you are using SSMS 16.x. The likely root cause of this problem is that the objects in your database have external dependencies (on objects in other databases) or have broken dependencies. In SSMS 16.x, the Always Encrypted wizard does a comprehensive validation of all dependencies in the entire database, including objects not dependent on that tables that you are trying to encrypt. We have fixed this problem in SSMS 17, by limiting the validation only to the tables containing the columns you are encrypting and the wizard does not error out anymore when encountering a validation error. Instead, it completes encryption and produces a warning, allowing you to investigate/address the issue later.

Regarding your second question - you need to designate a primary key column for each table.

Sql-server – Can SQL Server Always Encrypted be used for temporary tables

It appears that you have to create a CMK and CEK in tempdb.

This command Works On My Machine®, and is largely copy/pasted from BOL articles.

USE tempdb

CREATE COLUMN MASTER KEY cmk_whatever
    WITH ( KEY_STORE_PROVIDER_NAME = N'cmk_whatever',
           KEY_PATH = 'Current User/Personal/f2260f28d909d21c642a3d8e0b45a830e79a1420' );

CREATE COLUMN ENCRYPTION KEY cek_whatever
    WITH VALUES
    (
        COLUMN_MASTER_KEY = cmk_whatever,
        ALGORITHM = 'RSA_OAEP',
        ENCRYPTED_VALUE = 0x01700000016C006F00630061006C006D0061006300680069006E0065002F006D0079002F003200660061006600640038003100320031003400340034006500620031006100320065003000360039003300340038006100350064003400300032003300380065006600620063006300610031006300284FC4316518CF3328A6D9304F65DD2CE387B79D95D077B4156E9ED8683FC0E09FA848275C685373228762B02DF2522AFF6D661782607B4A2275F2F922A5324B392C9D498E4ECFC61B79F0553EE8FB2E5A8635C4DBC0224D5A7F1B136C182DCDE32A00451F1A7AC6B4492067FD0FAC7D3D6F4AB7FC0E86614455DBB2AB37013E0A5B8B5089B180CA36D8B06CDB15E95A7D06E25AACB645D42C85B0B7EA2962BD3080B9A7CDB805C6279FE7DD6941E7EA4C2139E0D4101D8D7891076E70D433A214E82D9030CF1F40C503103075DEEB3D64537D15D244F503C2750CF940B71967F51095BFA51A85D2F764C78704CAB6F015EA87753355367C5C9F66E465C0C66BADEDFDF76FB7E5C21A0D89A2FCCA8595471F8918B1387E055FA0B816E74201CD5C50129D29C015895CD073925B6EA87CAF4A4FAF018C06A3856F5DFB724F42807543F777D82B809232B465D983E6F19DFB572BEA7B61C50154605452A891190FB5A0C4E464862CF5EFAD5E7D91F7D65AA1A78F688E69A1EB098AB42E95C674E234173CD7E0925541AD5AE7CED9A3D12FDFE6EB8EA4F8AAD2629D4F5A18BA3DDCC9CF7F352A892D4BEBDC4A1303F9C683DACD51A237E34B045EBE579A381E26B40DCFBF49EFFA6F65D17F37C6DBA54AA99A65D5573D4EB5BA038E024910A4D36B79A1D4E3C70349DADFF08FD8B4DEE77FDB57F01CB276ED5E676F1EC973154F86
    );


CREATE TABLE #listofdates
(
    compare_dt DATETIME2(7)
        ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = cek_whatever, ENCRYPTION_TYPE = DETERMINISTIC,
                         ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256' ) NOT NULL
);

Best Answer

Related Solutions

Sql-server – Error 71501, 71561 while encrypting columns using always encrypted feature of SQL Server 2016

Sql-server – Can SQL Server Always Encrypted be used for temporary tables

Related Question