Dotazování sloupců pomocí funkce Always Encrypted se sadou Azure Data Studio

platí pro:SQL ServerAzure SQL Databaseazure SQL Managed Instance

Tento článek popisuje, jak dotazovat sloupce, šifrované pomocí Always Encrypted pomocí Azure Data Studio. Pomocí nástroje Azure Data Studio můžete:

• Načtěte hodnoty šifrového textu uložené v šifrovaných sloupcích.
• Načtení hodnot prostého textu uložených v šifrovaných sloupcích
• Odešlete hodnoty prostého textu, které cílí na šifrované sloupce (například v příkazech INSERT nebo UPDATE a jako vyhledávací parametr klauzulí WHERE v příkazech SELECT).

Načítání hodnot šifrového textu uložených v šifrovaných sloupcích

Tato část popisuje, jak načíst data uložená v šifrovaných sloupcích jako šifrový text.

Schody

1. Ujistěte se, že jste pro okno dotazu zakázali funkci Always Encrypted pro připojení k databázi, ze které spustíte SELECT dotaz načítání hodnot šifrového textu. Viz Povolení a zakázání funkce Always Encrypted pro připojení k databázi níže.
2. Spusťte dotaz SELECT. Všechna data načtená z šifrovaných sloupců se vrátí jako binární (šifrované) hodnoty.

Příklad

Za předpokladu, že SSN je šifrovaný sloupec v tabulce Patients, dotaz uvedený níže načte binární hodnoty šifrovaného textu, pokud je pro připojení k databázi zakázaná funkce Always Encrypted.

Načítání hodnot prostého textu uložených v šifrovaných sloupcích

Tato část popisuje, jak načíst data uložená v šifrovaných sloupcích jako šifrový text.

Požadavky

• Azure Data Studio verze 17.1 nebo novější
• Pro přístup k hlavním klíčům sloupců a metadatům o klíčích, které chrání sloupce, na které spouštíte dotaz, potřebujete mít přístup. Podrobnosti najdete v tématu Oprávnění pro dotazování šifrovaných sloupců níže.
• Hlavní klíče sloupců musí být uložené v trezoru klíčů ve službě Azure Key Vault nebo windows Certificate Store. Azure Data Studio nepodporuje jiná úložiště klíčů a nepodporuje hlavní klíče sloupců uložené v spravovaných HSM ve službě Azure Key Vault.

Schody

1. Povolte funkci Always Encrypted pro připojení k databázi pro okno dotazu, ze kterého spustíte SELECT dotaz načítání a dešifrování dat. Tím získáte pokyn Microsoft .NET Data Provider for SQL Server (používaného nástrojem Azure Data Studio) k dešifrování šifrovaných sloupců v sadě výsledků dotazu. Viz Povolení a zakázání funkce Always Encrypted pro připojení k databázi níže.
2. Spusťte dotaz SELECT. Všechna data načtená z šifrovaných sloupců budou vrácena jako hodnoty prostého textu původních datových typů.

Příklad

Za předpokladu, že SSN je šifrovaný sloupec v tabulce Patients, vrátí níže uvedený dotaz hodnoty prostého textu, pokud je pro připojení k databázi povolená funkce Always Encrypted, a pokud máte přístup k hlavnímu klíči sloupce nakonfigurovaného pro SSN sloupec.

Odesílání hodnot prostého textu, které cílí na šifrované sloupce

Tato část popisuje, jak spustit dotaz, který odesílá hodnoty, které cílí na šifrovaný sloupec. Například dotaz, který vloží, aktualizuje nebo vyfiltruje hodnotu uloženou v šifrovaném sloupci:

Požadavky

• Azure Data Studio verze 18.1 nebo novější
• Potřebujete přístup k hlavním klíčům sloupců a metadatům o klíčích, na kterých chráníte sloupce, na které spouštíte dotaz. Podrobnosti najdete v tématu Oprávnění pro dotazování šifrovaných sloupců níže.
• Hlavní klíče sloupců musí být uložené v trezoru klíčů ve službě Azure Key Vault nebo windows Certificate Store. Azure Data Studio nepodporuje jiná úložiště klíčů a nepodporuje hlavní klíče sloupců uložené v spravovaných HSM ve službě Azure Key Vault.

Schody

1. Povolte funkci Always Encrypted pro připojení k databázi pro okno dotazu, ze kterého spustíte SELECT dotaz načítání a dešifrování dat. Tím bude mít Microsoft .NET Data Provider pro SQL Server (používaný nástrojem Azure Data Studio) za úkol, aby šifroval parametry dotazů, jež se zaměřují na šifrované sloupce, a dešifroval výsledky získané ze šifrovaných sloupců. Viz Povolení a zakázání funkce Always Encrypted pro připojení k databázi níže.
2. Povolte parametrizaci pro funkci Always Encrypted pro okno dotazu. Podrobnosti najdete níže v části Parametrizace pro Always Encrypted.
3. Deklarujte proměnnou Transact-SQL a inicializujte ji hodnotou, kterou chcete odeslat do databáze, ať už jde o vložení, aktualizaci nebo filtrování.
4. Spusťte dotaz, který odesílá hodnotu proměnné Transact-SQL do databáze. Azure Data Studio převede proměnnou na parametr dotazu a před odesláním do databáze zašifruje její hodnotu.

Příklad

Za předpokladu, že SSN je šifrovaný sloupec char(11) v tabulce Patients, pokusí se následující skript najít řádek obsahující '795-73-9838' ve sloupci SSN. Výsledky se vrátí, pokud je pro připojení k databázi povolená funkce Always Encrypted, je pro okno dotazu povolená parametrizace funkce Always Encrypted a máte přístup k hlavnímu klíči sloupce nakonfigurovaného pro SSN sloupec.

@SSN char(11) = '795-73-9838' SELECT * FROM [dbo].[Patients] WHERE [SSN] = @SSN a výsledky dotazu." />

Oprávnění pro dotazování šifrovaných sloupců

Pokud chcete spouštět jakékoli dotazy na šifrované sloupce, včetně dotazů, které načítají data v šifrované podobě, potřebujete v databázi oprávnění ZOBRAZIT DEFINICI HLAVNÍHO KLÍČE SLOUPCE a ZOBRAZIT DEFINICI ŠIFROVACÍHO KLÍČE SLOUPCE.

Kromě výše uvedených oprávnění potřebujete k dešifrování výsledků dotazu nebo k šifrování parametrů dotazu (vytvořených parametrizací Transact-SQL proměnných) také oprávnění k úložišti klíčů, abyste mohli přistupovat k a používat hlavní klíč sloupce, který chrání cílové sloupce. Podrobné informace o oprávněních úložiště klíčů najdete v tématu Vytvoření a uložení sloupcových hlavních klíčů pro Always Encrypted a vyhledejte část relevantní pro vaše úložiště klíčů.

Povolení a zakázání funkce Always Encrypted pro připojení k databázi

Když se připojíte k databázi v Nástroji Azure Data Studio, můžete pro připojení k databázi povolit nebo zakázat funkci Always Encrypted. Ve výchozím nastavení je funkce Always Encrypted zakázaná.

Povolení funkce Always Encrypted pro připojení k databázi instruuje zprostředkovatele dat Microsoft .NET pro SQL Server, využívaného Azure Data Studio, aby se pokusil transparentně:

• Dešifrujte všechny hodnoty načtené ze šifrovaných sloupců a vrácené ve výsledcích dotazu.
• Zašifrujte hodnoty parametrizovaných Transact-SQL proměnných, které cílí na šifrované databázové sloupce.

Pokud funkci Always Encrypted pro připojení nepovolíte, zprostředkovatel dat Microsoft .NET pro SQL Server se nepokusí šifrovat parametry dotazu ani dešifrovat výsledky.

Funkci Always Encrypted můžete povolit nebo zakázat při připojení k databázi. Obecné informace o tom, jak se připojit k databázi, najdete tady:

• Rychlý start : Připojení a dotazování SQL Serveru pomocí Azure Data Studio
• Rychlý start pro : Připojení a dotazování databáze Azure SQL pomocí nástroje Azure Data Studio

Povolení (zakázání) funkce Always Encrypted:

1. V dialogovém okně připojení klepněte na tlačítko Upřesnit....
2. Chcete-li pro připojení povolit funkci Always Encrypted, nastavte pole Always Encrypted na Povoleno. Chcete-li funkci Always Encrypted zakázat, ponechte hodnotu pole Always Encrypted prázdné, nebo ji nastavte na hodnotu Zakázáno.
3. Kliknutím na tlačítko OK zavřete rozšířené vlastnosti.

Pokud chcete spouštět příkazy, které využívají zabezpečenou enklávu na straně serveru, když používáte Always Encrypted se zabezpečenými enklávami, musíte kromě povolení funkce Always Encrypted pro připojení zadat protokol ověření identity enklávy a adresu URL pro ověření identity enklávy. Podrobné informace najdete v tématu Požadavky pro spouštění příkazů T-SQL pomocí enkláv v nástroji Azure Data Studio.

(Only provide this if a different context suggests another translation is needed)

Pokud chcete přepnout mezi povoleným a zakázaným funkcí Always Encrypted pro existující okno dotazu, klikněte na Odpojit a potom klikněte na Připojit a dokončete výše uvedené kroky a znovu se připojte k databázi s požadovanými hodnotami pole Always Encrypted.

Poznámka

Tlačítko Změnit připojení v okně dotazu v současné době nepodporuje přepínání mezi povolením a zakázáním funkce Always Encrypted.

Parametrizace pro Always Encrypted

Parametrizace pro funkci Always Encrypted je funkcí v Azure Data Studio 18.1 a novějších, která převádí proměnné Transact-SQL automaticky na parametry dotazu (instance třídy SqlParameter). To umožňuje podkladovému Microsoft .NET Data Provider for SQL Server zjistit data cílící na šifrované sloupce a před odesláním těchto dat do databáze je zašifrovat.

Bez parametrizace předává zprostředkovatel dat Microsoft .NET pro SQL Server každý příkaz, který v okně dotazu vytvoříte, jako neparametrizovaný dotaz. Pokud dotaz obsahuje literály nebo Transact-SQL proměnné, které cílí na šifrované sloupce, nebude zprostředkovatel dat rozhraní .NET Framework pro SQL Server schopen rozpoznat a šifrovat je před odesláním dotazu do databáze. Jako výsledek dotaz neproběhne úspěšně kvůli neshodě typů (mezi proměnnou Transact-SQL literálu prostého textu a šifrovaným sloupcem). Například následující dotaz selže bez parametrizace za předpokladu, že je sloupec SSN zašifrovaný.

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

Povolení a zakázání parametrizace pro funkci Always Encrypted

Parametrizace funkce Always Encrypted je ve výchozím nastavení zakázaná.

Povolení nebo zakázání parametrizace pro funkci Always Encrypted:

1. Vyberte Soubor>Předvolby>Nastavení (Kód>Předvolby>Nastavení na Macu).
2. Přejděte na Data>Microsoft SQL Server.
3. Vyberte nebo zrušte výběr Povolit parametrizaci pro funkci Always Encrypted.
4. Zavřete okno nastavení.

Poznámka

Parametrizace funkce Always Encrypted funguje pouze v dotazu, který používá připojení k databázi s povolenou funkcí Always Encrypted (viz Povolení a zakázání funkce Always Encrypted pro připojení k databázi). Pokud okno dotazu používá připojení k databázi bez povolené funkce Always Encrypted, nebudou parametrizovány žádné proměnné Transact-SQL.

Jak funguje parametrizace pro funkci Always Encrypted

Pokud jsou pro okno dotazu povoleny jak parametrizace pro Always Encrypted, tak i samotný Always Encrypted, Azure Data Studio se pokusí parametrizovat proměnné Transact-SQL, které splňují následující předběžné podmínky:

• Jsou deklarovány a inicializovány ve stejném příkazu (vložená inicializace). Proměnné deklarované pomocí samostatných příkazů SET nebudou parametrizovány.
• Inicializují se pomocí jednoho literálu. Proměnné inicializované pomocí výrazů, včetně jakýchkoli operátorů nebo funkcí, nebudou parametrizovány.

Níže jsou uvedeny příklady proměnných, které Azure Data Studio parametrizuje.

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

Tady je několik příkladů proměnných, které se Azure Data Studio nepokusí parametrizovat:

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

Aby byl pokus o parametrizaci úspěšný:

• Typ literálu použitého pro inicializaci proměnné, která má být parametrizována, se musí shodovat s typem v deklaraci proměnné.
• Pokud je deklarovaný typ proměnné typ data nebo typ času, musí být proměnná inicializována pomocí řetězce pomocí jednoho z následujících formátů kompatibilních se standardem ISO 8601.

Tady jsou příklady deklarací proměnných Transact-SQL, které způsobí chyby parametrizace:

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 používá IntelliSense k informování o tom, které proměnné je možné úspěšně parametrizovat a které pokusy o parametrizaci selžou (a proč).

Deklarace proměnné, která může být úspěšně parametrizována, je označena podtržením informační zprávy v okně dotazu. Pokud najedete myší na příkaz deklarace, který byl označen podtržením informační zprávy, zobrazí se zpráva obsahující výsledky procesu parametrizace, včetně hodnot klíčových vlastností výsledného objektu třídy SqlParameter (proměnná je mapována na: SqlDbType, Size, Přesnost, Scale, SqlValue). Můžete také zobrazit úplný seznam všech proměnných, které byly úspěšně parametrizovány v zobrazení Problémy. Chcete-li otevřít Problémy zobrazení, vyberte Zobrazení>Problémy.

Pokud se Azure Data Studio pokusil parametrizovat proměnnou, ale parametrizace selhala, deklarace proměnné se označí podtržením chyby. Pokud najedete myší na deklaraci, která byla označena chybovým podtržením, zobrazí se podrobnosti o chybě. Úplný seznam chyb parametrizace můžete zobrazit také pro všechny proměnné v zobrazení Problémy.

Poznámka

Vzhledem k tomu, že funkce Always Encrypted podporuje omezenou podmnožinu převodů typů, je v mnoha případech nutné, aby datový typ proměnné Transact-SQL byl stejný jako typ cílového sloupce databáze, na který cílí. Předpokládejme například, že typ sloupce SSN v tabulce Patients je char(11), následující dotaz selže, protože typ proměnné @SSN, která je nchar(11), neodpovídá typu sloupce.

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.

Poznámka

Bez parametrizace se celý dotaz, včetně převodů typů, zpracovává uvnitř SQL Serveru nebo Azure SQL Database. Pokud je povolená parametrizace, některé převody typů provádí zprostředkovatel dat Microsoft .NET pro SQL Server v nástroji Azure Data Studio. Vzhledem k rozdílům mezi systémem typů Microsoft .NET a systémem typů SQL Serveru (například různou přesností některých typů, jako je například float), může dotaz s povoleným parametrizací vytvořit jiné výsledky než dotaz spuštěný bez povolení parametrizace.

Další kroky

• Vývoj aplikací pomocí funkce Always Encrypted

Viz také

• funkce Always Encrypted