Oszlopok lekérdezése az Always Encrypted használatával az Azure Data Studióval

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Ez a cikk azt ismerteti, hogyan kérdezhetők le az Always EncryptedAzure Data Studiohasználatával titkosított oszlopok. Az Azure Data Studióval a következő megoldásokat használhatja:

• 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).

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

Ez a szakasz a titkosított oszlopokban tárolt adatok titkosítási szövegként való lekérését ismerteti.

Lépések

1. Győződjön meg arról, hogy letiltotta az Always Encrypted szolgáltatást a lekérdezési ablak adatbázis-kapcsolatához, amelyből egy SELECT lekérdezést fog futtatni, amely beolvassa a rejtjelszöveg-értékeket. Lásd Az Always Encrypted engedélyezése és letiltása adatbázis-kapcsolathoz alább.
2. 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

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

Ez a szakasz a titkosított oszlopokban tárolt adatok titkosítási szövegként való lekérését ismerteti.

Előfeltételek

• Az Azure Data Studio 17.1-es vagy újabb verziója.
• Hozzá kell férnie az oszlop főkulcsához és a kulcs(ok) metaadataihoz, amelyek védik a lekérdezést futtató oszlopokat. Részletekért lásd a titkosított oszlopok lekérdezésére vonatkozó engedélyeket alább.
• Az oszlop főkulcsát az Azure Key Vaultban vagy a Windows Tanúsítványtárolóban található kulcstartóban kell tárolni. Az Azure Data Studio nem támogatja a többi kulcstárolót, és nem támogatja felügyelt HSM-ekben az Azure Key Vaultban tárolt oszlop-főkulcsokat.

Lépések

1. Engedélyezze az Always Encryptedt a lekérdezési 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 a Microsoft .NET-adatszolgáltatót az SQL Server (amelyet az Azure Data Studio 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.
2. 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.

Példa

Feltételezve, hogy az SSN egy titkosított 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

Ez a szakasz azt ismerteti, hogyan futtathat egy titkosított oszlopot megcélzott értékeket küldő lekérdezést. Például egy olyan lekérdezés, amely titkosított oszlopban tárolt érték alapján szúr be, frissít vagy szűr:

Előfeltételek

• Az Azure Data Studio 18.1-es vagy újabb verziója.
• Hozzá kell férnie az oszlop főkulcsához és a lekérdezést futtató oszlopok védelmét biztosító kulcs(ok) metaadataihoz. Részletekért lásd a titkosított oszlopok lekérdezésére vonatkozó engedélyeket alább.
• Az oszlop főkulcsát az Azure Key Vaultban vagy a Windows Tanúsítványtárolóban található kulcstartóban kell tárolni. Az Azure Data Studio nem támogatja a többi kulcstárolót, és nem támogatja felügyelt HSM-ekben az Azure Key Vaultban tárolt oszlop-főkulcsokat.

Lépések

1. Engedélyezze az Always Encryptedt a lekérdezési 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 utasítja a Microsoft .NET-adatszolgáltatót az SQL Server (amelyet az Azure Data Studio használ) a titkosított oszlopokat célzó lekérdezési paraméterek titkosítására és a titkosított oszlopokból lekért eredmények visszafejtésére. Lásd Az Always Encrypted engedélyezése és letiltása adatbázis-kapcsolathoz alább.
2. Engedélyezze az Always Encrypted paraméterezését a lekérdezési ablakban. A részletekért tekintse meg az always encrypted paraméterezését.
3. Deklaráljon egy Transact-SQL változót, és inicializálja azt egy olyan értékkel, amelyet el szeretne küldeni (beszúrás, frissítés vagy szűrés) az adatbázisba.
4. Futtassa a lekérdezést, amely az adatbázisnak elküldi a Transact-SQL változó értékét. Az Azure Data Studio á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 SSN egy titkosított char(11) oszlop a Patients táblában, az alábbi szkript megkísérli megtalálni az SSN oszlopban '795-73-9838' tartalmazó sort. Az eredmények akkor lesznek visszaadva, ha az Always Encrypted engedélyezve van az adatbázis-kapcsolathoz, az Always Encrypted paraméterezése engedélyezve van a lekérdezési ablakban, és ön hozzáfér a SSN oszlophoz konfigurált oszlop főkulcsához.

@SSN char(11) = '795-73-9838' SELECT * FROM [dbo]. [Betegek] WHERE [SSN] = @SSN lekérdezés és a lekérdezés eredményei." />

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, a A COLUMN MASTER KEY DEFINITION megtekintése és AZ OSZLOPTITKOSÍTÁSI KULCSDEFINÍCIÓ engedélyeinek megtekintése az adatbázisban.

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 az Azure Data Studióban 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 a Microsoft .NET-adatszolgáltatót az SQL Server, amelyet az Azure Data Studio használ, hogy transzparens módon próbáljon meg próbálkozni:

• 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 Server Microsoft .NET-adatszolgáltatója nem próbálja meg titkosítani a lekérdezési paramétereket, és nem fejti vissza az eredményeket.

Ha adatbázishoz csatlakozik, engedélyezheti vagy letilthatja az Always Encrypted szolgáltatást. Az adatbázishoz való csatlakozással kapcsolatos általános információkért lásd:

• Gyors kezdés: SQL Server csatlakoztatása és lekérdezése az Azure Data Studio használatával
• Rövid útmutató: Az Azure Data Studio használata az Azure SQL Database-hez való csatlakozáshoz és lekérdezéshez

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

1. A Kapcsolat párbeszédablakban kattintson a Speciális...elemre.
2. Ha engedélyezni szeretné az Always Encryptedt a kapcsolathoz, állítsa az Always Encrypted mezőt Engedélyezettértékre. Az Always Encrypted letiltásához hagyja üresen az Always Encrypted mező értékét, vagy állítsa Letiltottértékre.
3. Kattintson OK gombra Speciális tulajdonságokbezárásához.

A kiszolgálóoldali biztonságos enklávét használó utasítások futtatásához, ha biztonságos enklávékkal Always Encryptedet használ, meg kell adnia egy enklávéigazolási protokollt és egy enklávéigazolási URL-címet, valamint engedélyeznie kell az Always Encryptedt a kapcsolathoz. További információ: T-SQL-utasítások enklávékkal való futtatásának előfeltételei az Azure Data Studio.

Borravaló

Ha egy meglévő lekérdezésablakban engedélyezni és letiltani szeretné az Always Encryptedt, kattintson a Kapcsolat bontása elemre, majd kattintson a Csatlakozás elemre, és végezze el a fenti lépéseket az adatbázishoz való újracsatlakozáshoz az Always Encrypted mező kívánt értékeivel.

Jegyzet

A Kapcsolat módosítása gomb egy lekérdezésablakban jelenleg nem támogatja az Always Encrypted engedélyezése és letiltása közötti váltást.

Az Always Encrypted paraméterezése

Az Always Encrypted paraméterezése az Azure Data Studio 18.1-es és újabb verzióinak egyik funkciója, amely automatikusan konvertálja Transact-SQL változókat lekérdezési paraméterekké (SqlParameter-osztály példányai). Ez lehetővé teszi, hogy az SQL Server mögöttes Microsoft .NET-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 az SQL Server Microsoft .NET-adatszolgáltatója nem paraméteres lekérdezésként adja át a lekérdezési ablakban 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 CHAR(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:

1. Válassza Fájl>Beállítások>Beállítások (Kód>Beállítások>Beállítások Mac gépen).
2. Navigáljon Adatok>Microsoft SQL Server.
3. Válassza ki vagy törölje a jelölést, Az Always Encryptedparaméterezésének engedélyezése.
4. Zárja be a Beállítások ablakot.

Jegyzet

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

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

Ha az Always Encrypted és az Always Encrypted paraméterezése is engedélyezve van egy lekérdezési ablakban, az Azure Data Studio megkísérli paraméterezni Transact-SQL 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.
• Egyetlen literál használatával inicializálják. 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 az Azure Data Studio által paraméterezni kívánt változókra.

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 Azure Data 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átumok egyikével.

Í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 Azure Data 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 egy információs üzenet aláhúzásával van megjelölve a lekérdezési ablakban. Ha egy információs üzenet aláhúzásával megjelölt deklarációs utasításra mutat, megjelenik a paraméterezési folyamat eredményeit tartalmazó üzenet, beleértve az eredményként kapott SqlParameter-osztály objektum kulcstulajdonságainak értékeit (a változó a következőre van leképezve: SqlDbType, Méret, Pontossági, Méretezés, SqlValue). A Problémák nézetben az összes sikeresen paraméterezett változó teljes listája is látható. A Problémák nézet megnyitásához válassza a Nézet>Problémáklehetőséget.

Ha az Azure Data Studio megpróbált paraméterezni egy változót, de a paraméterezés sikertelen volt, a változó deklarációja hiba aláhúzással lesz megjelölve. Ha az egérmutatót a hiba aláhúzásával megjelölt deklarációs utasítás fölé viszi, megjelennek a hibával kapcsolatos eredmények. A Problémák nézetben az összes változó paraméterezési hibáinak teljes listáját is megtekintheti.

Jegyzet

Mivel az Always Encrypted a típuskonverziók korlátozott részhalmazát támogatja, sok esetben szükséges, hogy egy Transact-SQL változó adattípusa megegyezik a céladatbázis-oszlop típusával. Tegyük fel például, hogy a SSN tábla Patients oszlopának típusa char(11), az alábbi lekérdezés sikertelen lesz, mivel a @SSNnchar(11) változó típusa nem egyezik meg 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 Microsoft .NET-adatszolgáltató hajt végre az SQL Serverhez az Azure Data Studióban. A Microsoft .NET-típusrendszer és az SQL Server-típusrendszer (például egyes típusok eltérő pontossága, például a lebegőpontosság) közötti különbségek 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