Delen via

Kolomversleuteling in-place configureren met Transact-SQL

  • Artikel

Van toepassing op: SQL Server 2019 (15.x) en hoger - Alleen Windows Azure SQL Database

In dit artikel wordt beschreven hoe u cryptografische bewerkingen in-place uitvoert op kolommen met Always Encrypted en beveiligde enclaves door middel van de INSTRUCTIE ALTER TABLE/ALTER COLUMN. Zie Kolomversleuteling ter plaatse configureren met Always Encrypted met beveiligde enclavesvoor basisinformatie over in-place versleuteling en algemene vereisten.

Met de instructie ALTER TABLE of ALTER COLUMN kunt u de doelversleutelingsconfiguratie voor een kolom instellen. Wanneer u de instructie uitvoert, zal de beveiligde enclave aan de serverzijde de gegevens die zijn opgeslagen in de kolom versleutelen, opnieuw versleutelen of ontsleutelen, afhankelijk van de huidige versleutelingsconfiguratie en de doelconfiguratie voor versleuteling die zijn opgegeven in de kolomdefinitie in de instructie.

  • Als de kolom momenteel niet is versleuteld, wordt deze versleuteld als u de ENCRYPTED WITH component in de kolomdefinitie opgeeft.
  • Als de kolom momenteel is versleuteld, wordt deze ontsleuteld (geconverteerd naar een kolom zonder opmaak), als u de ENCRYPTED WITH component niet opgeeft in de kolomdefinitie.
  • Als de kolom momenteel is versleuteld, wordt deze opnieuw versleuteld als u de ENCRYPTED WITH component opgeeft en het opgegeven kolomversleutelingstype of de kolomversleutelingssleutel verschilt van het momenteel gebruikte versleutelingstype of de kolomversleutelingssleutel.

Notitie

U kunt cryptografische bewerkingen niet combineren met andere wijzigingen in één ALTER TABLE/ALTER COLUMN instructie, behalve het wijzigen van de kolom in NULL of NOT NULL, of het wijzigen van een sortering. U kunt bijvoorbeeld een kolom niet versleutelen EN een gegevenstype van de kolom wijzigen in één ALTER TABLE/ALTER COLUMN Transact-SQL instructie. Gebruik twee afzonderlijke uitspraken.

Net als elke query die gebruikmaakt van een beveiligde enclave aan de serverzijde, moet een ALTER TABLE/ALTER COLUMN-instructie die in-place versleuteling activeert, worden verzonden via een verbinding met Always Encrypted en enclaveberekeningen ingeschakeld.

In de rest van dit artikel wordt beschreven hoe u in-place versleuteling activeert met behulp van de ALTER TABLE/ALTER COLUMN-instructie van SQL Server Management Studio. U kunt ook ALTER TABLE/ALTER COLUMN uitgeven vanuit Azure Data Studio of uw toepassing.

Notitie

Op dit moment biedt de cmdlet Invoke-Sqlcmd in de SqlServer PowerShell-module en sqlcmdgeen ondersteuning voor het gebruik van ALTER TABLE/ALTER COLUMN voor in-place cryptografische bewerkingen.

In-place versleuteling uitvoeren met Transact-SQL in SSMS

Vereisten

Stappen

  1. Open een queryvenster met Always Encrypted en enclaveberekeningen ingeschakeld in de databaseverbinding. Zie Always Encrypted in- en uitschakelen voor een databaseverbindingvoor meer informatie.

  2. Geef in het queryvenster de ALTER TABLE/ALTER COLUMN instructie op, waarbij u de doelversleutelingsconfiguratie opgeeft voor een kolom die u wilt versleutelen, ontsleutelen of opnieuw wilt versleutelen. Als u de kolom versleutelt of opnieuw versleutelt, gebruikt u de ENCRYPTED WITH-clausule. Als uw kolom een tekenreekskolom is (bijvoorbeeld char, varchar, nchar, nvarchar), moet u de sortering mogelijk ook wijzigen in een BIN2-sortering.

    Notitie

    Als uw kolomhoofdsleutel is opgeslagen in Azure Key Vault, wordt u mogelijk gevraagd u aan te melden bij Azure.

  3. Wis de plancache voor alle batches en opgeslagen procedures die toegang hebben tot de tabel, zodat versleutelingsinformatie over parameters kan worden vernieuwd.

    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

    Notitie

    Als u het plan voor de betrokken query niet uit de cache verwijdert, kan de eerste uitvoering van de query mislukken nadat de versleuteling is mislukt.

    Gebruik ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE of DBCC FREEPROCCACHE om de plannen cache zorgvuldig te wissen, omdat dit kan zorgen voor tijdelijke prestatiesvermindering bij query's. Als u de negatieve gevolgen van het wissen van de cache wilt minimaliseren, kunt u de plannen voor de betrokken query's selectief verwijderen.

  4. Roep sp_refresh_parameter_encryption aan om de metagegevens bij te werken voor de parameters van elke module (opgeslagen procedure, functie, weergave, trigger) die worden bewaard in sys.parameters en mogelijk ongeldig zijn geworden door de kolommen te versleutelen.

Voorbeelden

Een kolom ter plaatse versleutelen

In het onderstaande voorbeeld wordt ervan uitgegaan:

  • CEK1 is een met enclave ondersteunde versleutelingssleutel voor kolommen.
  • De SSN kolom is tekst zonder opmaak en gebruikt momenteel de standaarddatabasesortering, zoals Latin1, niet-BIN2-sortering (bijvoorbeeld Latin1_General_CI_AI_KS_WS).

De instructie versleutelt de SSN-kolom in plaats met behulp van deterministische versleuteling en de enclave-geactiveerde kolomversleutelingssleutel. Ook wordt de standaarddatabasesortering overschreven met de bijbehorende BIN2-sortering (op dezelfde codepagina).

De bewerking wordt online uitgevoerd (ONLINE = ON). Let ook op de aanroep van ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, waarmee de query-plannen opnieuw worden aangemaakt. Deze worden beïnvloed door de wijziging van het tabelschema.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
WITH
(ONLINE = ON);
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Een kolom in-place opnieuw versleutelen om het versleutelingstype te wijzigen

In het onderstaande voorbeeld wordt ervan uitgegaan:

  • De SSN kolom wordt versleuteld met behulp van deterministische versleuteling en een enclave-versleutelingssleutel voor kolomversleuteling, CEK1.
  • De huidige sortering, ingesteld op kolomniveau, is Latin1_General_BIN2.

De onderstaande instructie versleutelt de kolom opnieuw met behulp van willekeurige versleuteling en dezelfde sleutel (CEK1)

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1]
, ENCRYPTION_TYPE = Randomized
, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Een kolom in-place opnieuw versleutelen om een kolomversleutelingssleutel te roteren

In het onderstaande voorbeeld wordt ervan uitgegaan:

  • De SSN kolom wordt versleuteld met behulp van willekeurige versleuteling en een enclave-versleutelingssleutel voor kolomversleuteling, CEK1.
  • CEK2 is een enclave-ingeschakelde kolomversleutelingssleutel (anders dan CEK1).
  • De huidige sortering, ingesteld op kolomniveau, is Latin1_General_BIN2.

Met de onderstaande instructie wordt de kolom opnieuw versleuteld met CEK2.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK2]
, ENCRYPTION_TYPE = Randomized
, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Een kolom ter plaatse ontsleutelen

In het onderstaande voorbeeld wordt ervan uitgegaan:

  • De SSN kolom wordt versleuteld met behulp van een sleutel voor kolomversleuteling met enclaves.
  • De huidige sortering, ingesteld op kolomniveau, is Latin1_General_BIN2.

Met de onderstaande instructie wordt de kolom ontsleuteld en blijft de sortering ongewijzigd. U kunt er ook voor kiezen om de sortering te wijzigen. Wijzig bijvoorbeeld de sortering in een niet-BIN2-sortering in dezelfde instructie.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
WITH (ONLINE = ON);
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Volgende stappen

Zie ook