Dotazování sloupců pomocí Always Encrypted v aplikaci SQL Server Management Studio

platí pro:SQL ServerAzure SQL Databaseazure SQL Managed Instance

Tento článek popisuje, jak dotazovat sloupce, šifrované pomocí Always Encrypted pomocí SQL Server Management Studio (SSMS). S SSMS 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).

Poznámka

Použití hlavních klíčů sloupců uložených v spravovaných HSM ve službě Azure Key Vault vyžaduje SSMS 18.9 nebo novější verzi.

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

Spouštění dotazů SELECT, které načítají šifrovaný text dat uložených v šifrovaných sloupcích (bez dešifrování dat), nevyžadují, abyste měli přístup k hlavním klíčům sloupců, které chrání data. Načtení hodnot ze šifrovaného sloupce jako šifrovaného textu v SSMS:

1. Ujistěte se, že máte přístup k metadatům o klíčích, které chrání sloupce, proti kterým spouštíte svůj dotaz. I když nepotřebujete mít přístup ke skutečným hlavním klíčům sloupců, potřebujete oprávnění na úrovni databáze k zobrazení objektů metadat hlavního klíče sloupce a šifrovacího klíče sloupce v databázi. Podrobnosti najdete níže v části Oprávnění pro dotazování šifrovaných sloupců.
2. Ujistěte se, že jste pro připojení k databázi pro okno Editoru dotazů zakázali funkci Always Encrypted, ze které spustíte SELECT dotaz, který načítá hodnoty šifrového textu. Viz Povolení a zakázání funkce Always Encrypted pro připojení k databázi níže.
3. Spusťte dotaz SELECT. Všechna data načtená z šifrovaných sloupců se vrátí jako binární (šifrované) hodnoty.

Příklad načítání šiferového textu

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

Načtení hodnot ze šifrovaného sloupce jako prostého textu (k dešifrování hodnot):

1. Ujistěte se, že máte přístup k hlavním klíčům sloupců a metadatům o klíčích, kterým chráníte sloupce, na které spouštíte dotaz. Podrobnosti najdete viz Oprávnění pro dotazování šifrovaných sloupců níže.
2. Ujistěte se, že jste pro okno Editoru dotazů povolili funkci Always Encrypted pro připojení k databázi, z tohoto okna budete spouštět dotaz typu SELECT pro načítání a dešifrování vašich dat. Tím získáte pokyn zprostředkovatele dat rozhraní .NET Framework pro SQL Server (používaný SSMS) 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.
3. 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ů.

Načtení příkladu prostého textu

Za předpokladu, že SSN je šifrovaný sloupec char(11) 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

Pokud chcete spustit dotaz, který odešle hodnotu, která cílí na šifrovaný sloupec, například dotaz, který vloží, aktualizuje nebo vyfiltruje hodnotu uloženou v šifrovaném sloupci:

1. Ujistěte se, že máte přístup k hlavním klíčům sloupců a metadatům klíčů, které chrání sloupce, se kterými se dotaz spouští. Další informace najdete v tématu Oprávnění pro dotazování šifrovaných sloupců níže.

2. Ujistěte se, že jste pro připojení k databázi v okně Editoru dotazů povolili funkci Always Encrypted, ze kterého spustíte dotaz SELECT pro načítání a dešifrování vašich dat. Tím získáte pokyn zprostředkovatele dat rozhraní .NET Framework pro SQL Server (používaný SSMS) 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.

3. Ujistěte se, že je pro okno Editoru dotazů povolená parametrizace funkce Always Encrypted. (Vyžaduje alespoň SSMS verze 17.0.) Deklarujte Transact-SQL proměnnou a inicializujete ji hodnotou, kterou chcete do databáze odeslat (vložit, aktualizovat nebo filtrovat podle). Další informace najdete v části Parametrizace pro funkci Always Encrypted níže.

4. Spusťte dotaz, který odesílá hodnotu proměnné Transact-SQL do databáze. SSMS 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 a vrátit hodnotu sloupce LastName, pokud je pro připojení k databázi povolená funkce Always Encrypted, pro okno Editor dotazů je povolená parametrizace funkce Always Encrypted a máte přístup k hlavnímu klíči sloupce nakonfigurovaného pro sloupec SSN.

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

Pokud chcete spouštět dotazy na šifrované sloupce, včetně dotazů, které načítají data v šifrovém textu, potřebujete v databázi oprávnění VIEW ANY COLUMN MASTER KEY DEFINITION a VIEW ANY COLUMN ENCRYPTION KEY DEFINITION.

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íčů pro přístup k hlavnímu klíči sloupce, který chrání dané sloupce. Podrobné informace o oprávněních úložiště klíčů naleznete v části "Vytvoření a uložení hlavních klíčů sloupců pro Always Encrypted" a vyhledejte pasáž 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 SSMS, 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 poskytovatele dat rozhraní .NET Framework pro SQL Server, který používá SQL Server Management Studio, aby se pokusil zajistit transparentnost:

• 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 rozhraní .NET Framework pro SQL Server se SSMS nepokusí šifrovat parametry dotazu nebo dešifrovat výsledky.

Funkci Always Encrypted můžete povolit nebo zakázat, když vytvoříte nové připojení nebo změníte existující připojení pomocí dialogového okna Připojit k serveru.

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

1. Otevřete dialogové okno Připojit k serveru (podrobnosti najdete v tématu Připojení k instanci SQL Serveru).
2. Vyberte Možnosti.
3. Vyberte kartu Always Encrypted. Pokud chcete funkci Always Encrypted povolit, vyberte povolit funkci Always Encrypted (šifrování sloupce). Pokud chcete funkci Always Encrypted zakázat, ujistěte se, že není vybraná volba Povolit funkci Always Encrypted (šifrování sloupců).
4. Vyberte Připojit.

Spropitné

Přepnutí mezi povoleným a zakázaným režimem Always Encrypted pro existující okno Editoru dotazů:

1. Klikněte pravým tlačítkem na libovolné místo v okně Editoru dotazů.
2. Vyberte Připojení>Změnit připojení .... Tím se otevře dialogové okno Připojit k serveru pro aktuální připojení pro okno Editoru dotazů.
3. Povolte nebo zakažte funkci Always Encrypted podle výše uvedených kroků a klikněte na Připojit.

Poznámka

Pokud chcete spouštět příkazy, které využívají zabezpečenou enklávu na straně serveru při použití Always Encrypted se zabezpečenými enklávami, přečtěte si téma Příkazy Spustit Transact-SQL pomocí zabezpečených enkláv.

Parametrizace pro Always Encrypted

Parametrizace funkce Always Encrypted v aplikaci SQL Server Management Studio, která automaticky převádí proměnné Transact-SQL na parametry dotazu (instance třídy SqlParameter). (Vyžaduje alespoň SSMS verze 17.0.) To umožňuje podkladovému zprostředkovateli dat rozhraní .NET Framework pro SQL Server detekovat data cílová na šifrované sloupce a šifrovat tato data před jejich odesláním do databáze.

Bez parametrizace předává zprostředkovatel dat rozhraní .NET Framework každý příkaz, který vytvoříte v Editoru dotazů 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 je před odesláním dotazu do databáze rozpoznat a zašifrovat. Výsledkem bude, že dotaz selže kvůli neshodě typů mezi proměnnou obsahující prostý text Transact-SQL 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 NCHAR(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 pro aktuální okno Editoru dotazů:

1. Vyberte z hlavní nabídky dotaz.
2. Vyberte Možnosti dotazu....
3. Přejděte na Provedení>Pokročilé.
4. Vyberte nebo zrušte výběr Povolit parametrizaci pro funkci Always Encrypted.
5. Vyberte OK.

Povolení nebo zakázání parametrizace pro funkci Always Encrypted pro budoucí okna Editoru dotazů:

1. V hlavní nabídce vyberte nástroje.
2. Vyberte Možnosti....
3. Přejděte na Provádění dotazů>SQL Server>Pokročilé.
4. Vyberte nebo zrušte výběr Povolit parametrizaci pro funkci Always Encrypted.
5. Vyberte OK.

Pokud spustíte dotaz v okně Editoru dotazů, které používá připojení k databázi s povolenou funkcí Always Encrypted, ale parametrizace není pro okno Editoru dotazů povolená, zobrazí se výzva k jeho povolení.

Poznámka

Parametrizace funkce Always Encrypted funguje pouze v oknech Editoru dotazů, které používají připojení k databázi s povolenou funkcí Always Encrypted (viz Povolení a zakázání parametrizace pro funkci Always Encrypted). Pokud okno Editoru dotazů 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 v okně Editoru dotazů povoleny jak parametrizace, tak chování pro Always Encrypted, nástroj SQL Server Management Studio se pokusí parametrizovat proměnné typu Transact-SQL, které splňují následující požadavky:

• 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, SQL Server Management 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 SQL Server Management 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ým typem proměnné typ data nebo časového typu, 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   

SQL Server Management Studio používá Technologii IntelliSense k informování o tom, které proměnné mohou být úspěšně parametrizovány 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 upozornění v Editoru dotazů. Pokud najedete myší na příkaz deklarace, který je označen podtržením upozornění, zobrazí se vám výsledky procesu parametrizace, včetně hodnot klíčových vlastností výsledného objektu SqlParameter (na nějž je proměnná mapována): SqlDbType, Size, Precision, Scale, SqlValue. Také můžete vidět úplný seznam všech proměnných, které byly úspěšně parametrizovány, na kartě Varování v zobrazení Seznam chyb. Pokud chcete otevřít zobrazení seznamu chyb, vyberte v hlavní nabídce Zobrazit a poté vyberte Seznam chyb.

Pokud se aplikace SQL Server Management Studio pokusila parametrizovat proměnnou, ale parametrizace selhala, deklarace proměnné bude označena podtržením chyby. Pokud přejedete kurzorem přes deklaraci označenou podtržením chyby, zobrazí se informace týkající se této chyby. Úplný seznam chyb parametrizace pro všechny proměnné můžete také zobrazit na kartě Chyba v zobrazení Seznam chyb. Pokud chcete otevřít zobrazení seznamu chyb, v hlavní nabídce vyberte Zobrazit a pak vyberte Seznam chyb.

Následující snímek obrazovky ukazuje příklad šesti deklarací proměnných. SQL Server Management Studio úspěšně parametrizoval první tři proměnné. Poslední tři proměnné nesplňují předpoklady pro parametrizaci, a proto se sql Server Management Studio nepokoušel je parametrizovat (jejich deklarace nejsou žádným způsobem označené).

Další příklad níže ukazuje dvě proměnné, které splňují předpoklady pro parametrizaci, ale pokus o parametrizaci selhal, protože proměnné jsou nesprávně inicializovány.

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, 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í rozhraní .NET Framework v aplikaci SQL Server Management Studio. Vzhledem k rozdílům mezi systémem typů rozhraní .NET Framework a systémem typů SQL Serveru (např. 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