Fråga efter kolumner med Always Encrypted med Azure Data Studio

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Den här artikeln beskriver hur du frågar efter kolumner, krypterade med Always Encrypted med hjälp av Azure Data Studio. Med Azure Data Studio kan du:

• Hämta chiffertextvärden som lagras i krypterade kolumner.
• Hämta oformaterade värden som lagras i krypterade kolumner.
• Skicka klartextvärden som riktar sig till krypterade kolumner (till exempel i INSERT- eller UPDATE-instruktioner och som en uppslagsparameter för WHERE-satserna i SELECT-instruktioner).

Hämtar chiffertextvärden som lagras i krypterade kolumner

I det här avsnittet beskrivs hur du hämtar data som lagras i krypterade kolumner som chiffertext.

Steg

1. Kontrollera att du har inaktiverat Always Encrypted för databasanslutningen för frågefönstret, där du kör en SELECT fråga som hämtar chiffertextvärden. Se Aktivera och inaktivera Always Encrypted för en databasanslutning nedan.
2. Kör din SELECT fråga. Alla data som hämtas från krypterade kolumner returneras som binära (krypterade) värden.

Exempel

Förutsatt att SSN är en krypterad kolumn i tabellen Patients, hämtar frågan nedan binära chiffertextvärden om Always Encrypted är inaktiverat för databasanslutningen.

Hämtar oformaterade värden som lagras i krypterade kolumner

I det här avsnittet beskrivs hur du hämtar data som lagras i krypterade kolumner som chiffertext.

Förutsättningar

• Azure Data Studio version 17.1 eller senare.
• Du måste ha åtkomst till kolumnhuvudnycklarna och metadata om de nycklar som skyddar de kolumner som du kör frågan mot. Mer information finns i Behörigheter för att fråga krypterade kolumner nedan.
• Dina kolumnhuvudnycklar måste lagras i ett nyckelvalv i Azure Key Vault eller Windows Certificate Store. Azure Data Studio stöder inte andra nyckellager och stöder inte kolumnhuvudnycklar som lagras i hanterade HSM:er i Azure Key Vault.

Steg

1. Aktivera Always Encrypted för databasanslutningen för frågefönstret, där du kör en SELECT fråga som hämtar och dekrypterar dina data. Detta instruerar Microsoft .NET Data Provider för SQL Server (används av Azure Data Studio) att dekryptera de krypterade kolumnerna i frågeresultatuppsättningen. Se Aktivera och inaktivera Always Encrypted för en databasanslutning nedan.
2. Kör din SELECT sökning. Alla data som hämtas från krypterade kolumner returneras som oformaterade värden för de ursprungliga datatyperna.

Exempel

Förutsatt att SSN är en krypterad kolumn i tabellen Patients returnerar frågan nedan oformaterade värden om Always Encrypted är aktiverat för databasanslutningen och om du har åtkomst till den kolumnhuvudnyckel som konfigurerats för SSN-kolumnen.

Skicka oformaterade värden som riktar sig till krypterade kolumner

I det här avsnittet beskrivs hur du kör en fråga som skickar värden som riktar sig mot en krypterad kolumn. Till exempel en fråga som infogar, uppdaterar eller filtrerar efter ett värde som lagras i en krypterad kolumn:

Förutsättningar

• Azure Data Studio version 18.1 eller senare.
• Du måste ha åtkomst till kolumnhuvudnycklarna och metadata om de nycklar som skyddar de kolumner som du kör frågan mot. Mer information finns i Behörigheter för att fråga krypterade kolumner nedan.
• Dina kolumnhuvudnycklar måste lagras i ett nyckelvalv i Azure Key Vault eller Windows Certificate Store. Azure Data Studio stöder inte andra nyckellager och stöder inte kolumnhuvudnycklar som lagras i hanterade HSM:er i Azure Key Vault.

Steg

1. Aktivera Always Encrypted för databasanslutningen för frågefönstret, där du kör en SELECT fråga som hämtar och dekrypterar dina data. Detta instruerar Microsoft .NET Data Provider för SQL Server (används av Azure Data Studio) för att kryptera frågeparametrar som riktar sig mot krypterade kolumner och dekryptera resultaten som hämtats från krypterade kolumner. Se Aktivera och inaktivera Always Encrypted för en databasanslutning nedan.
2. Aktivera parameterisering för Always Encrypted för frågefönstret. Se parameterisering för Always Encrypted nedan för mer information.
3. Deklarera en Transact-SQL variabel och initiera den med ett värde som du vill skicka (infoga, uppdatera eller filtrera efter) till databasen.
4. Kör frågan och skicka värdet för variabeln Transact-SQL till databasen. Azure Data Studio konverterar variabeln till en frågeparameter och krypterar dess värde innan den skickas till databasen.

Exempel

Förutsatt att SSN är en krypterad char(11) kolumn i tabellen Patients försöker skriptet nedan hitta en rad som innehåller '795-73-9838' i SSN-kolumnen. Resultatet returneras om Always Encrypted är aktiverat för databasanslutningen, Parameterisering för Always Encrypted är aktiverat för frågefönstret och du har åtkomst till den kolumnhuvudnyckel som konfigurerats för SSN-kolumnen.

@SSN char(11) = '795-73-9838' SELECT * FROM [dbo].[Patienter] WHERE [SSN] = @SSN fråga och frågeresultaten. />

Behörigheter för att fråga krypterade kolumner

Om du vill köra frågor mot krypterade kolumner, inklusive frågor som hämtar data i chiffertext, behöver du behörigheterna VISA VALFRI MASTERNYCKELDEFINITION och VISA VALFRI KOLUMNKRYPNYCKELDEFINITION i databasen.

Förutom ovanstående behörigheter, för att dekryptera eventuella frågeresultat eller för att kryptera frågeparametrar (som produceras genom att parameterisera Transact-SQL variabler), behöver du även behörigheter för nyckelarkiv för att komma åt och använda till kolumnhuvudnyckeln som skyddar målkolumnerna. Detaljerad information om nyckellagringsbehörigheter finns i Skapa och lagra kolumnhuvudnycklar för Always Encrypted och hitta ett avsnitt som är relevant för ditt nyckelarkiv.

Aktivera och inaktivera Always Encrypted för en databasanslutning

När du ansluter till en databas i Azure Data Studio kan du antingen aktivera eller inaktivera Always Encrypted för databasanslutningen. Som standard är Always Encrypted inaktiverat.

Om du aktiverar Always Encrypted för en databasanslutning instrueras Microsoft .NET Data Provider för SQL Server, som används av Azure Data Studio, att försöka transparent:

• Dekryptera alla värden som hämtas från krypterade kolumner och returneras i frågeresultat.
• Kryptera värdena för de parametriserade Transact-SQL variabler som riktar sig mot krypterade databaskolumner.

Om du inte aktiverar Always Encrypted för en anslutning försöker Microsoft .NET Data Provider för SQL Server inte kryptera frågeparametrar eller dekryptera resultat.

Du kan aktivera eller inaktivera Always Encrypted när du ansluter till en databas. Allmän information om hur du ansluter till en databas finns i:

• Snabbstart: Ansluta och fråga SQL Server med hjälp av Azure Data Studio
• Snabbstart: Använd Azure Data Studio för att ansluta och fråga Azure SQL Database

Så här aktiverar du (inaktiverar) Always Encrypted:

1. I dialogrutan Anslutning klickar du på Avancerat....
2. Om du vill aktivera Always Encrypted för anslutningen anger du fältet Always Encrypted till Aktiverad. Om du vill inaktivera Always Encrypted lämnar du antingen värdet för fältet Always Encrypted tomt eller ställer in det på Inaktiverad.
3. Klicka på OK för att stänga Avancerade egenskaper.

Om du vill köra instruktioner som använder en säker enklaver på serversidan när du använder Always Encrypted med säkra enklavermåste du ange ett enklavattesteringsprotokoll och en URL för enklavattestering, förutom att aktivera Always Encrypted för anslutningen. Detaljerad information finns i Krav för att köra T-SQL-instruktioner med enklaver i Azure Data Studio.

Tips

Om du vill växla mellan att Always Encrypted är aktiverat och inaktiverat för ett befintligt frågefönster klickar du på Koppla från och klickar sedan på Anslut och slutför stegen ovan för att återansluta till databasen med önskade värden för fältet Always Encrypted.

Obs

Knappen Ändra anslutning i ett frågefönster stöder för närvarande inte att växla mellan att Always Encrypted aktiveras och inaktiveras.

Parameterisering för Always Encrypted

Parameterisering för Always Encrypted är en funktion i Azure Data Studio 18.1 och senare som automatiskt konverterar Transact-SQL variabler till frågeparametrar (instanser av SqlParameter Class). På så sätt kan den underliggande Microsoft .NET Data Provider för SQL Server identifiera data som riktar sig mot krypterade kolumner och kryptera sådana data innan de skickas till databasen.

Utan parameterisering skickar Microsoft .NET Data Provider för SQL Server varje instruktion som du skapar i frågefönstret som en icke-parametriserad fråga. Om frågan innehåller literaler eller Transact-SQL variabler som riktar sig mot krypterade kolumner kan .NET Framework-dataprovidern för SQL Server inte identifiera och kryptera dem innan frågan skickas till databasen. Därför misslyckas frågan på grund av typmatchningsfel (mellan textliteralen Transact-SQL variabeln och den krypterade kolumnen). Följande fråga misslyckas till exempel utan parameterisering, förutsatt att kolumnen SSN är krypterad.

DECLARE @SSN CHAR(11) = '795-73-9838'
SELECT * FROM [dbo].[Patients]
WHERE [SSN] = @SSN

Aktivera och inaktivera parameterisering för Always Encrypted

Parameterisering för Always Encrypted är inaktiverad som standard.

Så här aktiverar/inaktiverar du parameterisering för Always Encrypted:

1. Välj Fil>Inställningar>Inställningar (Code>Inställningar>Inställningar på Mac).
2. Gå till Data>Microsoft SQL Server.
3. Välj eller avmarkera Aktivera parameterisering för Always Encrypted.
4. Stäng fönstret Inställningar.

Notera

Parameterisering för Always Encrypted fungerar endast i en fråga som använder databasanslutningar med Always Encrypted aktiverat (se Aktivera och inaktivera Always Encrypted för en databasanslutning). Inga Transact-SQL variabler parametriseras om frågefönstret använder en databasanslutning utan Always Encrypted aktiverat.

Så här fungerar parameterisering för Always Encrypted

Om både parameterisering för Always Encrypted och Always Encrypted är aktiverade för ett frågefönster försöker Azure Data Studio parametrisera Transact-SQL variabler som uppfyller följande förutsättningar:

• Deklareras och initieras i samma uttryck (inline-initialisering). Variabler som deklareras med separata SET-instruktioner kommer inte att parametriseras.
• Initieras med en enda literal. Variabler som initieras med uttryck, inklusive operatorer eller funktioner, parametriseras inte.

Nedan visas exempel på variabler som Azure Data Studio kommer att parametrisera.

DECLARE @SSN char(11) = '795-73-9838';
   
DECLARE @BirthDate date = '19990104';
DECLARE @Salary money = $30000;

Här är några exempel på variabler som Azure Data Studio inte försöker parametrisera:

DECLARE @Name nvarchar(50); --Initialization separate from declaration
SET @Name = 'Abel';

DECLARE @StartDate date = GETDATE(); -- a function used instead of a literal

DECLARE @NewSalary money = @Salary * 1.1; -- an expression used instead of a literal

För att ett försök till parameterisering ska lyckas:

• Typen av literal som används för initieringen av variabeln som ska parametriseras måste matcha typen i variabeldeklarationen.
• Om den deklarerade typen av variabel är en datumtyp eller en tidstyp måste variabeln initieras med hjälp av en sträng med något av följande ISO 8601-kompatibla format.

Här är exempel på Transact-SQL variabeldeklarationer som resulterar i parameteriseringsfel:

DECLARE @BirthDate date = '01/04/1999' -- unsupported date format   
   
DECLARE @Number int = 1.1 -- the type of the literal does not match the type of the variable   

Azure Data Studio använder Intellisense för att informera dig om vilka variabler som kan parametriseras och vilka parameteriseringsförsök som misslyckas (och varför).

En deklaration av en variabel som kan parametriseras framgångsrikt markeras med en informationsmeddelandeunderstrykning i frågefönstret. Om du hovrar på en deklarationsuttryck som har markerats med understrykning av ett informationsmeddelande visas meddelandet som innehåller resultatet av parameteriseringsprocessen, inklusive värdena för nyckelegenskaperna för det resulterande SqlParameter Class-objektet (variabeln mappas till: SqlDbType, Size, Precision, Scale, SqlValue). Du kan också se den fullständiga listan över alla variabler som har parametriserats i vyn Problem. Öppna vyn Problem genom att välja Visa>Problem.

Om Azure Data Studio har försökt parametrisera en variabel, men parameteriseringen har misslyckats, markeras deklarationen för variabeln med en understrykning av felet. När du håller muspekaren över den deklaration som har markerats med en felmarkering, kommer du att få information om felet. Du kan också se den fullständiga listan över parameteriseringsfel för alla variabler i vyn Problem.

Notera

Eftersom Always Encrypted stöder en begränsad delmängd av typkonverteringar krävs i många fall att datatypen för en Transact-SQL variabel är samma som den typ av måldatabaskolumn som den riktar sig mot. Om du till exempel antar att typen av kolumnen SSN i tabellen Patients är char(11)misslyckas frågan nedan eftersom typen av variabeln @SSN, som är nchar(11), inte matchar typen av kolumn.

DECLARE @SSN nchar(11) = '795-73-9838'
SELECT * FROM [dbo].[Patients]
WHERE [SSN] = @SSN;

Msg 402, Level 16, State 2, Line 5   
The data types char(11) encrypted with (encryption_type = 'DETERMINISTIC', 
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', 
column_encryption_key_database_name = 'Clinic') collation_name = 'Latin1_General_BIN2' 
and nchar(11) encrypted with (encryption_type = 'DETERMINISTIC', 
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', 
column_encryption_key_database_name = 'Clinic') are incompatible in the equal to operator.

Not

Utan parameterisering bearbetas hela frågan, inklusive typkonverteringar i SQL Server/Azure SQL Database. Med parameterisering aktiverat utförs vissa typkonverteringar av Microsoft .NET Data Provider för SQL Server i Azure Data Studio. På grund av skillnader mellan Microsoft .NET-typsystemet och SQL Server-typsystemet (till exempel olika precision för vissa typer, till exempel flyttal), kan en fråga som körs med parameterisering aktiverad ge andra resultat än frågan som körs utan att parameterisering har aktiverats.

Nästa steg

• Utveckla program med Always Encrypted

Se även

• Alltid Krypterad