Självstudie: Komma igång med Always Encrypted

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

I den här självstudien lär du dig hur du kommer igång med Always Encrypted. Den visar dig:

• Så här krypterar du valda kolumner i databasen.
• Så här gör du en förfrågan mot krypterade kolumner.

Anmärkning

Om du letar efter information om Always Encrypted med säkra enklaver, se följande handledningar i stället:

• Komma igång med Always Encrypted med säkra enklaver
• Självstudie: Komma igång med Always Encrypted med säkra enklaver i SQL Server

Förutsättningar

För den här självstudien behöver du:

• En tom databas i Azure SQL Database, Azure SQL Managed Instance eller SQL Server. Anvisningarna nedan förutsätter att databasnamnet är ContosoHR. Du måste vara ägare till databasen (medlem i db_owner-rollen). Information om hur du skapar en databas finns i Snabbstart: Skapa en enkel databas – Azure SQL Database eller Skapa en databas i SQL Server.
• Valfritt, men rekommenderas, särskilt om databasen finns i Azure: ett nyckelvalv i Azure Key Vault. Information om hur du skapar ett nyckelvalv finns i Snabbstart: Skapa ett nyckelvalv med azure-portalen.
  • Om nyckelvalvet använder behörighetsmodellen för åtkomstprinciper kontrollerar du att du har följande nyckelbehörigheter i nyckelvalvet: get, list, create, unwrap key, wrap key, verify, sign. Se Tilldela en nyckelvalv-åtkomstprincip.
  • Om du använder behörighetsmodellen för rollbaserad åtkomstkontroll (RBAC) i Azure kontrollerar du att du är medlem i Key Vault Crypto Officer roll för ditt nyckelvalv. Se Ge åtkomst till Key Vault-nycklar, certifikat och hemligheter med en rollbaserad åtkomstkontroll i Azure.
• Den senaste versionen av SQL Server Management Studio (SSMS) eller den senaste versionen av SqlServer - och Az PowerShell-modulerna. Az PowerShell-modulen krävs endast om du använder Azure Key Vault.

Steg 1: Skapa och fylla i databasschemat

I det här steget skapar du HR-schemat och tabellen Anställda . Sedan fyller du i tabellen med vissa data.

SSMS

1. Anslut till databasen. Anvisningar om hur du ansluter till en databas från SSMS finns i Snabbstart: Ansluta och fråga en Azure SQL Database eller en Azure SQL Managed Instance med SQL Server Management Studio (SSMS) eller Snabbstart: Ansluta och fråga en SQL Server-instans med SQL Server Management Studio (SSMS).

2. Öppna ett nytt frågefönster för ContosoHR-databasen .

3. Klistra in och kör nedanstående instruktioner för att skapa en ny tabell med namnet Anställda.

   CREATE SCHEMA [HR];
   GO
   
   CREATE TABLE [HR].[Employees]
   (
       [EmployeeID] [int] IDENTITY(1,1) NOT NULL
       , [SSN] [char](11) NOT NULL
       , [FirstName] [nvarchar](50) NOT NULL
       , [LastName] [nvarchar](50) NOT NULL
       , [Salary] [money] NOT NULL
   ) ON [PRIMARY];
   

4. Klistra in och kör nedanstående instruktioner för att lägga till några anställningsposter i Anställda-tabellen.

   INSERT INTO [HR].[Employees]
   (
       [SSN]
       , [FirstName]
       , [LastName]
       , [Salary]
   )
   VALUES
   (
       '795-73-9838'
       , N'Catherine'
       , N'Abel'
       , $31692
   );
   
   INSERT INTO [HR].[Employees]
   (
       [SSN]
       , [FirstName]
       , [LastName]
       , [Salary]
   )
   VALUES
   (
       '990-00-6818'
       , N'Kim'
       , N'Abercrombie'
       , $55415
   );
   

PowerShell

Kör följande kommandon i en PowerShell-session. Se till att du uppdaterar anslutningssträngen med adressen för de server- och autentiseringsinställningar som är giltiga för databasen.

Import-Module "SqlServer"

# Set your database connection string
$connectionString = "Server = myServerAddress; Database = ContosoHR; ..."

# Create a new table, named Employees.
$query = @'
    CREATE SCHEMA [HR];
    GO
    
    CREATE TABLE [HR].[Employees]
    (
        [EmployeeID] [int] IDENTITY(1,1) NOT NULL
        , [SSN] [char](11) NOT NULL
        , [FirstName] [nvarchar](50) NOT NULL
        , [LastName] [nvarchar](50) NOT NULL
        , [Salary] [money] NOT NULL
    ) ON [PRIMARY];
'@
Invoke-SqlCmd -ConnectionString $connectionString -Query $query

# Add a few rows to the Employees table.
$query = @'
    INSERT INTO [HR].[Employees]
    (
        [SSN]
        , [FirstName]
        , [LastName]
        , [Salary]
    )
    VALUES
    (
        '795-73-9838'
        , N'Catherine'
        , N'Abel'
        , $31692
    );

    INSERT INTO [HR].[Employees]
    (
        [SSN]
        , [FirstName]
        , [LastName]
        , [Salary]
    )
    VALUES
    (
        '990-00-6818'
        , N'Kim'
        , N'Abercrombie'
        , $55415
    );
'@
Invoke-SqlCmd -ConnectionString $connectionString -Query $query

Steg 2: Kryptera kolumner

I det här steget etablerar du en kolumnhuvudnyckel och en kolumnkrypteringsnyckel för Always Encrypted. Sedan krypterar du kolumnerna SSN och Lön i tabellen Anställda .

SSMS

SSMS innehåller en guide som hjälper dig att enkelt konfigurera Always Encrypted genom att konfigurera en kolumnhuvudnyckel, en kolumnkrypteringsnyckel och kryptera valda kolumner.

1. I Object Explorer, expandera Databaser>ContosoHR>Tabeller.

2. Högerklicka på tabellen Anställda och välj Kryptera kolumner för att öppna guiden Always Encrypted.

   

3. Välj Nästa på sidan Introduktion i guiden.

4. På sidan Kolumnval .

   1. Välj kolumnerna SSN och Salary . Välj deterministisk kryptering för SSN-kolumnen och randomiserad kryptering för kolumnen Lön . Deterministisk kryptering stöder frågor, till exempel punktsökningssökningar som omfattar likhetsjämförelser i krypterade kolumner. Randomiserad kryptering stöder inga beräkningar på krypterade kolumner.
   2. Lämna CEK-Auto1 (Ny) som kolumnkrypteringsnyckel för båda kolumnerna. Den här nyckeln finns inte än och genereras av guiden.
   3. Välj Nästa.

   

5. På sidan Huvudnyckelkonfiguration konfigurerar du en ny kolumnhuvudnyckel som ska genereras av guiden. Först måste du välja var du vill lagra kolumnhuvudnyckeln. Guiden stöder två typer av nyckelarkiv:

   • Azure Key Vault – rekommenderas om databasen finns i Azure
   • Windows-certifikatarkiv

   I allmänhet är Azure Key Vault det rekommenderade alternativet, särskilt om databasen finns i Azure.

   • Så här använder du Azure Key Vault:

     1. Välj Azure Key Vault.
     2. Välj Logga in och slutför inloggningen till Azure.
     3. När du har loggat in visar sidan listan över prenumerationer och nyckelvalv som du har åtkomst till. Välj en Azure-prenumeration som innehåller nyckelvalvet som du vill använda.
     4. Välj ditt tangentvalv.
     5. Välj Nästa.

     

   • Så här använder du Windows-certifikatarkivet:

     1. Välj Windows-certifikatarkiv.

     2. Låt standardvalet Aktuell användare vara kvar. Då instrueras guiden att generera ett certifikat (den nya kolumnmästarnyckeln) i Aktuell användare-lagringsutrymmet.

        

     3. Välj Nästa.

6. På sidan In-Place Krypteringsinställningar krävs ingen ytterligare konfiguration eftersom databasen inte har en enklav aktiverad. Välj Nästa.

7. På sidan Kör inställningar får du frågan om du vill fortsätta med kryptering eller generera ett PowerShell-skript som ska köras senare. Lämna standardinställningarna och välj Nästa.

8. På sidan Sammanfattning informerar guiden dig om de åtgärder som ska utföras. Kontrollera att all information är korrekt och välj Slutför.

9. På sidan Resultat kan du övervaka förloppet för guidens åtgärder. Vänta tills alla åtgärder har slutförts och välj Stäng.

   

10. (Valfritt) Utforska de ändringar som guiden har gjort i databasen.

    1. Expandera ContosoHR>Security>Always Encrypted Keys för att utforska metadataobjekten för kolumnhuvudnyckeln och kolumnkryptering som guiden skapade.

    2. Du kan också köra frågorna nedan mot systemkatalogvyerna som innehåller viktiga metadata.

       SELECT * FROM sys.column_master_keys;
       SELECT * FROM sys.column_encryption_keys
       SELECT * FROM sys.column_encryption_key_values
       

    3. I Object Explorer högerklickar du på tabellen Anställda och väljer Skripttabell som>SKAPA till>Nytt frågeredigeringsfönster. Då öppnas ett nytt frågefönster med create table-instruktionen för tabellen Anställda . Observera satsen ENCRYPTED WITH som visas i definitionerna för kolumnerna SSN och Salary .

    4. Du kan också köra frågan nedan mot sys.columns för att hämta krypteringsmetadata på kolumnnivå för de två krypterade kolumnerna.

       SELECT
       [name]
       , [encryption_type]
       , [encryption_type_desc]
       , [encryption_algorithm_name]
       , [column_encryption_key_id]
       FROM sys.columns
       WHERE [encryption_type] IS NOT NULL;
       

PowerShell

1. Skapa en kolumnhuvudnyckel i nyckelarkivet.

   • Om du använder Azure Key Vault kör du kommandona nedan för att skapa en asymmetrisk nyckel i nyckelvalvet. Se till att du anger rätt ID för din prenumeration, namnet på resursgruppen som innehåller ditt nyckelvalv och ditt nyckelvalvnamn.

     Import-Module "Az"
     Connect-AzAccount
     $subscriptionId = "<your Azure subscription ID"
     $resourceGroup = "your resource group name containing your key vault"
     $keyVaultName = "your vault name"
     $keyVaultKeyName = "your key name"
     
     # Switch to your subscription.
     Set-AzConteXt -SubscriptionId $subscriptionId
     
     # To validate the above key vault settings, get the key vault properties.
     Get-AzKeyVault -VaultName $keyVaultName -ResourceGroupName $resourceGroup 
     
     # Create a key in the key vault.
     $keyVaultKey = Add-AzKeyVaultKey -VaultName $keyVaultName -Name $keyVaultKeyName -Destination "Software"
     $keyVaultKey
     

   • Om du använder Windows-certifikatarkivet kör du kommandona nedan för att skapa ett certifikat i ditt aktuella användararkiv.

     $cert = New-SelfSignedCertificate -Subject "HRCMK" -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange
     

2. Anslut till databasen med hjälp av SqlServer PowerShell-modulen. Se till att du anger en giltig anslutningssträng för databasen.

   $database = Get-SqlDatabase -ConnectionString $connectionString
   $database
   

3. Etablera ett huvudnyckelmetadataobjekt för kolumner (som refererar till huvudnyckeln för den fysiska kolumnen som du har skapat i nyckelarkivet) i databasen.

   • Om du använder Azure Key Vault kör du kommandona nedan.

     # Sign in to Azure for the SqlServer PowerShell module
     Add-SqlAzureAuthenticationContext -Interactive
     
     # Create a SqlColumnMasterKeySettings in-memory object referencing the key you've created in your key vault. 
     $cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyURL $keyVaultKey.Key.Kid
     
     # Create column master key metadata object (referencing your certificate), named CMK1, in the database.
     $cmkName = "CMK1"
     New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings
     

   • Om du använder Windows-certifikatarkivet kör du kommandona nedan.

     # Create a SqlColumnMasterKeySettings in-memory object referencing your certificate.
     $cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation "CurrentUser" -Thumbprint $cert.Thumbprint
     
     # Create column master key metadata object, named CMK1, in the database.
     $cmkName = "CMK1"
     New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings
     

4. Generera en kolumnkrypteringsnyckel, kryptera den med den kolumnhuvudnyckel som du har skapat och skapa ett metadataobjekt för kolumnkrypteringsnyckeln i databasen.

   $cekName = "CEK1"
   New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName
   

5. Kryptera kolumnerna SSN och Salary i tabellen Anställda . Välj deterministisk kryptering för SSN-kolumnen och randomiserad kryptering för kolumnen Lön . Deterministisk kryptering stöder frågor, till exempel punktsökningssökningar som omfattar likhetsjämförelser i krypterade kolumner. Randomiserad kryptering stöder inga beräkningar på krypterade kolumner.

   # Encrypt the SSN and Salary columns 
   $ces = @()
   $ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.SSN" -EncryptionType "Deterministic" -EncryptionKey $cekName
   $ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.Salary" -EncryptionType "Randomized" -EncryptionKey $cekName
   Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .
   
   

6. (Valfritt) Utforska de ändringar som du har gjort i databasen.

   • Kör kommandona nedan för att fråga systemkatalogvyer som innehåller metadata om kolumnhuvudnyckeln och kolumnkrypteringsnyckeln som du skapade.

     $query = @'
     SELECT * FROM sys.column_master_keys;
     SELECT * FROM sys.column_encryption_keys
     SELECT * FROM sys.column_encryption_key_values
     '@
     
     Invoke-SqlCmd -ConnectionString $connectionString -Query $query
     

   • Kör kommandona nedan för att fråga sys.columns för att hämta krypteringsmetadata på kolumnnivå för de två krypterade kolumnerna.

     $query = @'
     SELECT
     [name]
     , [encryption_type]
     , [encryption_type_desc]
     , [encryption_algorithm_name]
     , [column_encryption_key_id]
     FROM sys.columns
     WHERE [encryption_type] IS NOT NULL;
     '@
     
     Invoke-SqlCmd -ConnectionString $connectionString -Query $query
     

Steg 3: Fråga krypterade kolumner

SSMS

1. Anslut till databasen med Always Encrypted inaktiverat för anslutningen.

   1. Öppna ett nytt frågefönster.
   2. Högerklicka var som helst i frågefönstret och välj Anslutning>Byt anslutning. Då öppnas dialogrutan Anslut till databasmotor .
   3. Välj Alternativ <<. Då visas ytterligare flikar i dialogrutan Anslut till databasmotor .
   4. Välj fliken Always Encrypted.
   5. Kontrollera att Aktivera Always Encrypted (kolumnkryptering) inte är markerat.
   6. Välj Anslut.

   

2. Klistra in och kör följande fråga. Frågan ska returnera binärkrypterade data.

   SELECT [SSN], [Salary] FROM [HR].[Employees]
   

   

3. Anslut till databasen med Always Encrypted aktiverat för anslutningen.

   1. Högerklicka var som helst i frågefönstret och välj Anslutning>Byt anslutning. Då öppnas dialogrutan Anslut till databasmotor .
   2. Välj Alternativ <<. Då visas ytterligare flikar i dialogrutan Anslut till databasmotor .
   3. Välj fliken Always Encrypted.
   4. Välj Aktivera Always Encrypted (kolumnkryptering).
   5. Välj Anslut.

   

4. Kör samma fråga igen. Eftersom du är ansluten med Always Encrypted aktiverat för databasanslutningen försöker klientdrivrutinen i SSMS dekryptera data som lagras i båda krypterade kolumnerna. Om du använder Azure Key Vault kan du uppmanas att logga in på Azure.

   

5. Aktivera parameterisering för Always Encrypted. Med den här funktionen kan du köra frågor som filtrerar data efter krypterade kolumner (eller infoga data i krypterade kolumner).

   1. Välj Fråga på huvudmenyn i SSMS.
   2. Välj Frågealternativ....
   3. Navigera till Utförande>Avancerat.
   4. Kontrollera att Aktivera parameterisering för Always Encrypted är markerat.
   5. Välj OK.

   

6. Klistra in och kör frågan nedan, som filtrerar data efter den krypterade SSN-kolumnen . Frågan ska returnera en rad som innehåller oformaterade värden.

   DECLARE @SSN [char](11) = '795-73-9838'
   SELECT [SSN], [Salary] FROM [HR].[Employees]
   WHERE [SSN] = @SSN
   

7. Om du använder Azure Key Vault som konfigurerats med behörighetsmodellen för åtkomstprinciper kan du också följa stegen nedan för att se vad som händer när en användare försöker hämta klartextdata från krypterade kolumner utan att ha åtkomst till kolumnhuvudnyckeln som skyddar data.

   1. Ta bort nyckelbehörigheten unwrap åt dig själv i åtkomstprincipen för ditt nyckelvalv. Mer information finns i Tilldela en åtkomstprincip för Key Vault.
   2. Eftersom klientdrivrutinen i SSMS cachelagrar kolumnkrypteringsnycklarna som hämtats från ett nyckelvalv i 2 timmar stänger du SSMS och öppnar det igen. Detta säkerställer att nyckelcachen är tom.
   3. Anslut till databasen med Always Encrypted aktiverat för anslutningen.
   4. Klistra in och kör följande fråga. Frågan kommer inte att lyckas och det kommer att visas ett felmeddelande som indikerar att den nödvändiga behörigheten unwrap saknas.

   SELECT [SSN], [Salary] FROM [HR].[Employees]
   

PowerShell

1. Anslut till databasen med Always Encrypted inaktiverat och kör en fråga för att läsa data från krypterade kolumner. Frågan ska returnera krypterade data som binära matriser.

   $query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
   Invoke-SqlCmd -ConnectionString $connectionString -Query $query
   

2. Anslut till databasen med Always Encrypted aktiverat och kör en fråga för att läsa data från krypterade kolumner. Eftersom du har åtkomst till kolumnhuvudnyckeln som skyddar dina krypterade kolumner bör frågan returnera klartextdata.

   $query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
   Invoke-SqlCmd -ConnectionString "$connectionString; Column Encryption Setting = Enabled" -Query $query
   

Anmärkning

Invoke-SqlCmd stöder inte frågor som kan filtrera efter eller infoga data i krypterade kolumner. Sådana frågor måste parametriseras och Invoke-SqlCmd stöder inte parametriserade frågor.

Nästa steg

• Utveckla program med Always Encrypted

Se även

• Always Encrypted-dokumentation
• Dokumentation om Always Encrypted med säkra enklaver
• Etablera Always Encrypted-nycklar med SQL Server Management Studio
• Konfigurera Always Encrypted med PowerShell
• Guiden Always Encrypted
• Frågekolumner med Always Encrypted med SQL Server Management Studio