SE の雑記

先日書いた Manaed Instance の TDE を無効化した DB を SQL Server 2022 にリストアする際の注意点 の続きになり、厳密にはデータベース暗号化キーが含まれているデータベースを SQL Server 2022 にリストアする際の作業となります。

基本的な考え方については、Restoring a SQL Managed Instance database backup to SQL Server 2022 now GA の記載となり、必要な作業については Azure Key Vault を使用した SQL Server TDE 拡張キー管理を設定する となります。

Key Vault へのアクセスに使用されている、拡張キー管理 (Extensible Key Management : EKM) については、Windows でのみサポートされているため、SQL Server on Linux では使用することはできず、現時点では、MI で TDE が有効な DB については、リストア可能な環境は Windows のみに限定されています。

TDE が有効な DB をリストアする場合に実施が必要となる作業

適切な設定を実施していないと発生するエラー

冒頭に記載した先日投稿した内容と同一ですが、MI で TDE が有効な DB (厳密にはデータベース暗号化キーが含まれる DB) をリストアしようとすると、適切な設定が行われていない環境では、次のようなエラーが発生します。

メッセージ 33111、レベル 16、状態 4、行 2
拇印 ‘0x719730xxxxxxxxxxxxxxxxx6ECA707’ でサーバー 非対称キー が見つかりません。
メッセージ 3013、レベル 16、状態 1、行 2
RESTORE DATABASE が異常終了しています。

MI のバックアップを SQL Server 2022 にリストアする場合、TDE が有効なデータベースのリストアについてはユーザーマネージドキーを使用して暗号化をする必要があり、このキーは Key Vault の「キー」に格納する必要がありますので、SQL Server 2022 から TDE で使用する暗号化キーに対して接続をすることができるようにするというのが必要となる作業となります。

この作業が冒頭に記載した Azure Key Vault を使用した SQL Server TDE 拡張キー管理を設定する に記載されている手順となります。

必要となる作業の概要

作業の詳細は、公式のドキュメントから確認できますので、本投稿では必要となる作業の流れの概要を記載しておきたいと思います。

1. Azure AD のアプリケーションの作成

Key Vault へのアクセスについては、Azure Arc のマネージド ID ではなく、AAD のサービスプリンシパルを使用してアクセスが行われるため、使用する AAD アプリケーションの登録が必要となります。

登録したアプリケーションについて必要となる情報は、次の 2 つとなります。

• アプリケーション (クライアント) ID
• クライアントシークレット

この情報を SQL Server に登録することで、Key Vault にアクセスする際の資格情報として活用が行われます。

2. Key Vault のアクセス権の設定

AAD のアプリケーションの登録が完了したら、アプリケーションが Key Vault にアクセスできるようにアクセス許可を設定する必要があります。

コンテナーのアクセスポリシーで制御をする場合には、手順 2:Key Vault を作成します に記載されている権限を上記で作成したサービスプリンシパルに付与します。

IAM で制御をする場合は、サービスプリンシパルに対して

• キー コンテナー暗号化ユーザー

のロールを TDE で使用している Key Vault のキーに対して割り当てることで、必要なアクセス許可を満たすことができるようです。

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

標準のコンポーネントでは SQL Server は Key Vault にアクセスすることはできないため、SQL Server Connector for Microsoft Azure Key Vault を SQL Server にインストールする必要があります。(このコネクターは、Windows 環境の SQL Server 向けにのみ提供されています)

インストールはウィザードに従えば完了します。

4. SQL Server のサービスアカウントに対してレジストリのアクセス権を付与

レジストリの「HKLM\SOFTWARE\Microsoft」配下に「SQL Server Cryptographic Provider」のキーを作成し、SQL Server のサービスアカウントのフルコントロール権限を付与しておきます。

この作業を実行しておかないと非対称キーを作成する際に次のようなエラーとなります。

メッセージ 33049、レベル 16、状態 2、行 1
名前 ‘xxxx’ のキーがプロバイダーに存在しないか、アクセスが拒否されています。プロバイダー エラー コード: 2058 (プロバイダー エラー – 利用できる情報はないため、詳細については EKM プロバイダーに問い合わせてください)。

5. EKM Provider の利用の有効化

Key Vault にアクセスし、登録されているキーの利用は、SQL Server の拡張キー管理 (Extensible Key Management : EKM) により実現されていますので、次のクエリを実行して、EKM をインスタンスレベルで有効にします。

USE master;
GO

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure 'EKM provider enabled', 1;
GO
RECONFIGURE;
GO

6. 暗号化サービスプロバイダーの作成

インストールした SQL Server Connector for Microsoft Azure Key Vault を使用した暗号化サービスプロバイダーを SQL Server に追加するため、次のクエリを実行します。

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

7. Key Vault にアクセスするための資格情報を追加

Key Vault にアクセスする際の資格情報を SQL Server に登録します。

USE master;
CREATE CREDENTIAL sysadmin_ekm_cred
    WITH IDENTITY = '<Key Vault リソース名>', 
    SECRET = '<AAD のアプリケーション ID (ハイフン無し)><AAD アプリケーションのクライアントシークレット>'
FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM;
GO

「IDENTITY」「SECRET」が Azure リソースの設定となります。「SECRET」についてはAAD アプリケーションの情報を指定する必要があるのですがフォーマットが決まっており、アプリケーション ID はハイフン無しで指定し、その直後に続けて AAD アプリケーションのシークレットを指定する必要があります。

8. sysadmin ロールのログインに作成した資格情報をマッピング

上記で作成した資格情報を sysadmin ロールのログインにマッピングを行います。

ALTER LOGIN [<sysadmin ロールのログイン名>] ADD CREDENTIAL sysadmin_ekm_cred;
GO

9. 作成した sysadmin ロールのログインで非対称キーを作成

上記で資格情報をマッピングした sysadmin ロールのログインで SQL Server にアクセスし、次のクエリを実行して、非対称キーを作成します。

CREATE ASYMMETRIC KEY EKMTDEKey
FROM PROVIDER [AzureKeyVault_EKM]
WITH PROVIDER_KEY_NAME = '<Key Vault のキー名>',
CREATION_DISPOSITION = OPEN_EXISTING;

「PROVIDER_KEY_NAME」には、MI の TDE で使用している Key Vault のキー名を指定します。

上述のレジストリ (HKLM\SOFTWARE\Microsoft\SQL Server Cryptographic Provider) の作成 / アクセス権の設定が行われていないとこのタイミングでエラーが発生します。

10. TDE 用のログインと資格情報のマッピングの変更

最後の設定として、TDE 用のログインを作成し、「8.」で設定した資格情報を作成したログインにマッピングしなおし、最小限の権限のログインで動作するようにします。

CREATE LOGIN TDE_Login FROM ASYMMETRIC KEY EKMTDEKey;
GO
ALTER LOGIN [<8. で指定したログイン>] DROP CREDENTIAL sysadmin_ekm_cred;
GO
ALTER LOGIN TDE_Login ADD CREDENTIAL sysadmin_ekm_cred;
GO

作業としてはこれで完了です。ここまでの作業が実行できていれば、MI でデータベース暗号化キーが含まれているデータベースを SQL Server 2022 にリストアすることが可能です。

作業を実施する中でエラーが発生した場合は、イベントログの「アプリケーション」に、「SQL Server Connector for Microsoft Azure Key Vault」がソースの情報としてエラーの詳細が出力されますので、この情報を基に不足している作業を判断することができます。