Dela via

Konfigurera kolumnkryptering på plats med Transact-SQL

  • Artikel

gäller för: SQL Server 2019 (15.x) och senare – Windows endast Azure SQL Database

Den här artikeln beskriver hur du utför kryptografiska åtgärder på plats på kolumner med Always Encrypted och säkra enklaver med ALTER TABLE-/ALTER COLUMN-instruktionen. Grundläggande information om kryptering på plats och allmänna förutsättningar finns i Konfigurera kolumnkryptering på plats med Always Encrypted med säkra enklaver.

Med instruktionen ALTER TABLE eller ALTER COLUMN kan du ange målkrypteringskonfigurationen för en kolumn. När du kör instruktionen kommer den säkra inneslutningen på serversidan att kryptera, återkryptera eller dekryptera data som lagras i kolumnen, beroende på aktuell och målkrypteringskonfiguration som anges i kolumndefinitionen i instruktionen.

  • Om kolumnen för närvarande inte är krypterad krypteras den om du anger ENCRYPTED WITH-satsen i kolumndefinitionen.
  • Om kolumnen för närvarande är krypterad dekrypteras den (konverteras till en oformaterad kolumn) om du inte anger ENCRYPTED WITH-satsen i kolumndefinitionen.
  • Om kolumnen för närvarande är krypterad krypteras den igen om du anger ENCRYPTED WITH-satsen och den angivna kolumnkrypteringstypen eller kolumnkrypteringsnyckeln skiljer sig från den krypteringstyp som används för närvarande eller kolumnkrypteringsnyckeln.

Not

Du kan inte kombinera kryptografiska åtgärder med andra ändringar i en enda ALTER TABLE/ALTER COLUMN-instruktion, förutom att ändra kolumnen till NULL eller NOT NULLeller ändra en sortering. Du kan till exempel inte kryptera en kolumn OCH ändra en datatyp för kolumnen i en enda ALTER TABLE/ALTER COLUMN Transact-SQL-instruktion. Använd två separata uttalanden.

Som alla frågor som använder en säker enklav på serversidan måste en ALTER TABLE/ALTER COLUMN-instruktion som utlöser kryptering på plats skickas via en anslutning med Always Encrypted- och enklaverberäkningar aktiverade.

Resten av den här artikeln beskriver hur du utlöser kryptering på plats med hjälp av ALTER TABLE/ALTER COLUMN-instruktionen från SQL Server Management Studio. Du kan också utfärda ALTER TABLE/ALTER COLUMN från Azure Data Studio eller ditt program.

Not

För närvarande stöder Invoke-Sqlcmd-cmdleten i SqlServer PowerShell-modulen och sqlcmdinte användning av ALTER TABLE/ALTER COLUMN för kryptografiska åtgärder på plats.

Utföra kryptering på plats med Transact-SQL i SSMS

Förutsättningar

Steg

  1. Öppna ett frågefönster med Always Encrypted- och enklaverberäkningar aktiverade i databasanslutningen. Mer information finns i Aktivera och inaktivera Always Encrypted för en databasanslutning.

  2. I frågefönstret utfärdar du ALTER TABLE/ALTER COLUMN-instruktionen och anger målkrypteringskonfigurationen för en kolumn som du vill kryptera, dekryptera eller kryptera om. Om du krypterar eller omkrypterar kolumnen använder du satsen ENCRYPTED WITH. Om din kolumn är en strängkolumn (till exempel char, varchar, nchar, nvarchar), kan du också behöva ändra sorteringen till en BIN2-sortering.

    Note

    Om din kolumnhuvudnyckel lagras i Azure Key Vault kan du uppmanas att logga in på Azure.

  3. Rensa plan-cacheminnet för alla batchar och lagrade procedurer som får tillgång till tabellen för att uppdatera krypteringsinformation för parametrar.

    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

    Notis

    Om du inte tar bort planen för den berörda frågan från cacheminnet kan den första körningen av frågan efter kryptering misslyckas.

    Använd ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE eller DBCC FREEPROCCACHE för att rensa plancachen noggrant, eftersom det kan leda till tillfällig försämring av frågeprestanda. För att minimera den negativa effekten av att rensa cacheminnet kan du selektivt ta bort planerna för de frågor som påverkas.

  4. Anropa sp_refresh_parameter_encryption för att uppdatera metadata för parametrarna för varje modul (lagrad procedur, funktion, vy, utlösare) som sparas i sys.parameters och kan ha ogiltigförklarats genom kryptering av kolumnerna.

Exempel

Kryptera en kolumn på plats

Exemplet nedan förutsätter:

  • CEK1 är en enklavaktiverad kolumnkrypteringsnyckel.
  • Kolumnen SSN är klartext och använder för närvarande standarddatabassortering, till exempel Latin1, icke-BIN2-sortering (till exempel Latin1_General_CI_AI_KS_WS).

Instruktionen krypterar SSN-kolumnen med deterministisk kryptering och den enklavaktiverade kolumnkrypteringsnyckeln på plats. Den skriver också över standarddatabassortering med motsvarande (på samma kodsida) BIN2-sortering.

Åtgärden utförs online (ONLINE = ON). Observera också att anropet till ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, som återskapar planerna för frågorna påverkas av tabellschemaändringen.

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

Kryptera om en kolumn på plats för att ändra krypteringstyp

Exemplet nedan förutsätter:

  • Kolumnen SSN krypteras med deterministisk kryptering och en enklavaktiverad kolumnkrypteringsnyckel CEK1.
  • Den aktuella sorteringen, som anges på kolumnnivå, är Latin1_General_BIN2.

Instruktionen nedan krypterar kolumnen igen med hjälp av randomiserad kryptering och samma nyckel (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

Kryptera om en kolumn på plats för att rotera en kolumnkrypteringsnyckel

Exemplet nedan förutsätter:

  • Kolumnen SSN krypteras med randomiserad kryptering och en enklavaktiverad kolumnkrypteringsnyckel CEK1.
  • CEK2 är en enklavaktiverad kolumnkrypteringsnyckel (skiljer sig från CEK1).
  • Den aktuella sorteringen, som anges på kolumnnivå, är Latin1_General_BIN2.

Instruktionen nedan krypterar kolumnen igen med 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

Dekryptera en kolumn på plats

Exemplet nedan förutsätter:

  • Kolumnen SSN krypteras med en enklavaktiverad kolumnkrypteringsnyckel.
  • Den aktuella sorteringen, som anges på kolumnnivå, är Latin1_General_BIN2.

Instruktionen nedan dekrypterar kolumnen och håller sorteringen oförändrad. Du kan också välja att ändra sortering. Ändra till exempel sorteringen till en icke-BIN2-sortering i samma sats.

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

Nästa steg

Se även