Oszlopok lekérdezése az Always Encrypted használatával az SQL Server Management Studióval

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseAzure SQL-felügyelt példány

Ez a cikk azt ismerteti, hogyan kérdezhetők le az Always EncryptedSQL Server Management Studio (SSMS)használatával titkosított oszlopok. Az SSMS-sel a következőt teheti:

• Titkosított oszlopokban tárolt titkosítási szöveges értékek lekérése.
• A titkosított oszlopokban tárolt egyszerű szöveges értékek lekérése.
• Küldjön egyszerű szöveges értékeket titkosított oszlopokra (például INSERT vagy UPDATE utasításokban, valamint a WHERE záradékok keresési paramétereként SELECT utasításokban).

Jegyzet

Az Azure Key Vaultban felügyelt HSM- tárolt oszlop főkulcsainak használatához az SSMS 18.9-es vagy újabb verziója szükséges.

Titkosított oszlopokban tárolt titkosítási szöveges értékek beolvasása

A titkosított oszlopokban tárolt adatok titkosítását lekérő SELECT-lekérdezések futtatása (az adatok visszafejtése nélkül) nem igényel hozzáférést az adatokat védő oszlop főkulcsaihoz. Értékek lekérése titkosított oszlopból titkosítási szövegként az SSMS-ben:

1. Győződjön meg arról, hogy hozzáfér az oszlopokat védő kulcsok metaadataihoz, amelyek ellen a lekérdezését futtatja. Bár nem kell hozzáférnie a tényleges oszlop főkulcsaihoz, adatbázisszintű engedélyekre van szüksége az oszlop főkulcsának és az oszloptitkosítási kulcs metaadat-objektumainak az adatbázisban való megtekintéséhez. Részletekért lásd a titkosított oszlopok lekérdezésére vonatkozó engedélyeket alább.
2. Győződjön meg arról, hogy letiltotta az Always Encrypted szolgáltatást a Lekérdezésszerkesztő ablak adatbázis-kapcsolatához, amelyből egy SELECT lekérdezést fog futtatni, amely titkosítatlan értékeket kér le. Lásd Az Always Encrypted engedélyezése és letiltása adatbázis-kapcsolathoz alább.
3. Futtassa a SELECT lekérdezést. A titkosított oszlopokból lekért adatok bináris (titkosított) értékekként lesznek visszaadva.

Példa titkosított szöveg lekérésére

Feltételezve, hogy SSN egy titkosított oszlop a Patients táblában, az alábbi lekérdezés lekéri a bináris rejtjelértékeket, ha az Always Encrypted le van tiltva az adatbázis-kapcsolathoz.

Titkosított oszlopokban tárolt egyszerű szöveges értékek beolvasása

Értékek lekérése egy titkosított oszlopból egyszerű szövegként (az értékek visszafejtéséhez):

1. Győződjön meg arról, hogy hozzáfér az oszlop főkulcsaihoz és azokhoz a kulcshoz kapcsolódó metaadatokhoz, amelyek a lekérdezése során védik az oszlopokat. Részletekért lásd a titkosított oszlopok lekérdezésére vonatkozó engedélyeket alább.
2. Győződjön meg arról, hogy engedélyezte az Always Encrypted szolgáltatást a Lekérdezésszerkesztő ablak adatbázis-kapcsolatához, amelyből egy SELECT lekérdezést fog futtatni az adatok lekéréséhez és visszafejtéséhez. Ez arra utasítja az SQL Server .NET-keretrendszer adatszolgáltatóját (amelyet az SSMS használ) a lekérdezés eredményhalmazában lévő titkosított oszlopok visszafejtésére. Lásd Az Always Encrypted engedélyezése és letiltása adatbázis-kapcsolathoz alább.
3. Futtassa a SELECT lekérdezést. A titkosított oszlopokból lekért adatok az eredeti adattípusok egyszerű szöveges értékeiként lesznek visszaadva.

Egyszerű szöveges példa beolvasása

Feltételezve, hogy az SSN egy titkosított char(11) oszlop a Patients táblában, az alábbi lekérdezés egyszerű szöveges értékeket ad vissza, ha az Always Encrypted engedélyezve van az adatbázis-kapcsolathoz, és ha rendelkezik hozzáféréssel a SSN oszlophoz konfigurált oszlop főkulcsához.

Egyszerű szöveges értékek küldése titkosított oszlopokra

Titkosított oszlopot célként szolgáló értéket küldő lekérdezés végrehajtása, például egy titkosított oszlopban tárolt érték által beszúrt, frissítéseket vagy szűrőket tartalmazó lekérdezés:

1. Győződjön meg arról, hogy hozzáfér az oszlop főkulcsaihoz és a lekérdezés által futtatott oszlopokat védő kulcsok metaadataihoz. További információ: Titkosított oszlopok lekérdezésére vonatkozó engedélyek alább.

2. Győződjön meg arról, hogy engedélyezte az Always Encrypted szolgáltatást a Lekérdezésszerkesztő ablak adatbázis-kapcsolatához, amelyből egy SELECT lekérdezést fog futtatni az adatok lekéréséhez és visszafejtéséhez. Ez arra utasítja az SQL Server .NET-keretrendszer adatszolgáltatóját (amelyet az SSMS használ) a lekérdezés eredményhalmazában lévő titkosított oszlopok visszafejtésére. Lásd Az Always Encrypted engedélyezése és letiltása adatbázis-kapcsolathoz alább.

3. Győződjön meg arról, hogy az Always Encrypted paraméterezése engedélyezve van a Lekérdezésszerkesztő ablakban. (Legalább az SSMS 17.0-s verzióját igényli.) Deklaráljon egy Transact-SQL változót, és inicializálja egy értékkel, amelyet el szeretne küldeni (beszúrás, frissítés vagy szűrés) az adatbázisba. A részletekért lásd alább az Always Encrypted paraméterezését.

4. Futtassa a lekérdezést, és küldje el a Transact-SQL változó értékét az adatbázisnak. Az SSMS átalakítja a változót egy lekérdezési paramétersé, és titkosítja az értékét, mielőtt elküldené az adatbázisba.

Példa

Feltételezve, hogy a SSN egy titkosított char(11) oszlop a Patients táblában, az alábbi szkript megpróbál megkeresni egy sort, amely '795-73-9838' tartalmaz az SSN oszlopban, és visszaadja a LastName oszlop értékét, feltéve, hogy az Always Encrypted engedélyezve van az adatbázis-kapcsolathoz, az Always Encrypted paraméterezése engedélyezve van a Lekérdezésszerkesztő ablakában, és hozzáfér a SSN oszlophoz konfigurált oszlop főkulcsához.

Titkosított oszlopok lekérdezésére vonatkozó engedélyek

Ha titkosított oszlopokon szeretne lekérdezéseket futtatni, beleértve a rejtjelszövegben adatokat lekérő lekérdezéseket is, szüksége van az adatbázis VIEW ANY COLUMN MASTER KEY DEFINITION és VIEW ANY COLUMN ENCRYPTION KEY DEFINITION engedélyeire.

A fenti engedélyeken kívül a lekérdezési eredmények visszafejtéséhez vagy a lekérdezési paraméterek titkosításához (Transact-SQL változók paraméterezésével előállított) kulcstároló-engedélyekre is szükség van a céloszlopokat védő oszlop főkulcsának eléréséhez és használatához. A kulcstároló engedélyekkel kapcsolatos részletes információkért keresse fel Az Always Encrypted- oszlop főkulcsainak létrehozása és tárolása című szakaszt, és keresse meg a kulcstárolóhoz kapcsolódó szakaszt.

Az Always Encrypted engedélyezése és letiltása adatbázis-kapcsolat esetén

Amikor SSMS-ben csatlakozik egy adatbázishoz, engedélyezheti vagy letilthatja az Always Encrypted szolgáltatást az adatbázis-kapcsolathoz. Alapértelmezés szerint az Always Encrypted le van tiltva.

Az Always Encrypted adatbázis-kapcsolathoz való engedélyezése arra utasítja az SQL Serverhez készült .NET-keretrendszer adatszolgáltatót, hogy az SQL Server Management Studio által használt, transzparens módon kísérelje meg:

• A titkosított oszlopokból lekért és a lekérdezési eredményekben visszaadott értékek visszafejtése.
• Titkosítja a titkosított adatbázisoszlopokat megcélozó paraméteres Transact-SQL változók értékeit.

Ha nem engedélyezi az Always Encrypted szolgáltatást egy kapcsolathoz, az SQL Serverhez készült .NET-keretrendszer adatszolgáltatója, az SSMS nem próbálja meg titkosítani a lekérdezési paramétereket, és nem fejti vissza az eredményeket.

Új kapcsolat létrehozásakor vagy meglévő kapcsolat módosításakor engedélyezheti vagy letilthatja az Always Encrypted szolgáltatást a Csatlakozás kiszolgálóhoz párbeszédpanelen.

Az Always Encrypted engedélyezése (letiltása):

1. Nyissa meg a Csatlakozás kiszolgálóhoz párbeszédpanelt (a részletekért lásd: Csatlakozás SQL Server-példányhoz).
2. Válassza a Beállításoklehetőséget.
3. Válassza a Always Encrypted lapot. Az Always Encrypted engedélyezéséhez válassza az Always Encrypted (oszloptitkosítás) engedélyezéselehetőséget. Az Always Encrypted letiltásához győződjön meg arról, hogy Az Always Encrypted (oszloptitkosítás) engedélyezése nincs kiválasztva.
4. Válassza és csatlakoztassa.

Borravaló

Váltás az Always Encrypted engedélyezése és letiltása között egy meglévő Lekérdezésszerkesztő ablak esetében:

1. Kattintson a jobb gombbal a Lekérdezésszerkesztő ablak tetszőleges pontjára.
2. Válassza Kapcsolat>Kapcsolat módosítása ...lehetőséget. Ekkor megnyílik a Csatlakozás kiszolgálóhoz párbeszédpanel a Lekérdezésszerkesztő ablak aktuális kapcsolatához.
3. Engedélyezze vagy tiltsa le az Always Encrypted szolgáltatást a fenti lépéseket követve, majd kattintson a Csatlakozásgombra.

Jegyzet

A kiszolgálóoldali biztonságos enklávét használó utasítások futtatásához Always Encrypted biztonságos enklávékkalvaló használatakor lásd: Transact-SQL utasítások futtatása biztonságos enklávékkal.

Az Always Encrypted paraméterezése

Az Always Encrypted paraméterezése az SQL Server Management Studio egyik funkciója, amely automatikusan konvertálja Transact-SQL változókat lekérdezési paraméterekké (SqlParameter-osztálypéldányai). (Legalább az SSMS 17.0-s verzióját igényli.) Ez lehetővé teszi, hogy az SQL Server mögöttes .NET-keretrendszer-adatszolgáltatója észlelje a titkosított oszlopokat megcélzó adatokat, és titkosítsa ezeket az adatokat, mielőtt elküldené azokat az adatbázisba.

Paraméterezés nélkül a .NET-keretrendszer adatszolgáltatója nem paraméteres lekérdezésként adja át a lekérdezésszerkesztőben létrehozott összes utasítást. Ha a lekérdezés olyan literális vagy Transact-SQL változókat tartalmaz, amelyek titkosított oszlopokat céloznak meg, az SQL Server .NET-keretrendszer adatszolgáltatója nem fogja tudni észlelni és titkosítani őket, mielőtt elküldené a lekérdezést az adatbázisnak. Ennek eredményeképpen a lekérdezés a típuseltérés miatt meghiúsul (az egyszerű szöveges konstans Transact-SQL változó és a titkosított oszlop között). A következő lekérdezés például paraméterezés nélkül meghiúsul, feltéve, hogy a SSN oszlop titkosítva van.

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

Az Always Encrypted paraméterezésének engedélyezése és letiltása

Az Always Encrypted paraméterezése alapértelmezés szerint le van tiltva.

Az Always Encrypted paraméterezésének engedélyezése/letiltása az aktuális Lekérdezésszerkesztő ablakhoz:

1. Válassza Lekérdezés lehetőséget a főmenüben.
2. Válassza Lekérdezés beállításai...lehetőséget.
3. Navigáljon Végrehajtás>Haladó.
4. Válassza ki vagy törölje a jelölést, Az Always Encryptedparaméterezésének engedélyezése.
5. Válassza OKlehetőséget.

Az Always Encrypted paraméterezésének engedélyezése/letiltása a jövőbeli Lekérdezésszerkesztő ablakaihoz:

1. Válassza Eszközök lehetőséget a főmenüben.
2. Válassza a lehetőségeket....
3. Navigáljon a(z) Lekérdezésvégrehajtási>SQL Server>Speciálispontra.
4. Válassza ki vagy törölje a jelölést, Az Always Encryptedparaméterezésének engedélyezése.
5. Válassza OKlehetőséget.

Ha olyan lekérdezést hajt végre egy Lekérdezésszerkesztő ablakban, amely az Always Encrypted funkcióval rendelkező adatbázis-kapcsolatot használ, de a paraméterezés nincs engedélyezve a Lekérdezésszerkesztő ablakban, a rendszer kérni fogja annak engedélyezését.

Jegyzet

Az Always Encrypted paraméterezése csak olyan Lekérdezésszerkesztő-ablakokban működik, amelyek olyan adatbázis-kapcsolatokat használnak, ahol az Always Encrypted engedélyezve van (lásd Az Always Encrypted paraméterezésének engedélyezése és letiltása). A rendszer nem paraméterez Transact-SQL változókat, ha a Lekérdezésszerkesztő ablak az Always Encrypted engedélyezése nélküli adatbázis-kapcsolatot használ.

Az Always Encrypted paraméterezésének működése

Ha az Always Encrypted paraméterezése és az adatbázis-kapcsolat Always Encrypted viselkedése engedélyezve van egy Lekérdezésszerkesztő ablakban, az SQL Server Management Studio megkísérli paraméterezni Transact-SQL olyan változókat, amelyek megfelelnek az alábbi előfeltételeknek:

• A deklarálás és inicializálás ugyanabban az utasításban történik (beágyazott inicializálás). A külön SET utasításokkal deklarált változók nem lesznek paraméterezve.
• Egyszerű literál használatával inicializálnak. A kifejezésekkel inicializált változók, beleértve az operátorokat vagy függvényeket, nem lesznek paraméterezve.

Az alábbiakban példákat talál a változókra, amelyeket az SQL Server Management Studio paraméterezni fog.

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

Íme néhány példa olyan változókra, amelyeket az SQL Server Management Studio nem próbál paraméterezni:

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

Sikeres paraméterezés esetén:

• A paraméterezendő változó inicializálásához használt literál típusának meg kell egyeznie a változó deklarációjának típusával.
• Ha a változó deklarált típusa dátum- vagy időtípus, a változót sztringgel kell inicializálni az alábbi ISO 8601 szabványnak megfelelő formátumokhasználatával.

Íme néhány példa Transact-SQL változódeklarációkra, amelyek paraméterezési hibákat eredményeznek:

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   

Az SQL Server Management Studio az Intellisense használatával tájékoztatja, hogy mely változók paraméterezhetők sikeresen, és mely paraméterezési kísérletek sikertelenek (és miért).

A sikeresen paraméterezhető változók deklarációja figyelmeztető aláhúzással van megjelölve a Lekérdezésszerkesztőben. Ha egy figyelmeztető aláhúzással megjelölt deklarációs utasításra mutat, látni fogja a paraméterezési folyamat eredményeit, beleértve az eredményül kapott SqlParameter objektum kulcstulajdonságainak értékeit (a változó megfeleltetve van): SqlDbType, Méret, Pontossági, Méretezési, SqlValue. A Figyelmeztetés lapján a Hibalista nézetben látható az összes sikeresen paraméterezett változó teljes listája is. A Hibalista nézet megnyitásához válassza a Nézet lehetőséget a főmenüből, majd válassza a Hibalistalehetőséget.

Ha az SQL Server Management Studio megpróbált paraméterezni egy változót, de a paraméterezés sikertelen volt, a változó deklarációja aláhúzással lesz megjelölve. Ha a hibát jelző aláhúzással megjelölt deklarációs utasításra mutat, a hibával kapcsolatos információk jelennek meg. A Hibalista nézetben a Hiba lapján megtekintheti az összes változó paraméterezési hibáinak teljes listáját is. A Hibalista nézet megnyitásához válassza a Nézet lehetőséget a főmenüből, majd válassza a Hibalistalehetőséget.

Az alábbi képernyőképen hat változó deklaráció látható. Az SQL Server Management Studio sikeresen paraméterezte az első három változót. Az utolsó három változó nem felelt meg a paraméterezés előfeltételeinek, ezért az SQL Server Management Studio nem próbálta paraméterezni őket (a deklarációk semmilyen módon nincsenek megjelölve).

Egy másik alábbi példa két olyan változót mutat be, amelyek megfelelnek a paraméterezés előfeltételeinek, de a paraméterezési kísérlet meghiúsult, mert a változók inicializálása helytelen.

Jegyzet

Mivel az Always Encrypted támogatja a típuskonverziók korlátozott részhalmazát, sok esetben szükséges, hogy egy Transact-SQL változó adattípusa megegyezik a céladatbázis oszlopának típusával, amelyet megcélzott. Ha például a SSN tábla Patients oszlopának típusa char(11), az alábbi lekérdezés sikertelen lesz, mivel a @SSN változó típusa, amely nchar(11), nem egyezik az oszlop típusával.

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.

Jegyzet

Paraméterezés nélkül a teljes lekérdezés, beleértve a típuskonverziókat is, az SQL Server/Azure SQL Database-ben lesz feldolgozva. Ha engedélyezve van a paraméterezés, bizonyos típusú átalakításokat a .NET-keretrendszer hajt végre az SQL Server Management Studióban. A .NET-keretrendszer típusa és az SQL Server típusú rendszer közötti különbségek (például egyes típusok eltérő pontossága, például lebegőpontos) miatt a paraméterezéssel végrehajtott lekérdezések eltérő eredményeket hozhatnak, mint a paraméterezés engedélyezése nélkül végrehajtott lekérdezés.

Következő lépések

• Alkalmazások fejlesztése Always Encrypted használatával

Lásd még:

• Mindig titkosítva