Skip to content
Branch: master
Find file History
Pull request Compare This branch is even with xlegend1024:master.
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
..
Failed to load latest commit information.
images
Readme.md

Readme.md

6. Encrypt Database

Encrypt database and protect encryption key by Azure Key Vault

EKM (Extensible Key Management ) Concept

encrypt

Architecture

lab6

Remotely access SQL Server Virtual Machine

  1. Click your virtual machine

    encrypt

  2. Click 'Connect'

    encrypt

  3. Enter credential to access the virtual machine

    encrypt

Enable TDE for your database

  1. Run SSMS (SQL Server Management Studio)

  2. Click connect

    encrypt

  3. Right click on master databse, click 'new query' to open query window.

    encrypt

  4. Copy following sql and run it

    USE [master];
    -- Use the EKM to open the asymmetric KEK that was previously created in the Key Vault
    CREATE ASYMMETRIC KEY TDE_KEY -- Give the asymmetric KEK a name in SQL Server 
    	FROM PROVIDER AzureKeyVault_EKM_Prov WITH
    	PROVIDER_KEY_NAME = 'securityworkshopkey', -- The name of the asymmetric KEK in Azure Key Vault
    	CREATION_DISPOSITION = OPEN_EXISTING -- To indicate that this is an existing key in Azure Key Vault
  5. Copy following sql and run it

    CREATE LOGIN TDE_Login
    FROM ASYMMETRIC KEY TDE_KEY ;
    GO
  6. Copy following sql and run it

    -- Alter the TDE Login to add this Credential for use by the Database Engine to access the Key Vault
    ALTER LOGIN TDE_Login 
    ADD CREDENTIAL sqlCred;
    GO
  7. Copy following sql and run it

    CREATE DATABASE [sampledb]
    CONTAINMENT = NONE
    ON  PRIMARY
    ( NAME = N'sampledb', FILENAME = N'F:\Data\sampledb.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
    LOG ON
    ( NAME = N'sampledb_log', FILENAME = N'F:\Log\sampledb_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
  8. Copy following sql and run it

    USE [sampledb];
    GO
    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM  = AES_256
    ENCRYPTION BY SERVER ASYMMETRIC KEY TDE_KEY;
    GO
  9. Copy following sql and run it

    -- Alter the database to enable transparent data encryption.
    -- This uses the asymmetric KEK you imported from Azure Key Vault to wrap your DEK.
    ALTER DATABASE [sampledb]
    SET ENCRYPTION ON ;
    GO
  10. Copy following sql and run it

    USE [sampledb]
    GO
    
    CREATE TABLE [dbo].[tblTemp](
    	[idx] [int] IDENTITY(1,1) NOT NULL,
    	[name] [nchar](10) NULL
    ) ON [PRIMARY]
    GO
    
    INSERT INTO [dbo].tblTemp VALUES ('workshop')
    GO
    
    SELECT * FROM [dbo].[tblTemp]
    GO
  11. Check TDE option of the sample database

    encrypt

Backup a key from Azure Key Vault

  1. Go to your Azure Key Vault

  2. Click 'Key' and then click the key

    encrypt

  3. click 'Download Backup' and save the file to your computer

    encrypt

Delete a Key from Azure Key Vault

  1. Go to your Azure Key Vault and find the key. Click 'Delete'.

    encrypt

Restart SQL Server

  1. Restart SQL Server

    encrypt

  2. When SQL Server is started again, you'll see your sample database is in recovery pending status.

    encrypt

Restore Key to the Azure Key Vault

  1. Go back to Azure Key Vault and click 'Keys'.

  2. Click 'Restore Backup' and select the key backup file.

    encrypt

  3. Wait until your key is restored

    encrypt

Restart SQL Server

  1. Restart SQL Server

    encrypt

  2. When SQL Server is started again, run sample query.

    USE [sampledb]
    GO
    
    SELECT * FROM [dbo].[tblTemp]
    GO

    encrypt


>> NEXT #7


EKM Hierarchy

encrypt

What is Service Principal

encrypt

You can’t perform that action at this time.