Querykolommen uitvoeren met Always Encrypted met Azure Data Studio

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

In dit artikel wordt beschreven hoe u kolommen opvraagt, versleuteld met Always Encrypted- met behulp van Azure Data Studio-. Met Azure Data Studio kunt u het volgende doen:

• Ciphertext-waarden ophalen die zijn opgeslagen in versleutelde kolommen.
• Platte tekstwaarden ophalen die zijn opgeslagen in versleutelde kolommen.
• Tekst zonder opmaakwaarden verzenden die gericht zijn op versleutelde kolommen (bijvoorbeeld in INSERT- of UPDATE-instructies en als opzoekparameter van de WHERE-componenten in SELECT-instructies).

Coderingstekstwaarden ophalen die zijn opgeslagen in versleutelde kolommen

In deze sectie wordt beschreven hoe u gegevens ophaalt die zijn opgeslagen in versleutelde kolommen als coderingstekst.

Stappen

1. Zorg ervoor dat u Always Encrypted hebt uitgeschakeld voor de databaseverbinding voor het queryvenster, waaruit u een SELECT query uitvoert voor het ophalen van coderingstekstwaarden. Zie Always Encrypted in- en uitschakelen voor een databaseverbinding hieronder.
2. Voer uw SELECT-query uit. Alle gegevens die worden opgehaald uit versleutelde kolommen, worden geretourneerd als binaire (versleutelde) waarden.

Voorbeeld

Ervan uitgaande dat SSN een versleutelde kolom in de Patients tabel is, worden met de onderstaande query binaire coderingstekstwaarden opgehaald als Always Encrypted is uitgeschakeld voor de databaseverbinding.

Waarden voor platte tekst ophalen die zijn opgeslagen in versleutelde kolommen

In deze sectie wordt beschreven hoe u gegevens ophaalt die zijn opgeslagen in versleutelde kolommen als coderingstekst.

Voorwaarden

• Azure Data Studio versie 17.1 of hoger.
• U moet toegang hebben tot de kolomhoofdsleutel(s) en de metagegevens over de sleutel(en) waarmee u de kolommen beveiligt waarop u de query uitvoert. Zie Machtigingen voor het uitvoeren van query's op versleutelde kolommen hieronder voor meer informatie.
• Uw kolomhoofdsleutel(s) moeten worden opgeslagen in een sleutelkluis in Azure Key Vault of Windows Certificate Store. Azure Data Studio biedt geen ondersteuning voor andere sleutelarchieven en biedt geen ondersteuning voor kolomhoofdsleutels die zijn opgeslagen in beheerde HSM's in Azure Key Vault.

Stappen

1. Schakel Always Encrypted in voor de databaseverbinding voor het queryvenster, waaruit u een SELECT query uitvoert die uw gegevens opvraagt en ontsleutelt. Hiermee wordt de Microsoft .NET-gegevensprovider voor SQL Server (gebruikt door Azure Data Studio) geïnstrueerd om de versleutelde kolommen in de queryresultatenset te ontsleutelen. Zie Always Encrypted in- en uitschakelen voor een databaseverbinding hieronder.
2. Voer uw SELECT-query uit. Alle gegevens die worden opgehaald uit versleutelde kolommen, worden geretourneerd als niet-versleutelde waarden van de oorspronkelijke gegevenstypen.

Voorbeeld

Ervan uitgaande dat SSN een versleutelde kolom in de Patients tabel is, retourneert de onderstaande query waarden zonder opmaak als Always Encrypted is ingeschakeld voor de databaseverbinding en als u toegang hebt tot de kolomhoofdsleutel die is geconfigureerd voor de SSN kolom.

Platte tekst waarden versturen naar versleutelde kolommen

In deze sectie wordt beschreven hoe u een query uitvoert waarmee waarden worden verzonden die zijn gericht op een versleutelde kolom. Een query die bijvoorbeeld een waarde invoegt, bijwerkt of filtert op een waarde die is opgeslagen in een versleutelde kolom:

Voorwaarden

• Azure Data Studio versie 18.1 of hoger.
• U moet toegang hebben tot de kolomhoofdsleutel(s) en de metagegevens over de sleutel(en) die de kolommen beveiligen waarop u de query uitvoert. Zie Machtigingen voor het uitvoeren van query's op versleutelde kolommen hieronder voor meer informatie.
• Uw kolomhoofdsleutel(s) moeten worden opgeslagen in een sleutelkluis in Azure Key Vault of Windows Certificate Store. Azure Data Studio biedt geen ondersteuning voor andere sleutelarchieven en biedt geen ondersteuning voor kolomhoofdsleutels die zijn opgeslagen in beheerde HSM's in Azure Key Vault.

Stappen

1. Schakel Always Encrypted in voor de databaseverbinding voor het queryvenster, waaruit u een SELECT query uitvoert die uw gegevens opvraagt en ontsleutelt. Hiermee wordt de Microsoft .NET-gegevensprovider voor SQL Server (gebruikt door Azure Data Studio) geïnstrueerd om queryparameters te versleutelen die gericht zijn op versleutelde kolommen en de resultaten te ontsleutelen die zijn opgehaald uit versleutelde kolommen. Zie Always Encrypted in- en uitschakelen voor een databaseverbinding hieronder.
2. Schakel Parameterisatie in voor Always Encrypted voor het queryvenster. Zie Parameterisatie voor Always Encrypted hieronder voor meer informatie.
3. Declareer een Transact-SQL variabele en initialiseer deze met een waarde die u wilt verzenden (invoegen, bijwerken of filteren) naar de database.
4. Voer de query uit die de waarde van de Transact-SQL variabele naar de database verzendt. Azure Data Studio converteert de variabele naar een queryparameter en versleutelt de waarde voordat deze naar de database wordt verzonden.

Voorbeeld

Ervan uitgaande dat SSN een versleutelde char(11) kolom in de Patients tabel is, probeert het onderstaande script een rij te vinden die '795-73-9838' bevat in de SSN-kolom. De resultaten worden geretourneerd als Always Encrypted is ingeschakeld voor de databaseverbinding, parameterisatie voor Always Encrypted is ingeschakeld voor het queryvenster en u toegang hebt tot de kolomhoofdsleutel die is geconfigureerd voor de SSN kolom.

@SSN char(11) = '795-73-9838' SELECT * FROM [dbo].[Patiënten] WHERE [SSN] = @SSN query en de resultaten van de query." />

Machtigingen voor het uitvoeren van query's op versleutelde kolommen

Als u query's wilt uitvoeren op versleutelde kolommen, inclusief query's die gegevens in cijfertekst ophalen, hebt u de WEERGAVE VAN ELKE KOLOMHOOFDSLEUTELDEFINITIE en WEERGAVE VAN ELKE KOLOMVERSLEUTELINGSLEUTELDEFINITIE toestemmingen in de database.

Naast de bovenstaande machtigingen, om queryresultaten te ontsleutelen of om queryparameters te versleutelen (geproduceerd door het parameteriseren van Transact-SQL variabelen), hebt u ook sleutelarchiefmachtigingen nodig om toegang te krijgen tot en te gebruiken voor de kolomhoofdsleutel die de doelkolommen beveiligt. Ga naar de sectie Kolomhoofdsleutels maken en opslaan voor Always Encrypted voor gedetailleerde informatie over machtigingen voor het sleutelarchief en zoek een passende sectie voor uw sleutelarchief.

Always Encrypted in- en uitschakelen voor een databaseverbinding

Wanneer u verbinding maakt met een database in Azure Data Studio, kunt u Always Encrypted in- of uitschakelen voor de databaseverbinding. Always Encrypted is standaard uitgeschakeld.

Als u Always Encrypted inschakelt voor een databaseverbinding, wordt de Microsoft .NET-gegevensprovider voor SQL Server-, die door Azure Data Studio wordt gebruikt, geïnstrueerd om transparant te proberen:

• Ontsleutel alle waarden die zijn opgehaald uit versleutelde kolommen en die in de queryresultaten worden geretourneerd.
• Versleutel de waarden van de geparameteriseerde Transact-SQL variabelen die zijn gericht op versleutelde databasekolommen.

Als u Always Encrypted niet inschakelt voor een verbinding, probeert de Microsoft .NET-gegevensprovider voor SQL Server geen queryparameters te versleutelen of resultaten te ontsleutelen.

U kunt Always Encrypted in- of uitschakelen wanneer u verbinding maakt met een database. Zie voor algemene informatie over het maken van verbinding met een database:

• quickstart: Verbinding maken en query's uitvoeren op SQL Server met behulp van Azure Data Studio
• quickstart: Azure Data Studio gebruiken om verbinding te maken en query's uit te voeren op Azure SQL-database-

Always Encrypted inschakelen (uitschakelen):

1. Klik in het dialoogvenster Verbinding op Geavanceerd....
2. Als u Always Encrypted wilt inschakelen voor de verbinding, stelt u het veld Always Encrypted in op ingeschakelde. Als u Always Encrypted wilt uitschakelen, laat u de waarde van het veld Always Encrypted leeg of stelt u dit in op Uitgeschakelde.
3. Klik op OK- om Geavanceerde Eigenschappente sluiten.

Als u instructies wilt uitvoeren die gebruikmaken van een beveiligde enclave aan de serverzijde wanneer u Always Encrypted gebruikt met beveiligde enclaves, moet u een enclave-attestation-protocol en een enclave-attestation-URL opgeven, naast het inschakelen van Always Encrypted voor de verbinding. Zie Vereisten voor het uitvoeren van T-SQL-instructies met behulp van enclaves in Azure Data Studiovoor gedetailleerde informatie.

Tip

Als u wilt schakelen tussen Always Encrypted dat wordt ingeschakeld en uitgeschakeld voor een bestaand queryvenster, klikt u op Verbinding verbreken en klikt u vervolgens op Verbinding maken en voert u de bovenstaande stappen uit om opnieuw verbinding te maken met uw database met de gewenste waarden van het veld Always Encrypted.

Notitie

De knop Verbinding wijzigen in een queryvenster biedt momenteel geen ondersteuning voor het in- en uitschakelen van Always Encrypted.

Parameterisatie voor Always Encrypted

Parameterisatie voor Always Encrypted is een functie in Azure Data Studio 18.1 en hoger die automatisch Transact-SQL variabelen converteert naar queryparameters (exemplaren van SqlParameter Class). Hierdoor kunnen de onderliggende Microsoft .NET-gegevensprovider voor SQL Server gegevens detecteren die zijn gericht op versleutelde kolommen en dergelijke gegevens versleutelen voordat ze naar de database worden verzonden.

Zonder parameterisering geeft de Microsoft .NET-gegevensprovider voor SQL Server elke instructie door die u in het queryvenster hebt gemaakt als een niet-geparameteriseerde query. Als de query letterlijke waarden of Transact-SQL variabelen bevat die zijn gericht op versleutelde kolommen, kan de .NET Framework-gegevensprovider voor SQL Server deze niet detecteren en versleutelen voordat de query naar de database wordt verzonden. Als gevolg hiervan mislukt de query omdat het type niet overeenkomt (tussen de letterlijke tekst zonder opmaak Transact-SQL variabele en de versleutelde kolom). De volgende query mislukt bijvoorbeeld zonder parameters, ervan uitgaande dat de kolom SSN is versleuteld.

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

Parameterisatie in- en uitschakelen voor Always Encrypted

Parameterisatie voor Always Encrypted is standaard uitgeschakeld.

Parameterisatie in- of uitschakelen voor Always Encrypted:

1. Selecteer >Voorkeuren>Instellingen (Code>Voorkeuren>Instellingen op Mac).
2. Navigeer naar Data>Microsoft SQL Server.
3. Schakel Parameterisatie voor Always Encryptedin of uit.
4. Sluit het venster Instellingen.

Notitie

Parameterisatie voor Always Encrypted werkt alleen in een query die databaseverbindingen gebruikt met Always Encrypted ingeschakeld (zie Always Encrypted in- en uitschakelen voor een databaseverbinding). Er worden geen Transact-SQL variabelen geparameteriseerd als in het queryvenster een databaseverbinding wordt gebruikt zonder Always Encrypted ingeschakeld.

Hoe parameterisatie voor Always Encrypted werkt

Als zowel de parameterisering voor Always Encrypted als Always Encrypted zelf zijn ingeschakeld voor een queryvenster, zal Azure Data Studio proberen de variabelen Transact-SQL te parameteriseren die aan de volgende voorwaarden voldoen:

• Worden gedeclareerd en geïnitialiseerd in dezelfde instructie (inline initialisatie). Variabelen die zijn gedeclareerd met behulp van afzonderlijke SET-instructies, worden niet geparameteriseerd.
• Worden geïnitialiseerd met één letterlijke waarde. Variabelen die zijn geïnitialiseerd met behulp van expressies, inclusief operatoren of functies, worden niet geparameteriseerd.

Hieronder vindt u voorbeelden van variabelen die door Azure Data Studio worden geparameteraliseerd.

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

Hier volgen enkele voorbeelden van variabelen die azure Data Studio niet probeert te parameteriseren:

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

Voor een poging tot parametrisering om succesvol te zijn:

• Het type letterlijke waarde dat wordt gebruikt voor de initialisatie van de variabele die moet worden geparametriseerd, moet overeenkomen met het type in de variabeledeclaratie.
• Als het gedeclareerde type van de variabele een datumtype of een tijdtype is, moet de variabele worden geïnitialiseerd met behulp van een tekenreeks met een van de volgende ISO 8601-compatibele indelingen.

Hier volgen voorbeelden van Transact-SQL variabelendeclaraties die leiden tot parameteriseringsfouten:

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 maakt gebruik van IntelliSense om u te informeren welke variabelen kunnen worden geparameteriseerd en welke parameterisatiepogingen mislukken (en waarom).

Een declaratie van een variabele die kan worden geparameteriseerd, wordt gemarkeerd met een infobericht-onderstreping in het query-venster. Als u de muisaanwijzer op een declaratie-instructie plaatst die is gemarkeerd met een onderstreept infobericht, ziet u het bericht met de resultaten van het parameterisatieproces, inclusief de waarden van de belangrijkste eigenschappen van de resulterende SqlParameter Class-object (de variabele is toegewezen aan: SqlDbType-, Grootte, Precisie, Schaal, SqlValue). U kunt ook de volledige lijst zien van alle variabelen die zijn geparameteriseerd in de weergave Problemen. Als u de weergave Problemen wilt openen, selecteert u >Problemen weergeven.

Als Azure Data Studio heeft geprobeerd een variabele te parameteriseren, maar de parameterisatie is mislukt, wordt de declaratie van de variabele gemarkeerd met een fout onderstrepen. Als u de aanwijzer op de declaratie plaatst die is gemarkeerd met een foutonderstreping, ziet u informatie over de fout. U kunt ook de volledige lijst met parameterfouten voor alle variabelen in de weergave Problemen bekijken.

Notitie

Aangezien Always Encrypted een beperkte subset van typeconversies ondersteunt, is het in veel gevallen vereist dat het gegevenstype van een Transact-SQL variabele hetzelfde is als het type doeldatabasekolom. Als het type van de SSN kolom in de Patients tabel bijvoorbeeld char(11)is, mislukt de onderstaande query, omdat het type @SSN variabele, dat nchar(11)is, niet overeenkomt met het type van de kolom.

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.

Notitie

Zonder parameterisering wordt de hele query, inclusief typeconversies, verwerkt in SQL Server/Azure SQL Database. Als parameters zijn ingeschakeld, worden sommige typeconversies uitgevoerd door Microsoft .NET Data Provider voor SQL Server in Azure Data Studio. Vanwege verschillen tussen het Microsoft .NET-typesysteem en het SQL Server-typesysteem (bijvoorbeeld een andere precisie van sommige typen, zoals float), kan een query die wordt uitgevoerd met parameterisatie ingeschakeld, verschillende resultaten opleveren dan de query die wordt uitgevoerd zonder parameters ingeschakeld.

Volgende stappen

• Toepassingen ontwikkelen met Always Encrypted

Zie ook

• Altijd Versleuteld