如何用本地测试数据替换SQL Always Encrypted加密列？

核心思路 #

绕开生产密钥限制，在测试环境创建独立的临时加密密钥，通过原地修改列的加密配置切换到临时密钥，之后就能直接写入随机测试数据，全程不用删原CMK/CEK，也不用重建列。

具体操作步骤 #

• 1. 在本地/UAT环境建一套临时CMK和CEK（和生产密钥完全独立，仅供测试用）
     以本地证书为例（也可用Azure密钥保管库，只要测试环境能访问）：

  -- 创建临时列主密钥
  CREATE COLUMN MASTER KEY TestCMK
  WITH (
      KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',
      KEY_PATH = 'CurrentUser/my/[你的临时证书指纹]'
  );
  
  -- 创建临时列加密密钥
  CREATE COLUMN ENCRYPTION KEY TestCEK
  WITH VALUES (
      COLUMN_MASTER_KEY = TestCMK,
      ALGORITHM = 'RSA_OAEP',
      ENCRYPTED_VALUE = [临时CMK加密后的CEK值]
  );
  

  生成临时证书和加密值的流程和生产环境一致，仅用测试环境的密钥存储即可。

• 2. 原地修改加密列的加密配置，切换到临时密钥

  ALTER TABLE [目标表名]
  ALTER COLUMN [敏感列名] [原列数据类型]
  ENCRYPTED WITH (
      COLUMN_ENCRYPTION_KEY = TestCEK,
      ENCRYPTION_TYPE = DETERMINISTIC, -- 和原列加密类型保持一致，原先是RANDOMIZED就替换成该值
      ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
  )
  WITH (ONLINE = ON); -- 可选，避免锁表影响其他测试操作
  

  执行后原生产加密的无效数据会转为临时密钥加密的无效数据，但测试环境能访问临时密钥，接下来就可以直接修改数据。

• 3. 写入随机测试数据
     现在列的加密密钥可被测试环境解密，直接执行UPDATE写入测试数据：

  UPDATE [目标表名]
  SET [敏感列名] = [随机生成的测试值] -- 例如生成随机手机号：'138' + RIGHT('000000000' + CAST(CAST(RAND()*1000000000 AS INT) AS VARCHAR),9)
  WHERE 1=1; -- 按需过滤行，比如仅更新测试专用数据行
  

• 4. （可选）后续若需切回原加密配置，重复步骤2将COLUMN_ENCRYPTION_KEY改回原CEK即可
     测试环境通常无需此操作，保持临时密钥配置即可。

关键注意点 #

• 完全符合限制：全程未删除原CMK/CEK，也未重建列，均为原地修改加密属性
• 生产与测试隔离：临时密钥仅用于测试环境，不会触及生产密钥和数据
• 操作简洁：无需复杂工具，纯SQL即可完成

内容的提问来源于stack exchange，提问作者W Terlep