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