LoginSignup
1
0

More than 1 year has passed since last update.

@atl-nabeshima(鍋島)in株式会社エーティーエルシステムズ

PowerBIサービスとVNETデータゲートウェイでAADによる行レベルセキュリティをSQL側で設定する方法

Last updated at Posted at 2022-02-16

こんにちは、株式会社 エーティーエルシステムズ 鍋島です。
今回はの投稿はPowerBI サービスとサーバーサイドの行レベルセキュリティを検証した記録です。

検証の目的

目的は次の３つの要件を満たすPowerBI レポートの作成することです。

  1. PowerBIサービスのレポートで、Azureの仮想ネットワーク(VNET)内のSQL Databaseデータを表示したい。
  2. SQL Databaseの認証は Azure AD認証としたい。
  3. PowerBI実行ユーザーによる行レベルのセキュリティ(RLS,Row Level Security)をAzure SQL Database側でやりたい。

検証してみたら、手順が長くなりましたので、記事にまとめてみました。
何かの参考になると幸いです。

今回はAzure ADユーザーによる行レベルセキュリティを実現するために 「仮想ネットワークデータゲートウェイ」というプレビュー機能を利用します。

PowerBI Premium Per User ライセンスが必要です。検証では60日間の試用版を利用しています。

本投稿の内容は、2022年1月時点の情報です。

全体の流れ

全体の流れは次のとおりです。

  1. Azure SQL Databaseで Azure Active Directory認証を有効にして、グループで認証できるようにする。
  2. データベースを準備して、行レベルのセキュリティを構成する
  3. Power Platform 管理センターで仮想ネットワークデータゲートウェイを追加する
  4. PowerBIサービスにプレミアムワークスペースを作成、レポートを作成して発行する
  5. PowerBIサービスでVNETデータゲートウェイのデータソースを作成して、マップする
  6. レポートから最終確認。行レベルのセキュリティで、ログインユーザーでフィルターされているのを確認する

参考情報

今回の投稿は以下の2つの記事を参考にしています。とても分かりやすい投稿で感謝しています。

1. Azure SQL Databaseで Azure Active Directoryの設定

Azure Active Directoryで認証を許可する

SQL DatabaseでAzure ADによるに認証を許可するには、SQL Server リソースで Azure Active Directory認証をするように設定します。
Azureの分かりにくいところですが、SQL データベース側ではなくSQL Server側での設定になりますので、ご注意下さい。
image.png

Azure ADにグループを追加する。

AADにグループを追加して、データベースにアクセス許可したい検証用ユーザを追加します。
image.png
image.png

今回、作成したセキュリティグループ PowerBI-AAD-RLS-TEST に、検証用のユーザーとして 太郎 testtaro@foobarboo.onmicrosoft.com と 花子
testhanako@foobarboo.onmicrosoft.com を 追加してます。

2. 行レベルのセキュリティの構成

SSMSでデータベースに接続する際は、SQL ServerでAzure Active Directory 管理者 に設定したユーザーで接続します。
データベース test-db を事前に準備しています。

検証用テーブルとデータの登録

検証用のUsersテーブルを作成し、検証用のデータを突っ込みます。

image.png

CREATE TABLE [dbo].[Users](
    [Id] [int] NOT NULL,
    [UserName] [varchar](50) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

データを登録します。PowerBIで接続する予定のユーザー（ここでは、太郎と花子）を追加し、それ以外にも検証用にダミーデータを登録します。
image.png

INSERT [dbo].[Users] ([Id], [UserName]) VALUES (1, N'testtaro@foobarboo.onmicrosoft.com')
INSERT [dbo].[Users] ([Id], [UserName]) VALUES (2, N'testhanako@foobarboo.onmicrosoft.com')
INSERT [dbo].[Users] ([Id], [UserName]) VALUES (3, N'foo@example.com')
INSERT [dbo].[Users] ([Id], [UserName]) VALUES (4, N'bar@example.com')
INSERT [dbo].[Users] ([Id], [UserName]) VALUES (5, N'boo@example.com')

グループに選択権限の付与

AzureADのグループ PowerBI-AAD-RLS-TEST をデータベース test-db のユーザーとして追加し、さらにUsersテーブルに対しセレクト権限を付与します。

テーブルへのアクセス権限の許可
USE [test-db]
--AzureADのセキュリティグループをユーザーとして追加
CREATE USER [PowerBI-AAD-RLS-TEST] FROM EXTERNAL PROVIDER
-- 選択権限をセキュリティーグループに付与
GRANT SELECT ON Users TO [PowerBI-AAD-RLS-TEST];

セキュリティポリシーを設定

スキーマを作成し、セキュリティーポリシーで利用する関数を作成
-- Securityという名前のスキーマを作成
CREATE SCHEMA Security; 
-- Usersテーブル用の関数を作成
-- 
CREATE FUNCTION Security.fn_secpredicate_user(@Name AS sysname)  
RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN
    SELECT
        1 AS fn_secpredicate_user
    WHERE
        @Name = USER_NAME() OR USER_NAME() = 'dbo';

USER_NAME関数は、AzureActiveDirectory認証の場合、基本的にはAzureADのメールアドレスが返ってきます。ただし、sysadmin 固定サーバー ロールのメンバーの場合 USER_NAME関数は dbo という文字列を返すので、別途許可しています。

作成した関数を使ってポリシーを作成します。

ポリシーを作成
CREATE SECURITY POLICY Security.Users_Filter  
ADD FILTER PREDICATE Security.fn_secpredicate_user(UserName) ON dbo.Users
WITH (STATE = ON);

RLSの完成！SSMSから確認します。
Usersテーブルには4レコード追加していますが、ログインした自分自身の行しか出力されません。
image.png

3. 仮想ネットワークデータゲートウェイの追加

公式はMicrosoftのこちらのドキュメントを参照してください。
https://docs.microsoft.com/ja-jp/data-integration/vnet/use-data-gateways-sources-power-bi

Power BI Premium アカウントの準備

まず、Power BI Premium アカウントを用意します。
VNET データ ゲートウェイは Power BI プレミアム ワークスペースと Premium Per User (PPU) ライセンスが必要になります。
本記事ではPPUは60日間の試用版を使用しています。

仮想ネットワーク データ ゲートウェイはプレミアムのみの機能で、Power BI プレミアム ワークスペースと Premium Per User (PPU) でのみパブリック プレビューが可能となります。 ただし、VNet データ ゲートウェイが一般的に利用できるようになった時点で、ライセンス要件が変更される可能性があります。

VNET データゲートウェイを作成する

公式ドキュメントに沿って進めます。手順は、以下のドキュメントが良く書かれていますので、割愛します。
https://docs.microsoft.com/ja-jp/data-integration/vnet/create-data-gateways

手順１：サブスクリプションにサービスプロバイダの登録します。
image.png

手順 2: サブネットを Microsoft Power Platform に関連付けます。
image.png

手順 3: VNet データ ゲートウェイを作成します。
その前に準備としてPPUのあるユーザーに「ネットワーク共同作成者」のロールを割り当てます。
以下の操作はAzurePortalでサブスクリプションに対して行います。
image.png
image.png

Power Platform 管理センター にアクセスして、仮想ネットワーク データゲートウェイを新規作成します。
image.png

Power Platform 管理センターに戻って 仮想ネットワークデータゲートウェイを新規作成します。
image.png

仮想ネットワークデータゲートウェイが作成できました。
image.png

PowerBIサービス側から確認します。
image.png
image.png

4. PowerBIレポートを作成して発行

image.png
image.png
テーブルを挿入してUsersテーブルのIDとUserNameを表示します。レポートができたら、Premium ワークスペースに発行します。
image.png

5. VNETデータゲートウェイにデータソースの追加

発行が完了するとPowerBIサービスでデータセットの設定を行います。
image.png

データセットの設定を開いた直後は、「資格情報が正しくないため、データ ソースを更新できません。資格情報を更新して、もう一度お試しください。」と表示されています。これはPowerBIサービスからVNET内のSQLDatabaseに接続できないためです。

image.png

データゲートウェイ接続を展開し、アクションの横▼をクリックします。
image.png

[VNETに追加]をクリックして、VNETにデータソース「SQL」を追加します。
image.png

データソースの情報を入力します。その際「DirectQuery クエリに Azure AD 経由で SSO を使用する」にチェックを入れることでAzure ADのユーザーとしてSQL Databaseに接続するようになります。
image.png

作成したデータソース「SQL」をこのデータセットに含まれている データ ソースのマップ先にセットして、「適用」します。
image.png

適用後、資格情報のエラーがなくなります。
image.png

6. レポートから最終確認

最後に発行したレポートを確認すると、ログインしているアカウントのレコードだけが表示されていることを確認できます。
image.png

最後に

仮想ネットワークデータゲートウェイ という プレビュー機能と PowerBI Premium Per User という有償ライセンスを使うことで目的が達成できました。また、仮想ネットワークデータゲートウェイはオンプレミスデータゲートウェイと違い設定が楽な上に、Azure ADによるセキュリティの設定ができるようになります。価格が気になるところですが、GAされるのが楽しみですね。

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0