Ócáid
Mar 31, 11 PM - Apr 2, 11 PM
An ócáid foghlama SQL, Fabric and Power BI is mó. Márta 31 – 2 Aibreán. Bain úsáid as cód FABINSIDER chun $ 400 a shábháil.
Cláraigh inniuNí thacaítear leis an mbrabhsálaí seo a thuilleadh.
Uasghrádú go Microsoft Edge chun leas a bhaint as na gnéithe is déanaí, nuashonruithe slándála, agus tacaíocht theicniúil.
Applies to:
SQL Server 2016 (13.x) and later
Azure SQL Database
Azure SQL Managed Instance
Alters a column encryption key in a database, adding or dropping an encrypted value. A column encryption key can have up to two values, which allows for the rotation of the corresponding column master key. A column encryption key is used when encrypting columns using Always Encrypted or Always Encrypted with secure enclaves. Before adding a column encryption key value, you must define the column master key that was used to encrypt the value by using SQL Server Management Studio or the CREATE MASTER KEY statement.
Transact-SQL syntax conventions
ALTER COLUMN ENCRYPTION KEY key_name
[ ADD | DROP ] VALUE
(
COLUMN_MASTER_KEY = column_master_key_name
[, ALGORITHM = 'algorithm_name' , ENCRYPTED_VALUE = varbinary_literal ]
) [;]
key_name
The column encryption key that you are changing.
column_master_key_name
Specifies the name of the column master key (CMK) used for encrypting the column encryption key (CEK).
algorithm_name
Name of the encryption algorithm used to encrypt the value. The algorithm for the system providers must be RSA_OAEP. This argument is not valid when dropping a column encryption key value.
varbinary_literal
The CEK BLOB encrypted with the specified master encryption key. This argument is not valid when dropping a column encryption key value.
Rabhadh
Never pass plaintext CEK values in this statement. Doing so will comprise the benefit of this feature.
Typically, a column encryption key is created with just one encrypted value. When a column master key needs to be rotated (the current column master key needs to be replaced with the new column master key), you can add a new value of the column encryption key, encrypted with the new column master key. This workflow allows you to ensure client applications can access data encrypted with the column encryption key, while the new column master key is being made available to client applications. An Always Encrypted enabled driver in a client application that does not have access to the new master key, will be able to use the column encryption key value encrypted with the old column master key to access sensitive data. The encryption algorithms, Always Encrypted supports, require the plaintext value to have 256 bits.
It is recommended you use tools, such as SQL Server Management Studio (SSMS) or PowerShell to rotate column master keys. See Rotate Always Encrypted keys using SQL Server Management Studio and Rotate Always Encrypted keys using PowerShell.
An encrypted value should be generated using a key store provider that encapsulates the key store holding the column master key.
Column master keys are rotated for following reasons:
Use sys.columns (Transact-SQL), sys.column_encryption_keys (Transact-SQL) and sys.column_encryption_key_values (Transact-SQL) to view information about column encryption keys.
Requires ALTER ANY COLUMN ENCRYPTION KEY permission on the database.
The following example alters a column encryption key called MyCEK
.
ALTER COLUMN ENCRYPTION KEY MyCEK
ADD VALUE
(
COLUMN_MASTER_KEY = MyCMK2,
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F0064006500650063006200660034006100340031003000380034006200350033003200360066003200630062006200350030003600380065003900620061003000320030003600610037003800310066001DDA6134C3B73A90D349C8905782DD819B428162CF5B051639BA46EC69A7C8C8F81591A92C395711493B25DCBCCC57836E5B9F17A0713E840721D098F3F8E023ABCDFE2F6D8CC4339FC8F88630ED9EBADA5CA8EEAFA84164C1095B12AE161EABC1DF778C07F07D413AF1ED900F578FC00894BEE705EAC60F4A5090BBE09885D2EFE1C915F7B4C581D9CE3FDAB78ACF4829F85752E9FC985DEB8773889EE4A1945BD554724803A6F5DC0A2CD5EFE001ABED8D61E8449E4FAA9E4DD392DA8D292ECC6EB149E843E395CDE0F98D04940A28C4B05F747149B34A0BAEC04FFF3E304C84AF1FF81225E615B5F94E334378A0A888EF88F4E79F66CB377E3C21964AACB5049C08435FE84EEEF39D20A665C17E04898914A85B3DE23D56575EBC682D154F4F15C37723E04974DB370180A9A579BC84F6BC9B5E7C223E5CBEE721E57EE07EFDCC0A3257BBEBF9ADFFB00DBF7EF682EC1C4C47451438F90B4CF8DA709940F72CFDC91C6EB4E37B4ED7E2385B1FF71B28A1D2669FBEB18EA89F9D391D2FDDEA0ED362E6A591AC64EF4AE31CA8766C259ECB77D01A7F5C36B8418F91C1BEADDD4491C80F0016B66421B4B788C55127135DA2FA625FB7FD195FB40D90A6C67328602ECAF3EC4F5894BFD84A99EB4753BE0D22E0D4DE6A0ADFEDC80EB1B556749B4A8AD00E73B329C95827AB91C0256347E85E3C5FD6726D0E1FE82C925D3DF4A9
);
GO
The following example alters a column encryption key called MyCEK
by dropping a value.
ALTER COLUMN ENCRYPTION KEY MyCEK
DROP VALUE
(
COLUMN_MASTER_KEY = MyCMK
);
GO
CREATE COLUMN ENCRYPTION KEY (Transact-SQL)
DROP COLUMN ENCRYPTION KEY (Transact-SQL)
CREATE COLUMN MASTER KEY (Transact-SQL)
Always Encrypted (Database Engine)
sys.column_encryption_keys (Transact-SQL)
sys.column_encryption_key_values (Transact-SQL)
sys.columns (Transact-SQL)
Always Encrypted
Overview of Key Management for Always Encrypted
Manage keys for Always Encrypted with secure enclaves
Ócáid
Mar 31, 11 PM - Apr 2, 11 PM
An ócáid foghlama SQL, Fabric and Power BI is mó. Márta 31 – 2 Aibreán. Bain úsáid as cód FABINSIDER chun $ 400 a shábháil.
Cláraigh inniu