DuoKey SQL EKM を監視するための SQL Server DMV とカタログビュー
登録された暗号化プロバイダーに関する情報を返します。
SELECT
provider_id,
name,
guid,
version,
dll_path,
is_enabled
FROM sys.cryptographic_providers;
GO| 列 | データ型 | 説明 |
|---|---|---|
provider_id | int | プロバイダーの一意の識別子 |
name | sysname | 暗号化プロバイダーの名前 |
guid | uniqueidentifier | プロバイダーの GUID |
version | nvarchar(256) | プロバイダーのバージョン文字列 |
dll_path | nvarchar(512) | プロバイダー DLL へのパス |
is_enabled | bit | プロバイダーが有効 (1) か無効 (0) か |
データベース内の非対称キーに関する情報を返します。
SELECT
name,
principal_id,
asymmetric_key_id,
pvt_key_encryption_type_desc,
algorithm_desc,
key_length,
sid,
thumbprint,
provider_id
FROM sys.asymmetric_keys;
GO| 列 | データ型 | 説明 |
|---|---|---|
name | sysname | キー名 |
asymmetric_key_id | int | キーの一意の識別子 |
algorithm_desc | nvarchar(60) | アルゴリズムの説明 (例: RSA_2048) |
key_length | int | キーの長さ (ビット単位) |
thumbprint | varbinary(32) | 識別用のキーの拇印 |
provider_id | int | EKM プロバイダー ID (EKM 以外のキーの場合は NULL) |
provider_id が NULL でないキーは、EKM で管理されるキーです。SQL Server の資格情報に関する情報を返します。
SELECT
credential_id,
name,
credential_identity,
create_date,
modify_date,
target_type,
target_id
FROM sys.credentials
WHERE name LIKE '%EKM%' OR name LIKE '%DuoKey%';
GO| 列 | データ型 | 説明 |
|---|---|---|
credential_id | int | 一意の識別子 |
name | sysname | 資格情報名 |
credential_identity | nvarchar(4000) | ID (DuoKey の場合は Client ID) |
create_date | datetime | 資格情報が作成された日時 |
target_type | nvarchar(100) | ターゲットの種類 (CRYPTOGRAPHIC PROVIDER) |
target_id | int | ターゲットプロバイダーの ID |
SECRET の値がシステムビューに公開されることはありません。すべてのデータベースの暗号化状態を返します。これは TDE の状態を監視するための主要な DMV です。
SELECT
database_id,
encryption_state,
create_date,
regenerate_date,
modify_date,
set_date,
opened_date,
key_algorithm,
key_length,
encryptor_thumbprint,
encryptor_type,
percent_complete
FROM sys.dm_database_encryption_keys;
GO| 列 | データ型 | 説明 |
|---|---|---|
database_id | int | データベース識別子 |
encryption_state | int | 現在の暗号化状態 (0〜6) |
key_algorithm | nvarchar(32) | 使用されるアルゴリズム (例: AES) |
key_length | int | キーの長さ (ビット単位) (128、192、256) |
encryptor_thumbprint | varbinary(20) | 保護キーの拇印 |
percent_complete | real | 状態変更の完了率 |
regenerate_date | datetime | 最後のキーローテーション日 |
| 状態 | 名前 | 説明 |
|---|---|---|
| 0 | DEK なし | データベース暗号化キーが存在しません |
| 1 | 未暗号化 | DEK は存在しますが、暗号化が有効になっていません |
| 2 | 暗号化中 | 暗号化が進行中です |
| 3 | 暗号化済み | データベースが完全に暗号化されています |
| 4 | キー変更中 | キーローテーションが進行中です |
| 5 | 復号中 | 復号が進行中です |
| 6 | 保護変更中 | 保護機能の変更が進行中です |
データファイルとログファイルの I/O 統計を返します。TDE のパフォーマンスへの影響を監視するのに役立ちます。
SELECT
database_id,
file_id,
num_of_reads,
num_of_writes,
io_stall_read_ms,
io_stall_write_ms,
size_on_disk_bytes
FROM sys.dm_io_virtual_file_stats(NULL, NULL);
GO| 列 | データ型 | 説明 |
|---|---|---|
num_of_reads | bigint | 発行された読み取りの回数 |
num_of_writes | bigint | 発行された書き込みの回数 |
io_stall_read_ms | bigint | 読み取りの待機合計時間 (ミリ秒) |
io_stall_write_ms | bigint | 書き込みの待機合計時間 (ミリ秒) |
size_on_disk_bytes | bigint | ディスク上のファイルサイズ |
-- Verify EKM provider is operational
DECLARE @result INT;
BEGIN TRY
SELECT @result = COUNT(*)
FROM sys.cryptographic_providers
WHERE name = 'DuoKeyProvider' AND is_enabled = 1;
IF @result = 1
PRINT 'EKM Provider: HEALTHY';
ELSE
PRINT 'EKM Provider: NOT FOUND OR DISABLED';
END TRY
BEGIN CATCH
PRINT 'EKM Provider: ERROR - ' + ERROR_MESSAGE();
END CATCH
GO-- Track key creation and modification for rotation planning
SELECT
ak.name AS KeyName,
ak.create_date AS Created,
ak.modify_date AS Modified,
DATEDIFF(DAY, ak.create_date, GETDATE()) AS AgeDays,
CASE
WHEN DATEDIFF(DAY, ak.create_date, GETDATE()) > 365
THEN 'Rotation Recommended'
WHEN DATEDIFF(DAY, ak.create_date, GETDATE()) > 270
THEN 'Rotation Soon'
ELSE 'OK'
END AS RotationStatus
FROM sys.asymmetric_keys ak
WHERE ak.provider_id IS NOT NULL
ORDER BY ak.create_date;
GO-- Monitor ongoing encryption operations
SELECT
DB_NAME(database_id) AS DatabaseName,
encryption_state,
CASE encryption_state
WHEN 2 THEN 'Encrypting'
WHEN 4 THEN 'Key Rotating'
WHEN 5 THEN 'Decrypting'
WHEN 6 THEN 'Changing Protector'
END AS Operation,
percent_complete AS [Progress %],
modify_date AS LastUpdate,
DATEDIFF(MINUTE, modify_date, GETDATE()) AS MinutesSinceUpdate
FROM sys.dm_database_encryption_keys
WHERE encryption_state IN (2, 4, 5, 6);
GOこのクエリは、TDE 暗号化状態の完全な概要を提供し、ダッシュボードや監視ツールに適しています:
-- Comprehensive TDE encryption status dashboard
SELECT
DB_NAME(dek.database_id) AS [Database],
d.state_desc AS [DB State],
CASE dek.encryption_state
WHEN 0 THEN 'No DEK'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encrypting...'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key Changing...'
WHEN 5 THEN 'Decrypting...'
WHEN 6 THEN 'Protection Changing...'
END AS [Encryption State],
dek.percent_complete AS [Progress %],
dek.key_algorithm + '_' + CAST(dek.key_length AS VARCHAR) AS [Algorithm],
ak.name AS [Protecting Key],
cp.name AS [EKM Provider],
dek.create_date AS [DEK Created],
dek.regenerate_date AS [Last Rotated],
DATEDIFF(DAY, ISNULL(dek.regenerate_date, dek.create_date), GETDATE())
AS [Days Since Rotation],
CASE
WHEN DATEDIFF(DAY, ISNULL(dek.regenerate_date, dek.create_date), GETDATE()) > 365
THEN 'OVERDUE'
WHEN DATEDIFF(DAY, ISNULL(dek.regenerate_date, dek.create_date), GETDATE()) > 270
THEN 'DUE SOON'
ELSE 'OK'
END AS [Rotation Status]
FROM sys.dm_database_encryption_keys dek
INNER JOIN sys.databases d ON dek.database_id = d.database_id
LEFT JOIN sys.asymmetric_keys ak ON dek.encryptor_thumbprint = ak.thumbprint
LEFT JOIN sys.cryptographic_providers cp ON ak.provider_id = cp.provider_id
ORDER BY d.name;
GO