How to Configure Azure Key Vault Integration for SQL Server on Azure Virtual Machines.

How to Configure Azure Key Vault Integration for SQL Server on Azure Virtual Machines.

/* Enable extensible key Management  */

sp_configure 'EKM provider enabled',1,1
go
reconfigure with override
go



--CREATE CREDENTIAL sysadmin_ekm_cred
--WITH IDENTITY = 'Punamvault', --keyvault Name
--SECRET = '7d4bb83a2dfb4ef2a8e59363ed17c221raksqlvm1backuppassword'
--FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov;
--go

USE [master]
GO

CREATE LOGIN [sql_login] WITH PASSWORD=N'password@123', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO

ALTER LOGIN [sql_login] DISABLE
GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [sql_login]
GO


/* Alter the login to add credentials */








/*download SQL Server Connector for Microsoft Azure Key Vault from download center */

CREATE CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov
FROM FILE = 'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll';

/* Create a credentails - In identity put the Azure key vault Name and in secret =
secret = Application ID + Keyvalue for Application */

/*AzureKeyVault_EKM_Prov is nothing but a CRYPTOGRPAHIC Provider which we have created in previous step */

CREATE CREDENTIAL sysadmin_ekm_cred
WITH IDENTITY = 'Punamvault',
SECRET = '7d4bb83a2dfb4ef2a8e59363ed17c2211pf1Lkp7TwpqqMesJzZWAhSQoaZ180Ul+MZ4hwx5GV0='
FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov

/* Attach SQL_Login with credential which i have created earlier */

ALTER LOGIN [SQL_Login]
ADD CREDENTIAL sysadmin_ekm_cred;

/* Create a ASYMMETRIC key */

CREATE ASYMMETRIC KEY sqlvm1key
FROM PROVIDER [AzureKeyVault_EKM_Prov]
WITH PROVIDER_KEY_NAME = 'sqlvm1key', -- here Provider name is key which i have created in the Azure key Vault
CREATION_DISPOSITION = OPEN_EXISTING

/* Check Key got created */

select * from sys.asymmetric_keys


/* Create credential for Azure key access */

CREATE CREDENTIAL [key01_ekm_cred]
WITH IDENTITY = 'Punamvault',
SECRET = '7d4bb83a2dfb4ef2a8e59363ed17c2211pf1Lkp7TwpqqMesJzZWAhSQoaZ180Ul+MZ4hwx5GV0='
FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov
go

CREATE LOGIN [key01_ekm_login] FROM ASYMMETRIC KEY [sqlvm1key];
go
ALTER LOGIN [key01_ekm_login] ADD CREDENTIAL [key01_ekm_cred];
go

use [rakeshtdehkm]
go
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER ASYMMETRIC KEY [sqlvm1key]
GO
ALTER DATABASE [rakeshtdehkm] SET ENCRYPTION ON
go


USE master
go

BACKUP DATABASE [rakeshtdehkm]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\rakeshtdehkm.bak'
WITH FORMAT, INIT, SKIP, NOREWIND, NOUNLOAD,
ENCRYPTION(ALGORITHM = AES_256, SERVER ASYMMETRIC KEY = [sqlvm1key]);
GO