SQL を使用してデータを照会

[このトピックはプレリリース ドキュメントであり、変更されることがあります。 SQL データ接続のみがプレビューされていることに注意してください。 Power BI は一般提供 (GA) です]

SQL データ接続は、Microsoft Dataverse エンドポイントで使用できます。 SQL 接続では、ターゲット Dataverse 環境のテーブル データへの読み取り専用アクセスが提供されるため、Dataverse データ テーブルに対して SQL クエリを実行できます。 データのカスタムビューは提供されていません。 Dataverse エンドポイントの SQL 接続は、データアクセスに Dataverse セキュリティ モデルを使用しています。 ユーザーがアクセスできるすべての Dataverse テーブルのデータを取得できます。

アプリケーション サポート

Power BIと SQL Server ManagementStudio の TDS (SQL) エンドポイント アプリケーションのサポートについては以下で説明します。

SQL Server Management Studio (プレビュー)

注意

SQL Server Management Studio18.9.2 のビルドで互換性の問題が見つかりました。 修正については調査中です。 修正が利用可能になるまで、ビルド SQL Server ManagementStudioの18.9.1 を使用してください。 このメモは、修正が利用可能になり次第更新されます。

また、SQL Server Management Studio (SSMS) バージョン 18.4 以降を Dataverse エンドポイント SQL 接続で使用することもできます。 SQL データ接続で SSMS を使用する例を以下に示します。

拡張アカウント テーブル。

セキュリティおよび認証

Azure Active Directory 認証のみサポートしています。 SQL 認証と Windows 認証はサポートされていません。 以下は、SSMS で SQL 接続にログオンする方法の例です。 サーバー名が組織のアドレス URL であることに注意してください。

接続ダイアログ。

注意

SSMS などのクライアント アプリケーションから TDS エンドポイントを使用するには、ポート 1433 または 5558、あるいはその両方を有効にする必要があります。 ポート 5558 のみを有効にする場合、ユーザーは サーバーに接続する SSMSのダイアログ - 例: myorgname.crm.dynamics.com; 5558 でそのポート番号をサーバー名に追加する必要があります。

テーブル データ クエリの例

以下は、SSMS で構成されるクエリのいくつかの例です。 最初の画像は、エイリアスと結果の順序を使用した簡単なクエリを示しています。

select top 5 a.name as [VIP customer], a.address1_postalcode as [ZIP code] from account a order by a.address1_postalcode desc

エイリアスと順序を使用した簡単なクエリ。

次のクエリは、JOIN を示しています。

select name, fullname from account a inner join contact c on a.primarycontactid = c.contactid

JOIN を使用した別のクエリ。

Power BI

Power Apps (https://make.powerapps.com) の Power BI で分析 オプション (データ > テーブル > Power BI で分析) を使用すると、Dataverse コネクタを使用して Power BI Desktop でデータを分析できます。 詳細: Power BI Desktop でのテーブル データの表示

注意

この機能を有効にするには、機能設定を管理する の TDS エンドポイント設定を参照してください。 有効にすると、Power Apps のコマンドバーで Power BI で分析 ボタンが表示されます。

サポートされている操作とデータ型

データを変更しようとする操作 (つまり、INSERT、UPDATE) は、この読み取り専用の SQL データ接続では機能しません。 Dataverse エンドポイントでサポートされている SQL 操作の詳細なリストについては、Dataverse SQL がいかに Transact-SQL と異なる点 を参照してください。

以下の Dataverse データ型は SQL 接続では対応していません : binaryimagesql_variantvarbinaryvirtualHierarchyIdmanagedpropertyfilexmlpartylisttimestampchoices

ヒント

partylist 属性は、代わりに以下のように activityparty テーブルに結合して問い合わせを行うことができます。

select act.activityid, act.subject, string_agg([to].partyidname, ', ')
from activitypointer as act
left outer join activityparty as [to] on act.activityid = [to].activityid and [to].participationtypemask = 2
group by act.activityid, act.subject

検索列タイプの動作

Dataverse 検索列は、結果セットの <lookup> ID と <lookup> 名で表されます。

選択列タイプの動作

Dataverse 選択列は、結果セットの <choice> 名と <choice> ラベルで表されます。

ヒント

選択列のラベルを変更した後、テーブルのカスタマイズを公開する必要があります。

制限

Dataverse エンドポイントから返されるクエリ結果には、80 MB の最大サイズ制限があります。 80 MB を超えるデータを返す大規模なデータ クエリの場合、Azure Synapse Link for Dataverseデータフロー などのデータ統合ツールの使用を検討してください。 詳細情報: データのインポートとエクスポート

ヒント

返却されるデータのサイズを許容範囲内に収めるため、複数行のテキスト列や選択肢の列は可能な限り少なくしてください。

クエリ結果で返される日付は、協定世界時 (UTC) としてフォーマットされます。 以前は、日付は現地時間で返されていました。

SQL を使ってデータを取得しても、RetrieveMultipleRequestRetrieveRequest のメッセージに登録されているプラグインがトリガーされません。 そのため、このようなプラグインによって通常実行されるクエリや結果の書き換えは、SQL クエリでは有効になりません。

TDS エンドポイントの実行を使用したクエリは、サービス保護 API 制限の下で実行されます。

接続に関する問題のトラブルシューティング

以下は、いくつかの既知のエラー状態とそれらを解決する方法です。

注意

SSMS などのクライアント アプリケーションから TDS エンドポイントを使用するには、ポート 1433 または 5558、あるいはその両方を有効にする必要があります。 ポート 5558 のみを有効にする場合、ユーザーは サーバーに接続する SSMSのダイアログ - 例: myorgname.crm.dynamics.com; 5558 でそのポート番号をサーバー名に追加する必要があります。

認証

Azure Active Directory 認証のみが Dataverse エンドポイント SQL 接続でサポートされています。 推奨される認証メカニズムは、"Azure Active Directory – 多要素認証 (MFA) を備えた "ユニバーサル" です。 しかしながら、"Azure Active Directory – パスワード" は、MFA が構成されていない場合に機能します。 他の形式の認証を使用しようとすると、次のようなエラーが表示されます。

  • Azure Active Directory – 統合型 認証使用時にエラーが返される。

"ログインに失敗しました: HTTP 要求はクライアント認証スキーム '匿名' で禁止されました。 RequestId: TDS;81d8a4f7-0d49-4d21-8f50-04364bddd370;2 Time: 2020-12-17T01:10:59.8628578Z (.Net SqlClient データ プロバイダー)”

  • SQL サーバー 認証の使用時にエラーが返される。

“ログイン エラー: この要求は認証されていません。 RequestId: TDS;918aa372-ccc4-438a-813e-91b086355343;1 Time: 2020-12-17T01:13:14.4986739Z (.Net SqlClient データ プロバイダー)”

  • Windows 認証使用時にエラーが返される。

“ログイン エラー: この要求は認証されていません。 RequestId: TDS;fda17c60-93f7-4d5a-ad79-7ddfbb917979;1 Time: 2020-12-17T01:15:01.0497703Z (.Net SqlClient データ プロバイダー)”

ブロックされたポート

ブロックされたポート エラーは、次のようになります。

エラー メッセージ。

解決策は、クライアントからの TCP ポート 1433 または 5558 のブロックが解除されていることを確認することです。 これを行うには、以下のいずれかの方法を使用します。

PowerShell を使用して TDS エンドポイントとの接続を検証します

  1. PowerShell コマンド ウィンドウを開きます。
  2. 接続テストのコマンドを実行します。
    Test-NetConnection -ComputerName <environment>.crm.dynamics.com -port 1433

接続が成功すると、「TcpTestSucceeded : True」という行が返されます。

TDS エンドポイントへの Telnet セッションを確立します

  1. Microsoft Windows コンピュータで、telnet をインストール/有効にします。
    1. 開始 を選択します。
    2. コントロール パネル を選択します。
    3. プログラムと機能 を選択します。
    4. Windows の機能の有効化または無効化 を選択します。
    5. Telnet クライアント オプションを選択します。
    6. OK を選択します。 インストールを確認するダイアログ ボックスが表示されます。 telnet コマンドが利用可能になりました。
  2. [コマンド] ウィンドウで telnet コマンドを実行します。
    telnet <environmentname>.crm.dynamics.com 1433

接続に成功すると、アクティブな telnet セッションになります。 失敗した場合は、次のエラーが表示されます。

<environmentname>.crm.dynamics.com への接続が… ポート 1433 でホストへの接続を開くことができませんでした: 接続に失敗しました」。

これは、ポートがクライアントでブロックされていることを意味します。

関連項目

FetchXML の使用によるクエリの作成
サービス保護の API 制限