SQL Server 2016 Always Encrypted（始终加密）

标签：Province 加密 ProvinceName dbo -- Encrypted Server Alias SQL

Always Encrypted 功能旨在保护 Azure SQL Database 或 SQL Server 数据库中存储的敏感数据，如信用卡号或身份证号（例如美国社会安全号码）。 始终加密允许客户端对客户端应用程序内的敏感数据进行加密，并且永远不向数据库引擎（ SQL Database 或 SQL Server）显示加密密钥。 因此，始终加密分隔了拥有数据（且可以查看它）的人员与管理数据（但没有访问权限）的人员。始终加密确保本地数据库管理员、云数据库操作员或其他高特权但未经授权的用户无法访问加密的数据，使客户能够放心地将敏感数据存储在不受其直接控制的区域。这样，组织便可以静态加密数据并利用 Azure 中的存储，将本地数据库的管理权限委托给第三方，或者降低其自身 DBA 员工的安全核查要求。（更多参考：始终加密（数据库引擎））

常用客户端对列加密的支持：

任务	SSMS	PowerShell	T-SQL
预配列主密钥、列加密密钥、加密列加密密钥及其相应的列主密钥。	用户帐户控制	是	否
在数据库中创建密钥元数据。	用户帐户控制	是	用户帐户控制
创建具有加密列的新表	用户帐户控制	是	用户帐户控制
对选定的数据库列中的现有数据进行加密	用户帐户控制	是	否

限制也比较多。具有以下特征的列不支持 Always Encrypted（例如，如果某个列存在以下任何情况，则不能在 CREATE TABLE/ALTER TABLE 中针对该列使用 Encrypted WITH 子句）：

使用以下任一数据类型的列： xml、 timestamp/rowversion、 image、 ntext、 text、 sql_variant、 hierarchyid、 geography、 geometry、别名、用户定义类型。
FILESTREAM 列
具有 IDENTITY 属性的列
具有 ROWGUIDCOL 属性的列
采用非 bin2 排序规则的字符串（varchar、char 等）
用作使用随机加密列作为键列的非聚集索引的键的列（可以是确定性加密列）
用作使用随机加密列作为键列的聚集索引的键的列（可以是确定性加密列）
用作包含随机和确定性加密列的全文索引的键的列
计算列引用的列（当表达式针对始终加密执行不受支持的操作时）
稀疏列集
统计信息引用的列
使用别名类型的列
分区列
包含默认约束的列
使用随机加密时 unique 约束引用的列（支持确定性加密）
使用随机加密时的主键列（支持确定性加密）
使用随机加密或确定性加密时引用外键约束中的列（如果被引用和引用列使用不同的键或算法）
check 约束引用的列
使用变更数据捕获的表中的列
具有更改跟踪的表中的主键列
屏蔽的列（使用动态数据屏蔽）
Stretch Database 表中的列。（无法为延伸启用其列已使用始终加密加密的表。）
外部 (PolyBase) 表中的列（注意：支持在同一查询中使用外部表和列已加密的表）
不支持针对加密列使用的表值参数。

不能对加密的列使用以下子句：

FOR XML
FOR JSON PATH

以下功能对加密的列不起作用：

事务复制或合并复制
分布式查询（链接服务器）

创建列主密钥和列加密密钥

--  创建列主密钥
--  DROP COLUMN MASTER KEY [MasterKey_CurrentUser];
CREATE COLUMN MASTER KEY [MasterKey_CurrentUser]
WITH
(
    KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = N'CurrentUser/My/53900F5E77EAB638B3D4BAE7428FC2CE1A8C660C'
)
GO

/* 
【KEY_STORE_PROVIDER_NAME】 取值：

密钥存储提供程序名称      基础密钥存储
---------------------       ----------------------------------------------------
'MSSQL_CERTIFICATE_STORE'   Windows 证书存储
'MSSQL_CSP_PROVIDER'        支持 Microsoft CryptoAPI 的存储，如硬件安全模块 (HSM)。
'MSSQL_CNG_STORE'           支持下一代加密技术 API 的存储，如硬件安全模块 (HSM)。
'Azure_Key_Vault'           请参阅 Azure Key Vault 入门


【KEY_PATH】 密钥路径格式及实际存储位置（Windows 证书存储）：

证书存储位置/证书存储名称/证书指纹 = N'CurrentUser/My/53900F5E77EAB638B3D4BAE7428FC2CE1A8C660C'

HKEY_CURRENT_USER\Software\Microsoft\SystemCertificates\CA\Certificates\53900F5E77EAB638B3D4BAE7428FC2CE1A8C660C

运行：certmgr.msc   （certmgr ——>>个人——>> 证书）
*/


--  创建列加密密钥
--  DROP COLUMN ENCRYPTION KEY [CEK01];
CREATE COLUMN ENCRYPTION KEY [CEK01]
WITH VALUES
(
    COLUMN_MASTER_KEY = [MasterKey_CurrentUser],
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F00350033003900300030006600350065003700370065006100620036003300380062003300640034006200610065003700340032003800660063003200630065003100610038006300360036003000630013A0AAE50CCE337E01F9E1B8793BD966F4715BA7568B90EC2CD7096586553F77195EA083EAD694CA4F9D15E2734DCC14E73A4F308BDDBC273588EFAD91D761D1604DBF517A930CE062C5A3445F1ED66A24F38CDC8D68184176B1F0DD656288AA715B22B8AE3F73E1A1EDF58AE6FABE1479F726A0F8397BCF96E1CA3C982E1EC8B560961EBE2A0A05619B6BABDCCA61582A177BE8B9B3D1187D8B04FBC40C4CA1D14E43BEF6EFCEE83F2982750FFE07B2369C06F3122BE8406A26EC491FF11C83099CB6A34FA5B59CF33859A95DDF1C5251F2A92CBF8E9A3760B22F42F3CA2302C0CD7FEA3D6509A288B0023B087360D985D95EFEF21CF7D9D36212479168A1A67DB0BD8CDA3DC1F02500BF4D43A45AFB62DBA88DF7CA2E77AE3F74686F26F3C524CB088B93C6DB0D2C2D2B16464FD92879483370EDC1F83DEC082582321BDD1364B3BFF5ED1FA55D0CD76EE00C8219FD6DE6CD8B8371DADD4C20E8C26C4AEC5C4D26C5ACFBA86C736BB92CA5A14EDAB4C265D052A5484012B0B03ED851879F8B7453B0D65A7CE4D7C9D70E9F5DA78E39F53BE16E798C9C999B0BA9591E83A2B17E2E9E98A263988C52CA8E9058832B68F4AD7B84BE1E3FE3D63DE254EDDC54B07CABD10434BCA53B31E383DF337CE1B0EEA4D17243839723A7AC04AF94A08350EDC7C56A22E1A6C47D428EC7098E9590DD2CE931F194464F94385BDE6D90153D
)
GO

/*
--  可增加多个列加密密钥
ALTER COLUMN ENCRYPTION KEY [CEK01]   
ADD VALUE   -- [ ADD | DROP ]
(  
    COLUMN_MASTER_KEY = [MasterKey_CurrentUser2], --不同的CMK
    ALGORITHM = 'RSA_OAEP',   
    ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F0064006500650063006200660034006100340031003000380034006200350033003200360066003200630062006200350030003600380065003900620061003000320030003600610037003800310066001DDA6134C3B73A90D349C8905782DD819B428162CF5B051639BA46EC69A7C8C8F81591A92C395711493B25DCBCCC57836E5B9F17A0713E840721D098F3F8E023ABCDFE2F6D8CC4339FC8F88630ED9EBADA5CA8EEAFA84164C1095B12AE161EABC1DF778C07F07D413AF1ED900F578FC00894BEE705EAC60F4A5090BBE09885D2EFE1C915F7B4C581D9CE3FDAB78ACF4829F85752E9FC985DEB8773889EE4A1945BD554724803A6F5DC0A2CD5EFE001ABED8D61E8449E4FAA9E4DD392DA8D292ECC6EB149E843E395CDE0F98D04940A28C4B05F747149B34A0BAEC04FFF3E304C84AF1FF81225E615B5F94E334378A0A888EF88F4E79F66CB377E3C21964AACB5049C08435FE84EEEF39D20A665C17E04898914A85B3DE23D56575EBC682D154F4F15C37723E04974DB370180A9A579BC84F6BC9B5E7C223E5CBEE721E57EE07EFDCC0A3257BBEBF9ADFFB00DBF7EF682EC1C4C47451438F90B4CF8DA709940F72CFDC91C6EB4E37B4ED7E2385B1FF71B28A1D2669FBEB18EA89F9D391D2FDDEA0ED362E6A591AC64EF4AE31CA8766C259ECB77D01A7F5C36B8418F91C1BEADDD4491C80F0016B66421B4B788C55127135DA2FA625FB7FD195FB40D90A6C67328602ECAF3EC4F5894BFD84A99EB4753BE0D22E0D4DE6A0ADFEDC80EB1B556749B4A8AD00E73B329C95827AB91C0256347E85E3C5FD6726D0E1FE82C925D3DF4A9  
); 
GO
--  删除其中一个
ALTER COLUMN ENCRYPTION KEY [CEK01]  
DROP VALUE  
(  
    COLUMN_MASTER_KEY = [MasterKey_CurrentUser2]  
);  
GO
*/

-- 查看（或打开数据库——>>安全性——>>Always Encrypted 密钥）
select * from sys.column_master_keys
select * from sys.column_encryption_keys
select * from sys.column_encryption_key_values


--  更改数据库排序规则
--  select * from ::fn_helpcollations() where name like 'Chinese%bin2'
ALTER DATABASE test COLLATE Latin1_General_BIN2;
GO

SQL Server 2016 Always Encrypted（始终加密）_字段

对已存在的表字段加密

SQL Server 2016 Always Encrypted（始终加密）_数据_02

--当前已存在的表，且有数据，结果如下：
CREATE TABLE [dbo].[tmp_ms_xx_Province1] (
    [ProvinceId]   INT              NOT NULL,
    [ProvinceName] NVARCHAR (60)  NOT NULL,
    [Alias] VARCHAR (10) NULL
);


--  原有的表字段加密前，先把字段长度加长（注意！）
ALTER TABLE Province ALTER COLUMN ProvinceName NVARCHAR(1000);
GO

/*【更注意】加密当前表的字段过程中，会把原来的表删掉替换，如果有特殊作用需谨慎！加密过程如下：

--1.创建同名表
CREATE TABLE [dbo].[tmp_ms_xx_Province1] (
    [ProvinceId]   INT              NOT NULL,
    [ProvinceName] NVARCHAR (1000)  COLLATE latin1_general_BIN2  ENCRYPTED WITH (
     COLUMN_ENCRYPTION_KEY = [CEK01],
     ALGORITHM = N'AEAD_AES_256_CBC_HMAC_SHA_256',
     ENCRYPTION_TYPE = RANDOMIZED
    ) NULL,
    [Alias]        VARCHAR (10)     COLLATE Chinese_PRC_CI_AS NULL,
    [tceGuidCol1]  UNIQUEIDENTIFIER CONSTRAINT [tmp_ms_xx_constraint_tceGuidCol1_Province_dbo1] UNIQUE
);

--2.原表增加一个字段 并 更新默认值
ALTER TABLE [dbo].[Province] ADD tceGuidCol1 UNIQUEIDENTIFIER;


--3.导入主要字段到新表
insert bulk [dbo].[tmp_ms_xx_Province1] ([ProvinceId] Int, [ProvinceName] VarBinary(2065), [tceGuidCol1] UniqueIdentifier) 
with (TABLOCK, FIRE_TRIGGERS)

--4.按guid更新数据到新表
UPDATE [dbo].[tmp_ms_xx_Province1]
SET    [dbo].[tmp_ms_xx_Province1].[Alias]   = [dbo].[Province].[Alias]
FROM   [dbo].[Province]
WHERE  [dbo].[Province].[tceGuidCol1] = [dbo].[tmp_ms_xx_Province1].[tceGuidCol1];


--5.删除原表，更名新表
DROP TABLE [dbo].[Province];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_Province1]', N'Province';

--6.删除之前添加的多余字段
ALTER TABLE [dbo].[Province] DROP CONSTRAINT [tmp_ms_xx_constraint_tceGuidCol1_Province_dbo1], COLUMN [tceGuidCol1];

*/


--  加密成功后查看，该字段都是加密字符。
SELECT * FROM Province

--  若要查看解密内容，重新连接，连接选项中添加此参数：Column Encryption Setting=Enabled

--  连接进来后直接查看，可看到明文数据
SELECT * FROM Province
SELECT * FROM Province WHERE ProvinceName = N'北京直辖市'


--  查询和添加数据呢？？ 这样是报错的！！！
SELECT * FROM Province WHERE ProvinceName = N'北京直辖市'
INSERT INTO Province(ProvinceId,ProvinceName,Alias) VALUES(35,N'测试' ,N'测试') --字符都使用Unicode
GO
/*
消息 206，级别 16，状态 2，第 143 行
操作数类型冲突: nvarchar 与 nvarchar(4000) encrypted with (encryption_type = 'DETERMINISTIC', 
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK01', 
column_encryption_key_database_name = 'test') 不兼容
*/


/*
那就用存储过程中的参数插入吧！！两个地方可设置当前连接的客户端使用参数化：
查询——>>查询选项——>>执行——>>高级——>>勾选“启用 Always Encrtpted 参数化”
工具——>>选项——>>执行查询——>>SQL Server——>>高级——>>勾选“启用 Always Encrtpted 参数化”
*/


--  查询，正常！
DECLARE @ProvinceName NVARCHAR(60) = N'北京直辖市'
SELECT * FROM Province WHERE ProvinceName = @ProvinceName
GO

--  添加，正常！
DECLARE @ProvinceId INT = 35
DECLARE @ProvinceName NVARCHAR(60) = N'测试'
DECLARE @Alias VARCHAR(10) = N'测试'
INSERT INTO dbo.Province(ProvinceId,ProvinceName,Alias) VALUES (@ProvinceId,@ProvinceName ,@Alias)
GO
/* 以下两种写法则报错（奇怪？）：
DECLARE @ProvinceId INT
DECLARE @ProvinceName NVARCHAR(60) 
DECLARE @Alias VARCHAR(10)
SET @ProvinceId = 36
SET @ProvinceName = N'测试'
SET @Alias = N'测试'
INSERT INTO dbo.Province(ProvinceId,ProvinceName,Alias) VALUES (@ProvinceId,@ProvinceName ,@Alias)
GO
DECLARE @ProvinceId INT,@ProvinceName NVARCHAR(60) ,@Alias VARCHAR(10)
SET @ProvinceId = 35
SET @ProvinceName = N'测试'
SET @Alias = N'测试'
INSERT INTO dbo.Province(ProvinceId,ProvinceName,Alias) VALUES (@ProvinceId,@ProvinceName ,@Alias)
GO

消息 33299，级别 16，状态 6，第 183 行
加密方案不匹配列/变量 '@ProvinceName'。列/变量的加密方案为 (encryption_type = 'PLAINTEXT')，
行“9”附近的表达式预期其为 (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256'
, column_encryption_key_name = 'CEK01', column_encryption_key_database_name = 'test') (或更弱)。

*/

 SELECT * FROM Province

新表创建时进行字段加密设置

--  DROP TABLE Customers
CREATE TABLE Customers (  
    CustName nvarchar(300) COLLATE  Latin1_General_BIN2 ENCRYPTED WITH (
            COLUMN_ENCRYPTION_KEY = CEK01,  -- CEK
            ENCRYPTION_TYPE = RANDOMIZED,       -- 随机长度
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),   
    SSN varchar(300) COLLATE  Latin1_General_BIN2 ENCRYPTED WITH (
            COLUMN_ENCRYPTION_KEY = CEK01,  -- CEK
            ENCRYPTION_TYPE = DETERMINISTIC ,   -- 固定长度
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),   
    Age int NULL  
);  
GO 

DECLARE @CustName nvarchar(60) = N'Always Encrypted'
DECLARE @SSN varchar(11) = '198-33-0987'
DECLARE @Age int = 10
INSERT INTO dbo.Customers(CustName,SSN,Age) VALUES (@CustName,@SSN ,@Age)
GO

SELECT * FROM Customers
GO

若去掉连接参数：Column Encryption Setting=Enabled , 则查询结果都为加密字符，加密字段的DML 也不正常。

标签：Province,加密,ProvinceName,dbo,--,Encrypted,Server,Alias,SQL
From： https://blog.51cto.com/hzc2012/6000781

SQL Server 2016 Always Encrypted（始终加密）

相关文章

赞助商

阅读排行