Azure Security Tutorial #6 – Encrypt Database

In this blog, you will learn about how to encrypt database and protect encryption key by Azure Key Vault

EKM (Extensible Key Management ) Concept

encrypt

Remotely access SQL Server Virtual Machine

  1. Click your virtual machineencrypt
  2. Click ‘Connect’encrypt
  3. Enter credential to access the virtual machineencrypt

Enable TDE for your database

  1. Run SSMS (SQL Server Management Studio)
  2. Click connectencrypt
  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 databaseencrypt

Backup a key from Azure Key Vault

  1. Go to your Azure Key Vault
  2. Click ‘Key’ and then click the keyencrypt
  3. click ‘Download Backup’ and save the file to your computerencrypt

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 Serverencrypt
  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 restoredencrypt

Restart SQL Server

  1. Restart SQL Serverencrypt
  2. When SQL Server is started again, run sample query.
    USE [sampledb]
    GO
    
    SELECT * FROM [dbo].[tblTemp]
    GO

    encrypt

About engsoon

Eng Soon is a 4-time Microsoft MVP and has nearly 5 years of experience building enterprise system in the cloud.He is also a Certified Microsoft Azure.Eng Soon also have strong technical skills and analytic skill. As a developer, Besides the development task, he also involved in Project Management, Consulting, and Marketing. He has a passion for technology and sharing what he learns with others to help enable them to learn faster and be more productive. He also took part as speaker in many nationwide technical events, such as Conference, Meetup and Workshop. Currently, looking for opportunity in Cyber Security which include Cloud Security and Application Security.

View all posts by engsoon →

Leave a Reply

Your email address will not be published. Required fields are marked *