如何用T-SQL为启用Always Encrypted的SQL Server数据库添加加密列

要通过T-SQL批量完成加密列添加操作，必须在添加列的同时指定加密配置——SQL Server不支持将已存在的普通列通过ALTER COLUMN改为加密列。具体操作步骤如下：

1. 确认可用的列加密密钥（CEK） #

先检查数据库中是否有可用的列加密密钥（Column Encryption Key），执行以下查询：

SELECT name, column_encryption_key_id 
FROM sys.column_encryption_keys;

你的数据库已启用Always Encrypted，通常已有可用的CEK；若需新建，可参考官方语法创建（此处假设已有可用密钥）。

2. 添加加密列的T-SQL语法 #

使用ALTER TABLE ADD COLUMN语句，通过ENCRYPTED WITH子句指定加密规则：

ALTER TABLE [你的表名]
ADD [新列名] [数据类型]
ENCRYPTED WITH (
    COLUMN_ENCRYPTION_KEY = [你的列加密密钥名称],
    ENCRYPTION_TYPE = DETERMINISTIC, -- 按需替换为 RANDOMIZED
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
)
NULL; -- 按需替换为 NOT NULL

参数说明： #

• ENCRYPTION_TYPE：
  • DETERMINISTIC：相同明文生成相同密文，支持等值查询但安全性稍低
  • RANDOMIZED：相同明文生成不同密文，安全性更高但不支持等值查询
• ALGORITHM：必须指定为AEAD_AES_256_CBC_HMAC_SHA_256（Always Encrypted唯一支持的加密算法）

3. 执行命令的关键注意事项 #

• 连接数据库时，必须在连接字符串中添加Column Encryption Setting=Enabled，否则会因无法访问密钥存储导致执行失败。
  • 若使用sqlcmd命令行工具，需添加参数-C（或--column-encryption），示例：

    sqlcmd -S 你的服务器名 -d 你的数据库名 -U 用户名 -P 密码 -C -Q "ALTER TABLE ..."
    

• 执行命令的账号需要具备列主密钥（CMK）的访问权限，以及目标表的ALTER权限。

内容的提问来源于stack exchange，提问作者Simon Parker