次の方法で共有

SQL を使用したデータのクエリ

  • [アーティクル]

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

ヒント

SQL Server Management Studio と .NET ライブラリ経由の SQL データ接続のみがプレビュー段階にあります。 Power BI は、一般公開されています。

前提条件

環境 で TDSエンドポイント を有効にする 設定を有効にする必要があります。 既定ではこの設定は有効です。 詳細情報: 機能設定の管理

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

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

SQL Server Management Studio (プレビュー)

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

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

セキュリティおよび認証

Microsoft Entra ID 認証のみサポートしています。 SQL 認証と Windows 認証はサポートされていません。 次の図は、SSMS で SQL 接続にサインインする方法の例を示しています。 サーバー名が組織のアドレス URL であることに注意してください。

接続ダイアログ。

ヒント

SSMS などのクライアント アプリケーションから TDS エンドポイントを使用するには、ポート 1433 または 5558、あるいはその両方を有効にする必要があります。 ポート5558のみを有効にする場合、ユーザーはSSMSの 接続to Server ダイアログでそのポート番号をサーバー名に追加する必要があります (例: 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 BI ) の 分析場所>オプション (>データ Power BIテーブル Power Apps 分析場所https://make.powerapps.com) を使用すると、 Dataverse コネクタを使用して Power BI Desktop のデータを分析できます。 詳細情報: 表データを表示 Power BI Desktop

ヒント

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

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

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

次の Dataverse データ型は、SQL接続ではサポートされていません: binary、 image、 sql_variant、 varbinary、 virtual、 HierarchyId、 managedproperty、 file、 xml、 partylist、 timestamp、 choices。 また、現時点では、テーブル タイプ「virtual」および「audit」はサポートされていません。

チップ

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>nameとして表されます。

選択列タイプの動作

Dataverse 選択列は、結果セットでは <choice>Nameおよび <choice>Labelとして表されます。

チップ

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

ヒント

クエリに多数の選択ラベルを含めると、パフォーマンスに大きな影響を与えます。 可能であれば、10 未満のラベルを使用することをお勧めします。 選択ラベルはローカライズされているため、ローカライズされた文字列を返すとコストが高くなります。

報告済みの SQL バージョン

Dataverse TDS エンドポイントは、Dataverse ビジネス ロジックを介して Microsoft SQL Server 読み取り専用クエリ機能をエミュレートします。 Dataverse select @@versionの現在のSQL Azureバージョン12.0.2000.8を返します。

操作ガイド

TDS エンドポイント を通じてデータを取得する場合、使用する必要がある主要なクエリ パターンがいくつかあります。 次のセクションで説明します。これらのクエリ パターンは、結果セットのパフォーマンスとサイズを管理します。

必要な列のみ

クエリを作成するときは、必要な列のみを返します。 この手法は、クエリの実行と、結果をクライアント アプリケーションに戻すことの両方に役立ちます。 一般に、クエリを 100 列未満に保つことをお勧めします。

列の選択

選択列が 2 つの列にフラット化され、使いやすくなりました。 ただし、選択列の値部分に対して集計とフィルターを実行することが重要です。 値の部分はインデックスを持つことができ、ベース テーブルに格納されます。 ただし、ラベル部分 ('choicecolumn' 名) は個別に保存されるため、取得にコストがかかり、インデックスを作成できません。 多数の選択ラベル列を使用すると、クエリの実行速度が低下する可能性があります。

上位 X を使用

データのテーブル全体を返そうとしないように、クエリで top 句を使用することが重要です。 たとえば、use Select Top 1000 accountid,name From account Where revenue > 50000 を使用すると、結果が最初の1,000件のアカウントに制限されます。

NOLOCK を使用しないでください

クエリを作成する際は、テーブル ヒント NOLOCK を使用しないでください。 このヒントにより、Dataverse によるクエリの最適化が妨げられます。

制限

Dataverse TDS エンドポイント には、厳密な最大サイズ制限がなくなりました。 代わりに、5 分間の固定タイムアウトがあります。 データ ストリーミングの導入により、固定の 5 分間のタイムアウト内で完了できる限り多くのデータを取得できます。 完了までに5分以上かかる大規模なデータ クエリの場合は、 Azure Synapse Link for Dataverse や データフロー などのデータ統合ツールの使用を検討してください。 詳細情報: データのインポートとエクスポート

チップ

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

警告

クエリの複雑さに応じて、5分間のタイムアウトを2分間に調整できます。 たとえば、 SELECT *、 NESTED FROMs and/or JOINs を含むクエリは、長時間実行されたままにしておくとサーバーに過度の負荷をかけるため、タイムアウト制限が自動的に2分に設定されます。 パフォーマンスを最大限に高めるには、SQLでこれらのパターンを使用しないことをお勧めします。

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

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

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

TDS エンドポイント はエラスティック テーブルでは使用できません。 詳細情報: 弾性テーブル

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

いくつかの既知のエラー状態とその解決方法を見てみましょう。

ヒント

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

認証

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

  • ID - 統合認証の使用時にエラーが返されました。 Microsoft Entra

「ログインに失敗しました: 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」という行が返されます。

場合によっては、トラフィックを IP レベルで直接ブロックできます。 IP アドレスも機能していることを検証するには、上記のドメイン テスト接続から返された IP アドレスを取得し、ComputerName パラメーター値を IP アドレスに置き換えます。

  1. 上記のコマンドから返されたアドレスを "RemoteAddress" とします
  2. Test-NetConnection -ComputerName <RemoteAddress> -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 セッションになります。 失敗した場合は、次のエラーが表示されます:

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

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

非 SSL から SSL へのポート リダイレクト

サードパーティ アプリケーションを使用すると、ポートが 1433/5558 から 443 にリダイレクトされるため、TDS 接続が失敗する可能性があります。 この失敗は、SSL 検査ルールによって通信がブロックされる可能性があるために発生します。ブロックの理由は、「非 SSL ポートから SSL ポートへのリダイレクト」です。 解決策は、IP アドレスを使用して Web プロキシ上の Dataverse TDS 通信を許可リストに登録することです。

サービスにアクセスするための公式IPアドレス値については、 IP-Addresses-Required を参照してください。

Dataverse TDS に接続する場合、ホスト名を許可リストに登録するだけでは不十分です。ポート 1433/5558から433 へのポート リダイレクトは、ホスト名ではなく IP アドレスを介して行われるためです。

参照

Dataverse SQLとTransact-SQLの違い仮想テーブル (エンティティ) の使用を開始する
FetchXmlを使用してデータをクエリするサービス保護APIの制限