Querykolommen uitvoeren met Always Encrypted met SQL Server Management 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 SSMS-(SQL Server Management Studio). Met SSMS kunt u het volgende doen:

• Ciphertext-waarden ophalen die zijn opgeslagen in versleutelde kolommen.
• Waarden voor platte tekst ophalen die zijn opgeslagen in versleutelde kolommen.
• Platte tekstwaarden verzenden die gericht zijn op versleutelde kolommen (bijvoorbeeld in INSERT- of UPDATE-instructies en als opzoekparameter van de WHERE-clausules in SELECT-instructies).

Notitie

Voor het gebruik van kolomhoofdsleutels die zijn opgeslagen in een beheerde HSM- in Azure Key Vault is SSMS 18.9 of een latere versie vereist.

Coderingstekstwaarden ophalen die zijn opgeslagen in versleutelde kolommen

Als u SELECT-query's uitvoert waarmee coderingstekst wordt opgehaald van gegevens die zijn opgeslagen in versleutelde kolommen (zonder de gegevens te ontsleutelen), hoeft u geen toegang te hebben tot kolomhoofdsleutels die de gegevens beveiligen. Waarden ophalen uit een versleutelde kolom als coderingstekst in SSMS:

1. Zorg ervoor dat u toegang hebt tot de metagegevens over de sleutels die de kolommen beveiligen, waarvoor u uw query uitvoert. Hoewel u geen toegang nodig hebt tot de werkelijke kolomhoofdsleutels, hebt u machtigingen op databaseniveau nodig om de metagegevensobjecten voor kolomhoofdsleutels en kolomversleutelingssleutels in de database weer te geven. Zie Machtigingen voor het uitvoeren van query's op versleutelde kolommen hieronder voor meer informatie.
2. Zorg ervoor dat u Always Encrypted hebt uitgeschakeld voor de databaseverbinding voor het venster Query-editor, waaruit u een SELECT query uitvoert voor het ophalen van coderingstekstwaarden. Zie Always Encrypted in- en uitschakelen voor een databaseverbinding hieronder.
3. Voer uw SELECT-query uit. Alle gegevens die worden opgehaald uit versleutelde kolommen, worden geretourneerd als binaire (versleutelde) waarden.

Voorbeeld van het ophalen van coderingstekst

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

Waarden ophalen uit een versleutelde kolom als tekst zonder opmaak (om de waarden te ontsleutelen):

1. Zorg ervoor dat u toegang hebt tot de kolomhoofdsleutels en de metagegevens over de sleutels 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.
2. Zorg ervoor dat u Always Encrypted hebt ingeschakeld voor de databaseverbinding voor het venster Query-editor, waaruit u een SELECT query uitvoert die uw gegevens opvraagt en ontsleutelt. Hiermee wordt de .NET Framework-gegevensprovider voor SQL Server (gebruikt door SSMS) geïnstrueerd om de versleutelde kolommen in de queryresultatenset te ontsleutelen. Zie Always Encrypted in- en uitschakelen voor een databaseverbinding hieronder.
3. Voer uw SELECT-query uit. Alle gegevens die worden opgehaald uit versleutelde kolommen, worden geretourneerd als platte tekst met de oorspronkelijke gegevenstypen.

Voorbeeld van platte tekst ophalen

Ervan uitgaande dat SSN een versleutelde char(11) 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.

nl-NL:

Platte-tekst waarden verzenden voor versleutelde kolommen

Een query uitvoeren waarmee een waarde wordt verzonden die is gericht op een versleutelde kolom, bijvoorbeeld een query die een waarde invoegt, bijwerkt of filtert op een waarde die is opgeslagen in een versleutelde kolom:

1. Zorg ervoor dat u toegang hebt tot de kolomhoofdsleutels en de metagegevens voor de sleutels waarmee de kolommen worden beveiligd waarop uw query wordt uitgevoerd. Zie Machtigingen voor het uitvoeren van query's op versleutelde kolommen hieronder voor meer informatie.

2. Zorg ervoor dat u Always Encrypted hebt ingeschakeld voor de databaseverbinding voor het venster Query-editor, waaruit u een SELECT query uitvoert die uw gegevens opvraagt en ontsleutelt. Hiermee wordt de .NET Framework-gegevensprovider voor SQL Server (gebruikt door SSMS) geïnstrueerd om de versleutelde kolommen in de queryresultatenset te ontsleutelen. Zie Always Encrypted in- en uitschakelen voor een databaseverbinding hieronder.

3. Zorg ervoor dat parameterisatie voor Always Encrypted is ingeschakeld voor het venster Query-editor. (Vereist ten minste SSMS versie 17.0.) Declareer een Transact-SQL variabele en initialiseer deze met een waarde, u wilt verzenden (invoegen, bijwerken of filteren) naar de database. Zie Parameterisatie voor Always Encrypted hieronder voor meer informatie.

4. Voer de query uit die de waarde van de Transact-SQL variabele naar de database verzendt. SSMS 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 en wordt de waarde van de kolom LastName geretourneerd, waarbij Always Encrypted is ingeschakeld voor de databaseverbinding. Parameterisatie voor Always Encrypted is ingeschakeld voor het venster Query-editor en u hebt toegang tot de kolomhoofdsleutel die is geconfigureerd voor de SSN kolom.

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 coderingstekst ophalen, hebt u de VIEW ANY COLUMN MASTER KEY DEFINITION en VIEW ANY COLUMN ENCRYPTION KEY DEFINITION machtigingen in de database nodig.

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 Maak en sla kolomhoofdsleutels op voor Always Encrypted voor gedetailleerde informatie over machtigingen voor sleutelarchieven en zoek een sectie die relevant is voor uw sleutelarchief.

Always Encrypted in- en uitschakelen voor een databaseverbinding

Wanneer u verbinding maakt met een database in SSMS, 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 .NET Framework-gegevensprovider voor SQL Server, die wordt gebruikt door SQL Server Management Studio, geïnstrueerd om transparant te proberen:

• Ontsleutel alle waarden die worden opgehaald uit versleutelde kolommen en in queryresultaten terugkomen.
• 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 .NET Framework-gegevensprovider voor SQL Server, SSMS, geen queryparameters te versleutelen of resultaten te ontsleutelen.

U kunt Always Encrypted in- of uitschakelen wanneer u een nieuwe verbinding maakt of als u een bestaande verbinding wijzigt met behulp van het dialoogvenster Verbinding maken met server.

Always Encrypted inschakelen (uitschakelen):

1. Open dialoogvenster Verbinding maken met server (zie Verbinding maken met een SQL Server-exemplaar voor meer informatie).
2. Selecteer opties .
3. Selecteer het tabblad Always Encrypted. Als u Always Encrypted wilt inschakelen, selecteert u Always Encrypted (kolomversleuteling) inschakelen. Als u Always Encrypted wilt uitschakelen, moet u ervoor zorgen dat Always Encrypted (kolomversleuteling) inschakelen niet is geselecteerd.
4. Selecteer Verbinding maken.

Tip

Schakelen tussen ingeschakeld en uitgeschakeld voor Always Encrypted in een bestaand Query-editorvenster:

1. Klik met de rechtermuisknop op een willekeurige plaats in het venster Queryeditor.
2. Selecteer Connection>Change Connection .... Hiermee opent u het dialoogvenster Verbinding maken met server voor de huidige verbinding voor het venster Queryeditor.
3. Schakel Always Encrypted in of uit, volg de bovenstaande stappen en klik op Verbinding maken.

Notitie

Als u instructies wilt uitvoeren die gebruikmaken van een beveiligde enclave aan de serverzijde wanneer u Always Encrypted gebruikt met beveiligde enclaves, raadpleegt u Instructies uitvoeren Transact-SQL met behulp van beveiligde enclaves.

Parameterisatie voor Always Encrypted

Parameterisatie voor Always Encrypted is een functie in SQL Server Management Studio die automatisch Transact-SQL variabelen converteert naar queryparameters (exemplaren van SqlParameter Class). (Vereist ten minste SSMS versie 17.0.) Hierdoor kan de onderliggende .NET Framework-gegevensprovider voor SQL Server gegevens detecteren die zijn gericht op versleutelde kolommen en dergelijke gegevens versleutelen voordat deze naar de database worden verzonden.

Zonder parameters geeft de .NET Framework-gegevensprovider elke instructie door, die u in de Query-editor maakt 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 NCHAR(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 voor het huidige venster queryeditor:

1. Selecteer Query in het hoofdmenu.
2. Selecteer query-instellingen....
3. Navigeer naar Uitvoering>Geavanceerd.
4. Schakel Parameterisatie inschakelen voor Always Encrypted-in of uit.
5. Selecteer OK-.

Parameterisatie in- of uitschakelen voor Always Encrypted voor toekomstige queryeditorvensters:

1. Kies Extra vanuit het hoofdmenu.
2. Selecteer opties....
3. Navigeer naar queryuitvoering>SQL Server>Advanced.
4. Schakel Parameterisatie inschakelen voor Always Encrypted-in of uit.
5. Selecteer OK-.

Als u een query uitvoert in een queryeditorvenster dat gebruikmaakt van een databaseverbinding met Always Encrypted ingeschakeld, maar parameterisatie niet is ingeschakeld voor het venster Query-editor, wordt u gevraagd deze in te schakelen.

Notitie

Parameterisatie voor Always Encrypted werkt alleen in queryeditorvensters die gebruikmaken van databaseverbindingen met Always Encrypted ingeschakeld (zie Parameterisatie in- en uitschakelen voor Always Encrypted-). Er worden geen Transact-SQL variabelen geparameteriseerd als het venster Query-editor een databaseverbinding gebruikt zonder Always Encrypted ingeschakeld.

Hoe parameterisatie voor Always Encrypted werkt

Als parameterisatie voor Always Encrypted en het gedrag Always Encrypted in de databaseverbinding zijn ingeschakeld voor een query-editorvenster, probeert SQL Server Management Studio de Transact-SQL-variabelen te parameteriseren die voldoen aan de volgende voorwaarden:

• 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 ziet u voorbeelden van variabelen. SQL Server Management Studio zal parameteriseren.

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

En hier volgen enkele voorbeelden van variabelen die SQL Server Management 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 parametrisatie om te slagen:

• 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 string volgens een van de volgende ISO 8601-compatibele indelingen.

Hier volgen de 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   

SQL Server Management 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 waarschuwingsstreping in de Query-editor. Als u de muisaanwijzer op een declaratie-instructie plaatst die is gemarkeerd met een waarschuwingsstreping, U ziet de resultaten van het parameterisatieproces, inclusief de waarden van de belangrijkste eigenschappen van het resulterende SqlParameter-object (de variabele is toegewezen aan): SqlDbType-, Grootte, Precision, Schaal, SqlValue. U kunt ook de volledige lijst zien van alle variabelen die zijn geparameteriseerd op het tabblad Waarschuwing van de foutlijst weergave. Als u de Foutlijst weergave wilt openen, selecteert u Weergave in het hoofdmenu en selecteert u vervolgens Foutlijst.

Als SQL Server Management Studio heeft geprobeerd een variabele te parameteriseren, maar de parameterisatie is mislukt, wordt de declaratie van de variabele gemarkeerd met een fout onderstrepen. Wanneer u de aanwijzer op de declaratieverklaring die is gemarkeerd met een foutonderlijning plaatst, krijgt u de resultaten over de fout. U kunt ook de volledige lijst met parameterisatiefouten voor alle variabelen bekijken op het tabblad Fout van het Foutlijst overzicht. Als u de foutlijst weergave wilt openen, klik op Weergave in het hoofdmenu en dan selecteert u foutlijst.

In de onderstaande schermopname ziet u een voorbeeld van zes variabeledeclaraties. SQL Server Management Studio heeft de eerste drie variabelen geparameteriseerd. De laatste drie variabelen voldoen niet aan de vereiste voorwaarden voor parameterisatie en daarom probeerde SQL Server Management Studio ze niet te parameteriseren (hun declaraties worden op geen enkele manier gemarkeerd).

In een ander voorbeeld hieronder ziet u twee variabelen die voldoen aan de vereiste voorwaarden voor parameterisatie, maar de parameterisatiepoging is mislukt omdat de variabelen onjuist zijn geïnitialiseerd.

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 van de doeldatabasekolom. Als het type 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 parameters wordt de hele query, inclusief typeconversies, verwerkt in SQL Server/Azure SQL Database. Als parameters zijn ingeschakeld, worden sommige typeconversies uitgevoerd door .NET Framework in SQL Server Management Studio. Vanwege verschillen tussen het .NET Framework-typesysteem en het SQL Server-typesysteem (bijvoorbeeld verschillende 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 parameterisatie ingeschakeld.

Volgende stappen

• Toepassingen ontwikkelen met Always Encrypted

Zie ook

• Always Encrypted