SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Azure Key Vault を使用した SQL Server の暗号化バックアップの取得

leave a comment

Azure Key Vault に暗号化で使用する非対称キーを格納して、その非対称キーを使用して暗号化されたバックアップを取得する方法について投稿してみたいと思います。

基本的な情報は

を元にしていただき、

を参考情報として利用していただければと。

Key Vault と WIndows Azure AD の知識がないので一日近く四苦八苦してしまいました…。

■証明書の作成

Key Vault には、PFX の証明書を格納するため、makecert を使用して、証明書の作成を行います。
# 今回は秘密鍵に「password」というパスワードを設定して作成しています。

makecert -r -pe -n "CN=SQL Server Encrypted Certificate" -b 01/01/2000 -e 01/01/2100 -sy 24 -sky exchange "c:\certtemp\testcert.cer" -sv "c:\certtemp\testcert.pvk"
pvk2pfx -pvk "c:\certtemp\testcert.pvk" -spc "c:\certtemp\testcert.cer" -pfx "c:\certtemp\testcert.pfx" -pi "password"

パスワードを使用しない証明書を Key Vault に格納しようとしたところ「指定された状態で使用するには無効なキーです」のエラーが発生していたので、証明書にはパスワードを設定しておいた方がよさそうです。

# もしかしたら違う原因かもしれませんが。

 

■Key Vault に証明書を格納

証明書が作成できたら、この証明書を Key Vault に格納します。

Key Vault への証明書の格納は Azure PowerShell で実行しますので、こちら からダウンロードしてインストールをしておきます。

Add-AzureAccount
Select-AzureSubscription -SubscriptionId <サブスクリプション ID> -Current
Switch-AzureMode AzureResourceManager
New-AzureResourceGroup -Name 'KeyVaultResource' -Location 'East Asia'
New-AzureKeyVault -VaultName 'SQLServerEncTestKeyVault' -ResourceGroupName 'KeyVaultResource' -Location 'East Asia' 

$securepfxpwd = ConvertTo-SecureString -String 'password' -AsPlainText -Force
Add-AzureKeyVaultKey -VaultName 'SQLServerEncTestKeyVault' -Name 'SQLServerEncryptionKey' -KeyFilePath 'c:\certtemp\testcert.pfx' -KeyFilePassword $securepfxpwd

 

Key Vault はリソースマネージャーにスイッチしてからでないと操作ができませんので、スイッチして適当なリソースグループを作ってから作成します。

今回は「SQLServerEncTestKeyVault」として作成をしていますが、この名称は URL で使用され一意になる必要がありますので、実際にテストをされる場合には他の名前を指定してください。

「$securepfxpwd」は証明書のパスワードを指定します。

この作業を実施することで Key Vault に証明書を格納することができます。

上記のスクリプトであれば、「SQLServerEncTestKeyVault」という Key Vault 「SQLServerEncryptionKey」という名称で、証明書を格納した状態となります。

 

■Azure AD の設定

SQL Server が Key Vault に格納された証明書にアクセスする場合には、Azure AD のアプリケーションとしてアクセスをしに行きますので、WAAD にアプリケーションを登録します。

Key Vault を作成したサブスクリプションで使用している WAAD にクラシックポータルでアクセスをし、

  1. 「アプリケーション」を選択
  2. 「追加」
  3. 「組織で開発中のアプリケーションを追加」
  4. 「名前」に適当な名称を入力し、「種類」に「WEB アプリケーションや WEB API」
  5. 「サインオン URL」「アプリケーション ID/URL」に適当な情報を入力

    # 今回はこれらの情報は使用しないので冒頭で紹介した技術資料のように「http://test1.contoso.com 」で問題ありません。

でアプリケーションを作成します。

アプリケーションを作成したら、そのアプリケーションの 2 つの情報が必要となりますので、アプリケーションの「構成」をクリックします。

一つ目に必要となる情報が「クライアント ID」です。

これについては表示されている情報をコピーしておきます。

image

二つ目に必要となる情報が「キー」の情報です。

最初はキーは登録されていない状態ですので、「時間の選択」で適当な年を選択し「保存」をクリックして、キーの情報を取得します。

# キーの情報は保存後に一度しか表示されませんので、コピーしてあとで使用できるように残しておきます。

image

ここまで終了したら、ポータルの操作は完了です。

最後に、このアプリケーションが Key Vault にアクセスできるように Azure PowerShell で設定をします。

Set-AzureKeyVaultAccessPolicy -VaultName 'SQLServerEncTestKeyVault' -ServicePrincipalName "<クライアント ID>" -PermissionsToKeys  get,list,wrapKey,unwrapKey,create

 

これで、今回作成した「SQLServerEncTestKeyVault」という Key Vault に、WAAD に作成したアプリケーションがアクセスできるようになります。

# WAAD のアプリケーションの情報を Kery Vault にアクセスする際のシークレットとして使用するイメージなるかと。

 

冒頭で紹介した技術資料ではサービスプリンシパルとして「CLIENTID_sysadmin_login」「SECRET_sysadmin_login」「CLIENTID_DBEngine」「SECRET_DBEngine」を設定するような記載がありますが、今回のテストをするような場合はスルーで大丈夫です。

# 各役割に対して、4 つのアプリケーションを作成して、操作の役割に応じてサービスプリンシパル (クライアント ID) を使い分けるという意味で書いているのだと思いますが、テストであれば一つのアプリケーションが登録されていれば問題ありません。

 

■SQL Server Connector for Microsoft Azure Key Vault のインストール

SQL Server から Key Vault へのアクセスは標準では実施することができず、コネクターをインストールすることで接続ができるようになります。

# SQL Server 2016 CTP 2.0 でもコネクターのインストールは必要で標準機能では接続できません。

コネクターは SQL Server Connector for Microsoft Azure Key Vault  からダウンロードできますので、ダウンロードしてインストールを行います。

インストールは、インストーラーに従えば完了し、インストールが完了すると、

  • 「C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault」にサンプルの SQL とプログラムが配置
  • サービスとして「Azure Key Vault SQL Connector」が登録

されます。

 

■拡張キー管理 (EKM) プロバイダーの登録

インストールしたコネクターを拡張キー管理プロバイダーとして登録するため、以下のクエリを実行します。

# 詳細は 拡張キー管理 (EKM) 参照

USE master
GO

sp_configure 'show advanced', 1
GO
RECONFIGURE
GO
sp_configure 'EKM provider enabled', 1
GO
RECONFIGURE
GO

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

 

これにより、SQL Server が Key Vault を利用することができるようになります。

 

■Key Vault を使用した暗号化バックアップの取得

それでは、Key Vault を使用した暗号化バックアップを取得してみたいと思います。

最初に、ログインしているユーザーが Key Vault に対してアクセスができるように設定を行います。

今回は「Administrator」でログインをしてクエリを実行しますので、Administrator に対してアクセスを許可しています。

USE master;

CREATE CREDENTIAL sysadmin_ekm_cred 
WITH IDENTITY = 'SQLServerEncTestKeyVault', 
SECRET = '<クライアント ID (ハイフンなし)><キー>'
FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov

ALTER LOGIN [<サーバー名>\Administrator]
ADD CREDENTIAL sysadmin_ekm_cred;

 

「クライアント ID」「キー」については、WAAD をポータルで操作した際に取得したものになります。

  • クライアント ID : 1650xxxx-aaaa-bbbb-cccc-xxxxxxcf27a4
  • キー : bRDEo3xxxxxxxxxxxxxxxxbbbbb=

だった場合は、

SECRET = '1650xxxxaaaabbbbccccxxxxxxcf27a4bRDEo3xxxxxxxxxxxxxxxxbbbbb='

というような形式となります。

# クライアント ID はハイフンなし、クライアント ID の後ろにスペースは開けずにキーを設定

これにより、「SQLServerEncTestKeyVault」という Key Vault に対して、WAAD に登録したアプリケーションの「クライアント ID」「キー」を使用してアクセスを行うという設定ができます。

# 一般的な認証のイメージですと、クライアント ID = ユーザー名 / キー = パスワードとなるようです。

ここまでの作業が終わったら SQL Server に非対称キーの情報を登録します。

USE master
GO

CREATE ASYMMETRIC KEY SQLSERVER_ENCRYPTION_KEY 
FROM PROVIDER [AzureKeyVault_EKM_Prov]
WITH PROVIDER_KEY_NAME = 'SQLServerEncryptionKey',
CREATION_DISPOSITION = OPEN_EXISTING;

 

「PROVIDER_KEY_NAME」は Key Vault に証明書を登録した際の「Name」の設定となります。

これで、Key Vault に格納した証明書の情報を SQL Server に登録できた状態となります。

最後の作業として、SQL Server のデータベースエンジンが Key Vault にアクセスできるように資格情報を作成します。

# 先ほどの作業は Administrator という作業ユーザーが Key Vault にアクセスするための作業で、この状態では SQL Server のデータベースエンジンは Key Vault にアクセスをすることができません。

CREATE CREDENTIAL Azure_EKM_cred 
WITH IDENTITY = 'SQLServerEncTestKeyVault', 
SECRET = '<クライアント ID(ハイフンなし)><キー>' 
FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov ;

CREATE LOGIN Enc_Login 
FROM ASYMMETRIC KEY SQLSERVER_ENCRYPTION_KEY;
GO 

ALTER LOGIN Enc_Login 
ADD CREDENTIAL Azure_EKM_cred ;
GO

 

最初の作業として、データベースエンジンが使用する資格情報を作成しています。

今回は、この際のクライアントID等の情報は最初に使用したものと同一の内容を設定しています。

# データベースエンジン用のアプリケーションを WAAD に作成して、そちらのクライアント ID とキーを使ったほうがセキュリティ的にはよいかもしれないですね。

次に、データベースエンジンが操作をする際に使用する非対称キーを使用sるログインを作成し 、そのログインに先ほど作成した資格情報をマッピングします。

これにより、以下のようなクエリで暗号化したバックアップが取得できるようになります。

BACKUP DATABASE [TEST] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\TEST.bak' 
WITH FORMAT, INIT,  NAME = N'TEST-完全 データベース バックアップ', SKIP, NOREWIND, NOUNLOAD, 
ENCRYPTION(ALGORITHM = AES_128, SERVER ASYMMETRIC KEY = [SQLSERVER_ENCRYPTION_KEY]),  STATS = 10
GO

バックアップを取得する際に非対称キーを指定していますが、これにより「Enc_Login」に設定した資格情報が使用され、Key Vault の非対称キーを使用して暗号化が行われます。

 

Key Vault との連携は透過的データ暗号化等でも使用できますので、TDE で使用する場合には上記の状態から


USE TEST 
GO

CREATE ASYMMETRIC KEY SQLSERVER_ENCRYPTION_KEY 
FROM PROVIDER [AzureKeyVault_EKM_Prov]
WITH PROVIDER_KEY_NAME = 'SQLServerEncryptionKey',
CREATION_DISPOSITION = OPEN_EXISTING;


CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM = AES_256 
ENCRYPTION BY SERVER ASYMMETRIC KEY SQLSERVER_ENCRYPTION_KEY;
GO

ALTER DATABASE TEST SET ENCRYPTION ON
GO

 

というような形で設定が可能です。

WAAD と Key Vault が全く分かっていない状態で始めたので、流れが理解できるまでものすごい時間がかかりました…。

Written by masayuki.ozawa

6月 4th, 2015 at 1:05 am

Posted in SQL Server

Tagged with

Leave a Reply

*