When using a data encryption mechanism , for example, Always Encrypted with secure enclaves , we also facing a challenge that the encryption key should be rotated regularly. To get a sense how long the key rotation process might take we performed the following test - Azure SQL Database was created in two tiers - DTU-based Standard S6 and S7. A test table create table [aese].demo( id int identity primary key , vc1 varchar(64) , nvc1 nvarchar(64) , nvc2 nvarchar(256) , vb1 varbinary(512) , d1 decimal(19,4) , m1 money , i1 int , bi1 bigint ); was populated with 1000000, 5000000, and 10000000 rows. All the columns (except the primary key) were encrypted and then re-encrypted with a new CEK. The re-encryption time was collected. For varchar/nvarchar/varbinary columns the random number of the same character were loaded using operations like this replicate(N'作', ceiling( rand()*63)+1) The 'var'-type ...
Always Encrypted is a feature designed to protect sensitive data, stored in Azure SQL Database or SQL Server databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). If a database has Always Encrypted enabled then it has at least one Column Encryption Key (CEK) with can be found in the sys.column_encryption_key_values system catalog view. If you run this query select column_encryption_key_id, column_master_key_id, encryption_algorithm_name, encrypted_value from sys.column_encryption_key_values It returns something like that The encrypted_value column is a CEK - random-generated key encrypted with the Master Encryption Key (MEK) which is stored in an Azure Key Vault. To see the master encryption key details use this query select name, column_master_key_id, key_store_provider_name, key_path from sys.column_master_keys the output would l...