拡張キー管理プロバイダーを使用しているセッションの情報を出力するSQL Server動的管理ビューレファレンス(162)

「Microsoft SQL Server」が稼働するデータベースシステムを運用する管理者に向け、「動的管理ビュー」の活用を軸にしたトラブル対策のためのノウハウを紹介していきます。今回は、拡張キー管理プロバイダーを使用しているセッションの情報を出力する方法について解説します。

» 2022年12月06日 05時00分 公開
[伊東敏章@IT]

この記事は会員限定です。会員登録(無料)すると全てご覧いただけます。

SQL Server動的管理ビュー一覧

 本連載では、「Microsoft SQL Server(以下、SQL Server)」で使用可能な動的管理ビューについて、動作概要や出力内容などを紹介していきます。今回は動的管理ビュー「sys.dm_cryptographic_provider_sessions」における、拡張キー管理プロバイダーを使用しているセッションの情報を出力する方法について解説します。対応バージョンは、SQL Server(サポートされている全てのバージョン)です。

概要

 SQL Serverには、拡張キー管理(EKM)を使用したデータ暗号化機能が用意されています。

 法規制順守の必要性やデータプライバシーに対する関心の高まりを受けて、さまざまなハードウェアベンダーからハードウェアセキュリティモジュール(HSM)製品が提供されています。SQL Serverの拡張キー管理では、EKM/HSMベンダーが作成したEKMモジュールをSQL Serverに登録することによって、EKMモジュールによる高度な暗号化機能やキー管理機能を利用できます。

 「sys.dm_cryptographic_provider_sessions」動的管理ビューでは、EKMプロバイダーを使用しているセッションの一覧情報を出力できます。

構文と引数

構文 sys.dm_cryptographic_provider_sessions (session_identifier)

引数名 データ型 説明
session_identifier int 返されるセッションを指定する整数
「0」=現在の接続のみ
「1」=全ての暗号接続

出力内容

列名 データ型 説明
provider_id int 暗号化サービスプロバイダーの識別番号
session_handle binary(8) 暗号化セッションハンドル
identity nvarchar(128) 暗号化サービスプロバイダーでの認証に使用するID
spid smallint 接続のセッションID

動作例

 下記のMicrosoftの公開情報を参考に「SQL Serverコネクタfor Azure Key Vault」をEKMプロバイダーとして使用して動作確認を行いました。

 リンク先を参考に、AzureやSQL Serverコネクタの準備を行い、インスタンスへEKMプロバイダーを登録しました(図1、図2)。

図1 図1 Azure Key VaultのEKMモジュールをプロバイダーとして登録したところ
図2 図2 「sys.cryptographic_providers」システムビューでプロバイダーIDを確認したところ

 登録が完了したら、下記のMicrosoftの公開情報を参考にしてセッションでデータ暗号化を行います。

 まずは接続中のセッションに、Azure Key Vaultに接続するための資格情報を追加して、Azure Key Vaultの非対称キーの参照と、非対称キーによって保護された対称キーを登録します(図3、図4)。

図3 図3 作業アカウントにAzure Key Vaultの資格情報を追加したところ
図4 図4 非対称キーの参照と、非対称キーによって保護された対称キーを登録したところ

 対称キーを登録したら、作成した対称キーでENCRYPTBYKEY関数とDECRYPTBYKEY関数を使用して、データ暗号化と復号を行うクエリを実行します(図5)。

図5 図5 Microsoftドキュメントを参考にデータ暗号化と復号を行うクエリを実行したところ

 別のセッションから「sys.dm_cryptographic_provider_sessions」動的管理ビューを出力すると、データ暗号化クエリを実行したセッションの情報が出力されました。「sys.dm_cryptographic_provider_sessions」動的管理ビューの「identity」列にはAzure Key Vault名が出力されるようです。使用しているキー名は出力されませんでした(図6)。

図6 図6 「sys.dm_cryptographic_provider_sessions」動的管理ビューを出力したところ

 次に、TDEが有効なデータベースを作成して、先ほどとは別のセッションでテーブルデータを参照して、「sys.dm_cryptographic_provider_sessions」動的管理ビューを出力してみました。新しいセッションの情報は出力されませんでしたので、TDEによる非対称キーの使用では情報は出力されないようです(図7)。

図7 図7 TDEが有効なDBのデータを参照後に「sys.dm_cryptographic_provider_sessions」動的管理ビューを出力したところ

※本Tipsは、「Windows Server 2019」上に「SQL Server 2019」をインストールした環境を想定して解説しています。

鬯ゥ謳セ�ス�オ�ス�ス�ス�コ鬯ョ�ヲ�ス�ョ髯キ�サ�ス�サ�ス�ス�ス�ソ�ス�ス�ス�ス鬯ッ�ッ�ス�ィ�ス�ス�ス�セ�ス�ス�ス�ス�ス�ス�ス�」鬯ッ�ョ�ス�エ髣費ソス�ス�・�ス�ス�ス�ウ�ス�ス�ス�ィ�ス�ス�ス�ス髯懶ス」�ス�、�ス�ス�ス�ク�ス�ス�ス�イ鬯ゥ蠅捺��ス�ソ�ス�ス�ス縺、ツ€�ス�ス�ス�ス�ス�ス�ス�」鬯ッ�ョ�ス�エ鬯ゥ蟶壽桶�ス�ュ鬮ョ�」�ス�ソ�ス�ス�ス�ス�ス�ィ鬮ッ蛹コ�サ繧托スス�ソ�ス�ス�ス�ス�ス�ス�ス�ス�ス�コ鬮」蛹�スス�オ髫エ謫セ�ス�エ�ス�ス隶難ス」�守「托スュ雜」�ス�「�ス�ス�ス�ス�ス�ス�ス�ゥ鬯ゥ蟷「�ス�「髫エ雜」�ス�「�ス�ス�ス�ス�ス�ス�ス�シ鬯ゥ蟷「�ス�「髫エ荳サ�ス隶捺サゑスソ�ス邵コ�、�つ€鬯ッ�ョ�ス�ヲ�ス�ス�ス�ェ鬩包スカ闔ィ�ス�ス�ヲ�ス�エ�ス縺、ツ€髯キ闌ィ�ス�キ�ス�ス�ス�ス�ス�ス�ス�サ鬯ッ�ッ�ス�ェ�ス�ス�ス�ュ�ス�ス�ス�ス�ス�ス�ス�イ鬯ゥ謳セ�ス�オ�ス�ス�ス�コ鬮ッ�キ�ス�キ�ス�ス�ス�カ�ス�ス�ス�ス�ス�ス�ス�ス New

筆者紹介

椎名 武史(しいな たけし)

BIPROGY株式会社(ビプロジー)所属。Microsoft MVP for Data Platform(2017~)。入社以来 SQL Serverの評価/設計/構築/教育などに携わりながらも、主にサポート業務に従事。SQL Serverのトラブル対応で社長賞の表彰を受けた経験も持つ。休日は学生時代の仲間と市民駅伝に参加し、銭湯で汗を流してから飲み会へと流れる。

伊東 敏章(いとう としあき)

BIPROGY株式会社(ビプロジー)所属。入社以来SQL Server一筋で評価/設計/構築/教育などに携わりながらも、主にサポート業務に従事。社内のプログラミングコンテストで4回の優勝経験も持つ。趣味は輪行で週末は自転車を持っての旅行。目標は色々な日本百選を制覇すること。

Copyright © ITmedia, Inc. All Rights Reserved.

スポンサーからのお知らせPR

Database Expert 鬯ョ�ォ�ス�ェ髯区サゑスソ�ス�ス�ス�ス�コ髣包スオ隴∵コキ�ク�キ�ス�ケ隴趣ス「�ス�ス�ス�ウ鬩幢ス「�ス�ァ�ス�ス�ス�ュ鬩幢ス「隴趣ス「�ス�ス�ス�ウ鬩幢ス「�ス�ァ�ス�ス�ス�ー

鬮ォ�エ陝キ�「�ス�ス�ス�ャ鬮ォ�エ鬲�シ夲スス�ス�ス�・鬮ォ�エ陝カ�キ�ス�」�ス�ッ髣厄スォ�ス�」

注目のテーマ

4AI by @IT - AIを作り、動かし、守り、生かす
Microsoft & Windows最前線2025
AI for エンジニアリング
ローコード/ノーコード セントラル by @IT - ITエンジニアがビジネスの中心で活躍する組織へ
Cloud Native Central by @IT - スケーラブルな能力を組織に
システム開発ノウハウ 【発注ナビ】PR
あなたにおすすめの記事PR

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。