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

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

# 検証の目的

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

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

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

:::note info
今回はAzure ADユーザーによる行レベルセキュリティを実現するために 「仮想ネットワークデータゲートウェイ」というプレビュー機能を利用します。
::: 
:::note info
PowerBI Premium Per User ライセンスが必要です。検証では60日間の試用版を利用しています。
:::
:::note info
本投稿の内容は、2022年1月時点の情報です。
:::


# 全体の流れ

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

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

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

https://ascii.jp/elem/000/004/014/4014967/

https://qiita.com/masahiro-yamaguchi/items/ac4a0793b543ddcc2061

#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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/1ceb38ad-9ace-d2c4-a9e4-35b4f24330cb.png)

## Azure ADにグループを追加する。
AADにグループを追加して、データベースにアクセス許可したい検証用ユーザを追加します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/02396d5a-618f-884a-b5b0-d03d55bd7a90.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/ae0246a1-495c-fb45-dcbe-8d92b3738c76.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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/1360b656-c6d2-4de2-0629-9a14ab208915.png)

```SQL
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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/1d69bfa1-1993-1ca7-bfe5-95394800f2a0.png)

```SQL
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テーブルに対しセレクト権限を付与します。

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

## セキュリティポリシーを設定
```SQL:スキーマを作成し、セキュリティーポリシーで利用する関数を作成
-- 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';
```
:::note info
USER_NAME関数は、AzureActiveDirectory認証の場合、基本的にはAzureADのメールアドレスが返ってきます。ただし、sysadmin 固定サーバー ロールのメンバーの場合 USER_NAME関数は dbo という文字列を返すので、別途許可しています。
:::


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

```SQL:ポリシーを作成
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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/b83d7ecc-79d2-5c73-8279-a23855c3f0c1.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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/7314549e-7b08-06a5-30f3-e3be125a7e4e.png)

手順 2: サブネットを Microsoft Power Platform に関連付けます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/c8fee26b-0b24-5f8e-7a77-1bccf92421c9.png)

手順 3: VNet データ ゲートウェイを作成します。
その前に準備としてPPUのあるユーザーに「ネットワーク共同作成者」のロールを割り当てます。
以下の操作はAzurePortalでサブスクリプションに対して行います。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/fa74bcf8-0036-0c7e-b82a-4e67811b634a.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/8c0d95f8-9ae8-caf5-3917-525742ec725f.png)

[Power Platform 管理センター](https://admin.powerplatform.microsoft.com/ext/DataGateways) にアクセスして、仮想ネットワーク データゲートウェイを新規作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/3a074b7f-2e21-08ef-70ec-c5794ec22977.png)

Power Platform 管理センターに戻って 仮想ネットワークデータゲートウェイを新規作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/f62ff7b5-ff20-6bca-5ffd-867916964ea5.png)

仮想ネットワークデータゲートウェイが作成できました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/d0febcdf-e5b4-d5e7-f71a-ff0e03187b11.png)

PowerBIサービス側から確認します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/35b846f5-36a4-80b1-b9fa-a812a1102fc3.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/42426c9f-7e3b-f13a-378c-51194e1bb96d.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/962a847c-72d7-8e92-77d0-1898bced1e85.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/0a36399e-b844-fe15-bc88-9e9ef03ade15.png)
テーブルを挿入してUsersテーブルのIDとUserNameを表示します。レポートができたら、Premium ワークスペースに発行します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/93892a54-731e-3fde-77e7-e83b1c164583.png)

# 5. VNETデータゲートウェイにデータソースの追加
発行が完了するとPowerBIサービスでデータセットの設定を行います。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/058cfca5-3462-c084-d75a-c879fd436a06.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/522112aa-62cb-b732-f948-b6bfc24a8732.png)

データゲートウェイ接続を展開し、アクションの横▼をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/8f8cc972-aa90-e809-84fc-1cc151f8330f.png)

[VNETに追加]をクリックして、VNETにデータソース「SQL」を追加します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/5855028f-bbdd-9405-67a5-876faf3a8021.png)

データソースの情報を入力します。その際「DirectQuery クエリに Azure AD 経由で SSO を使用する」にチェックを入れることでAzure ADのユーザーとしてSQL Databaseに接続するようになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/ae1e86ba-feab-4c54-2732-b3969e98ac5b.png)

作成したデータソース「SQL」をこのデータセットに含まれている データ ソースのマップ先にセットして、「適用」します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/9802702e-e692-d915-2ca7-d9f53738e8ba.png)

適用後、資格情報のエラーがなくなります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/34938b00-a0e1-4541-0912-8759e1970822.png)

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

最後に発行したレポートを確認すると、ログインしているアカウントのレコードだけが表示されていることを確認できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1760066/a8a5e591-c347-41ef-7ef8-86bf9cd5aed9.png)

# 最後に

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