Dela via

Fråga efter kolumner med Always Encrypted med SQL Server Management Studio

  • Artikel

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 SQL Server Management Studio (SSMS). Med SSMS 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).

Notera

Om du använder kolumnhuvudnycklar som lagras i ett hanterat HSM- i Azure Key Vault krävs SSMS 18.9 eller senare version.

Hämtar chiffertextvärden som lagras i krypterade kolumner

Om du kör SELECT-frågor som hämtar chiffertext för data som lagras i krypterade kolumner (utan att dekryptera data) behöver du inte ha åtkomst till kolumnhuvudnycklar som skyddar data. Så här hämtar du värden från en krypterad kolumn som chiffertext i SSMS:

  1. Se till att du kan komma åt metadata om nycklarna som skyddar kolumnerna som du kör din fråga mot. Även om du inte behöver kunna komma åt de faktiska kolumnhuvudnycklarna behöver du behörigheter på databasnivå för att visa metadataobjekten för kolumnhuvudnyckeln och kolumnkrypteringsnyckeln i databasen. Mer information finns i Behörigheter för att fråga krypterade kolumner nedan.
  2. Kontrollera att du har inaktiverat Always Encrypted för databasanslutningen för frågeredigerarens fönster, 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.
  3. Kör SELECT-förfrågan. Alla data som hämtas från krypterade kolumner returneras som binära (krypterade) värden.

Hämtar chiffertextexempel

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.

Skärmbild av SELECT [SSN] FROM [dbo].[Patienter]-fråga och resultatet av frågan som visas som binära chiffertextvärden.

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

Så här hämtar du värden från en krypterad kolumn som klartext (för att dekryptera värdena):

  1. Se till att du kan komma åt kolumnhuvudnycklarna och metadata om nycklarna 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.
  2. Kontrollera att du har aktiverat Always Encrypted för databasanslutningen för frågeredigerarens fönster, där du kör en SELECT fråga som hämtar och dekrypterar dina data. Detta instruerar .NET Framework Data Provider för SQL Server (används av SSMS) att dekryptera de krypterade kolumnerna i frågeresultatuppsättningen. Se Aktivera och inaktivera Always Encrypted för en databasanslutning nedan.
  3. Kör din SELECT sökfråga. Alla data som hämtas från krypterade kolumner returneras som oformaterade värden för de ursprungliga datatyperna.

Hämtar ett exempel på klartext

Förutsatt att SSN är en krypterad char(11) 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 kolumnhuvudnyckeln som konfigurerats för SSN-kolumnen.

Skärmbild av SELECT [SSN] FROM [Clinic].[dbo].[Patients]-fråga och resultaten av frågan som visas som oformaterade textvärden.

Skicka oformaterade värden som riktar sig till krypterade kolumner

Så här kör du en fråga som skickar ett värde som riktar sig mot en krypterad kolumn, till exempel en fråga som infogar, uppdaterar eller filtrerar med ett värde som lagras i en krypterad kolumn:

  1. Se till att du kan komma åt kolumnhuvudnycklarna och metadata för nycklarna som skyddar kolumnerna som din fråga körs mot. Mer information finns i Behörigheter för att fråga krypterade kolumner nedan.

  2. Kontrollera att du har aktiverat Always Encrypted för databasanslutningen för frågeredigerarens fönster, där du kör en SELECT fråga som hämtar och dekrypterar dina data. Detta instruerar .NET Framework Data Provider för SQL Server (används av SSMS) att dekryptera de krypterade kolumnerna i frågeresultatuppsättningen. Se Aktivera och inaktivera Always Encrypted för en databasanslutning nedan.

  3. Se till att parameterisering för Always Encrypted är aktiverat för frågeredigerarens fönster. (Kräver minst SSMS version 17.0.) Deklarera en Transact-SQL variabel och initiera den med ett värde som du vill skicka (infoga, uppdatera eller filtrera efter) till databasen. Mer information finns i parameterisering för Always Encrypted nedan.

  4. Kör frågan och skicka värdet för variabeln Transact-SQL till databasen. SSMS 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 och returnera värdet för kolumnen LastName, förutsatt att Always Encrypted är aktiverat för databasanslutningen, parameterisering för Always Encrypted är aktiverat för frågeredigerarens fönster och du har åtkomst till kolumnhuvudnyckeln som konfigurerats för SSN-kolumnen.

Skärmbild av frågan med hjälp av en variabel för @SSN och den resulterande raden som returneras.

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 VIEW ANY COLUMN MASTER KEY DEFINITION och VIEW ANY COLUMN ENCRYPTION KEY DEFINITION 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 SSMS 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 .NET Framework-dataprovidern för SQL Server, som används av SQL Server Management Studio, att transparent försöka:

  • 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 .NET Framework-dataprovidern för SQL Server, SSMS använder, inte kryptera frågeparametrar eller dekryptera resultat.

Du kan aktivera eller inaktivera Always Encrypted när du skapar en ny anslutning eller ändra en befintlig anslutning med hjälp av dialogrutan Anslut till server.

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

  1. Öppna dialogrutan Anslut till server (mer information finns i Anslut till en SQL Server-instans).
  2. Välj alternativ.
  3. Välj fliken Always Encrypted. Om du vill aktivera Always Encrypted väljer du Aktivera Always Encrypted (kolumnkryptering). Om du vill inaktivera Always Encrypted kontrollerar du att Aktivera Always Encrypted (kolumnkryptering) inte är markerat.
  4. Välj Anslut.

Tips

Växla mellan att Always Encrypted aktiveras och inaktiveras för ett befintligt frågeredigerarefönster:

  1. Högerklicka var som helst i frågeredigerarens fönster.
  2. Välj Anslutning>Ändra anslutning .... Då öppnas dialogrutan Anslut till server för den aktuella anslutningen för frågeredigerarens fönster.
  3. Aktivera eller inaktivera Always Encrypted genom att följa stegen ovan och klicka på Anslut.

Not

Information om hur du kör instruktioner som använder en säker enklav på serversidan när du använder Always Encrypted med säkra enklaverfinns i Kör Transact-SQL-instruktioner med hjälp av säkra enklaver.

Parameterisering för Always Encrypted

Parameterisering för Always Encrypted är en funktion i SQL Server Management Studio som automatiskt konverterar Transact-SQL variabler till frågeparametrar (instanser av SqlParameter Class). (Kräver minst SSMS version 17.0.) Detta gör att den underliggande .NET Framework-dataprovidern för SQL Server kan identifiera data som riktar sig mot krypterade kolumner och kryptera sådana data innan de skickas till databasen.

Utan parameterisering skickar .NET Framework-dataprovidern varje instruktion, som du skapar i frågeredigeraren, 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 NCHAR(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 för det aktuella frågeredigerarens fönster:

  1. Välj Sökfråga i huvudmenyn.
  2. Välj frågealternativ....
  3. Gå till exekvering>Avancerat.
  4. Välj eller avmarkera Aktivera parameterisering för Always Encrypted.
  5. Välj OK.

Så här aktiverar/inaktiverar du parameterisering för Always Encrypted för framtida Frågeredigerarens fönster:

  1. Välj Verktyg på huvudmenyn.
  2. Välj alternativ....
  3. Gå till Frågekörning>SQL Server>Avancerad.
  4. Välj eller avmarkera Aktivera parameterisering för Always Encrypted.
  5. Välj OK.

Om du kör en fråga i ett frågeredigerarefönster som använder en databasanslutning med Always Encrypted aktiverat, men parameterisering inte är aktiverad för frågeredigerarens fönster, uppmanas du att aktivera den.

Not

Parameterisering för Always Encrypted fungerar endast i Frågeredigerarens fönster som använder databasanslutningar med Always Encrypted aktiverat (se Aktivera och inaktivera parameterisering för Always Encrypted). Inga Transact-SQL variabler parametriseras om frågeredigeraren 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-beteendet i databasanslutningen är aktiverade för ett frågeredigerarefönster försöker SQL Server Management Studio parameterisera Transact-SQL variabler som uppfyller följande förutsättningar:

  • Deklareras och initieras i samma sats (inline-initiering). 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 SQL Server Management 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 SQL Server Management 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 exemplen 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

SQL Server Management 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 markeras med en varningslinje i frågeredigeraren. Om du hovrar på en deklarationssats som har markerats med en varningsstrykning, du ser resultatet av parametriseringsprocessen, inklusive värdena för de viktiga egenskaperna för det resulterande SqlParameter- -objektet (variabeln mappas till): SqlDbType, Size, Precision, Scale, SqlValue. Du kan också se den fullständiga listan över alla variabler som har parameteriserats på fliken Varning i vyn Fellista. Om du vill öppna vyn fellista väljer du Visa på huvudmenyn och väljer sedan fellista.

Om SQL Server Management Studio har försökt parametrisera en variabel, men parameteriseringen har misslyckats, markeras deklarationen för variabeln med ett understruket fel. Om du för muspekaren över deklarationssatsen som har markerats med en felunderstrykning får du resultatet om felet visas. Du kan också se den fullständiga listan över parameteriseringsfel för alla variabler på fliken Fel i vyn fellista. Om du vill öppna vyn fellista väljer du Visa på huvudmenyn och väljer sedan fellista.

Skärmbilden nedan visar ett exempel på sex variabeldeklarationer. SQL Server Management Studio har parametriserat de tre första variablerna. De tre sista variablerna uppfyllde inte de förutsättningar som krävs för parameterisering och därför försökte SQL Server Management Studio inte parametrisera dem (deras deklarationer markeras inte på något sätt).

Skärmbild som visar ett exempel på sex variabeldeklarationer med tre parametriserade och tre fel och tillhörande varningsmeddelanden.

Ett annat exempel nedan visar två variabler som uppfyller förutsättningar för parameterisering, men parameteriseringsförsöket misslyckades eftersom variablerna har initierats felaktigt.

Skärmbild som visar ett exempel på två variabeldeklarationer som slutligen misslyckas med de associerade felmeddelandena.

Obs

Eftersom Always Encrypted stöder en begränsad delmängd av typkonverteringar måste datatypen för en Transact-SQL variabel i många fall vara samma som typen av måldatabaskolumn. Anta till exempel 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.

Anteckning

Utan parameterisering bearbetas hela frågan, inklusive typkonverteringar, i SQL Server/Azure SQL Database. När parameteriseringen är aktiverad utförs vissa typkonverteringar av .NET Framework i SQL Server Management Studio. På grund av skillnader mellan .NET Framework-typsystemet och SQL Server-typsystemet (t.ex. 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 är aktiverad.

Nästa steg

Se även