SQL zur Abfrage von Daten verwenden

Die Microsoft Dataverse Geschäftsebene stellt einen TDS-Endpunkt (Tabular Data Stream) bereit, der eine SQL-Datenverbindung emuliert. Die SQL-Verbindung bietet einen schreibgeschützten Zugriff auf die Tabellendaten der Ziel Dataverse-Umgebung und lässt die Ausführung von SQL-Abfragen gegen die Dataverse-Datentabellen zu. Es werden keine benutzerdefinierten Ansichten der Daten zur Verfügung gestellt. Die SQL-Verbindung des Dataverse-Endpunkts verwendet das Dataverse-Sicherheitsmodell für den Datenzugriff. Es können Daten für alle Dataverse-Tabellen abgerufen werden, auf die ein Benutzer Zugriff hat.

Anforderungen

Die Einstellung TDS-Endpunkt aktivieren muss in Ihrer Umgebung aktiviert sein. Standardmäßig ist diese Einstellung aktiviert. Weitere Informationen: Einstellungen der Funktionen verwalten

Um die Datenexfiltration zu verhindern, aktivieren Sie die Zugriffssteuerung auf Benutzerebene für TDS-Endpunkt. Weisen Sie eine Sicherheitsrolle mit dem Prinzip des geringsten Privilegs und Datenzugriffsrechten ausschließlich den Tabellen zu, auf die die Benutzer zugreifen müssen, und gewähren Sie die Berechtigung „Zugriff auf TDS-Endpunkt erlauben“.

Unterstützung von Anwendungen

Die Unterstützung von TDS-Endpunktanwendungen für Power BI und SQL Server Management Studio wird im Folgenden beschrieben.

SQL Server Management Studio

Sie können auch SQL Server Management Studio (SSMS) Version 18.12.1 oder höher mit der SQL-Verbindung des Dataverse-Endpunkts verwenden. Beispiele für die Verwendung von SSMS mit der SQL-Datenverbindung sind in der Abbildung aufgeführt.

Sicherheit und Authentifizierung

Nur Microsoft Entra ID Authentifizierung wird unterstützt. SQL-Authentifizierung und Windows authentication werden nicht unterstützt. Die nächste Abbildung zeigt ein Beispiel für die Anmeldung bei der SQL-Verbindung in SSMS. Beachten Sie, dass der Servername die URL der Organisationsadresse ist.

Informationen zur Endpunkt-Verschlüsselung: Datenschutz bei der Übertragung

Beispiel für die Abfrage von Tabellendaten

Hier finden Sie ein paar Beispielabfragen, die in SSMS verfasst wurden. Das erste Bild zeigt eine einfache Abfrage mit Aliasen und Ergebnisreihenfolge.

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

Diese nächste Abfrage zeigt einen JOIN an.

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

Power BI (Allgemeine Verfügbarkeit)

Sie können die Option Analyze in Power BI (Data>Tables>Analyze in Power BI) in Power Apps (https://make.powerapps.com) verwenden, um mithilfe des Dataverse Connectors Daten in Power BI Desktop zu analysieren. Weitere Informationen: Tabellendaten in Power BI Desktop anzeigen

Unterstützte Operationen und Datentypen

Jede Operation, die versucht, Daten zu ändern (d.h. INSERT, UPDATE), funktioniert mit dieser schreibgeschützten SQL-Datenverbindung nicht. Eine detaillierte Liste der unterstützten SQL-Vorgänge auf dem Dataverse-Endpunkt finden Sie unter How Dataverse SQL differs from Transact-SQL.

Folgende Dataverse Dateitypen werden von der SQL-Verbindung nicht unterstützt: binary, image, sql_variant, varbinary, virtual, HierarchyId, managedproperty, file, xml, partylist, timestamp, choices. Außerdem werden die Tabellentypen „virtuell“ und „Audit“ derzeit nicht unterstützt.

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

Nachschlagen von Spaltentyp-Verhaltensweisen

Dataverse-Lookupspalten werden als <lookup>id und <lookup>name in einem Ergebnissatz dargestellt.

Verhalten von Auswahlspaltentypen

Dataverse-Auswahlspalten werden als <choice>Name und <choice>Label in einem Abfrageergebnis dargestellt.

Gemeldete SQL-Version

Der Dataverse TDS-Endpunkt simuliert die schreibgeschützten Abfragefunktionen von Microsoft SQL Server über die Dataverse-Geschäftslogik. Dataverse gibt die aktuelle SQL-Azure Version 12.0.2000.8 für select @@version zurück.

Leistungsleitfaden

Wenn Sie Daten über den TDS-Endpunkt abrufen, sollten einige wichtige Abfragemuster verwendet werden. Diese in den nächsten Abschnitten beschriebenen Abfragemuster verwalten die Leistung und Größe der Ergebnismengen.

Nur notwendige Spalten

Geben Sie beim Erstellen einer Abfrage nur die notwendigen Spalten zurück. Diese Technik unterstützt sowohl die Ausführung der Abfrage als auch die Rückübertragung der Ergebnisse an die Client-Anwendung. Im Allgemeinen wird empfohlen, weniger als 100 Spalten abzufragen.

Auswahlspalten

Auswahlspalten werden zur Vereinfachung der Benutzerfreundlichkeit auf zwei Spalten reduziert. Es ist jedoch wichtig, Aggregate und Filter auf den Wert in der Auswahlspalte anzuwenden. Der Werteteil kann Indizes haben und wird in der Basistabelle gespeichert. Der Bezeichnungsteil („choicecolumn“-Name) wird jedoch separat gespeichert, was beim Abrufen mehr kostet und nicht indiziert werden kann. Die Verwendung einer erheblichen Anzahl von Spalten mit Auswahlbezeichnungen kann zu einer langsamer ausgeführten Abfrage führen.

Top X verwenden

Es ist wichtig, in Ihren Abfragen eine Top-Klausel zu verwenden, um zu verhindern, dass versucht wird, die gesamte Datentabelle zurückzugeben. Verwenden Sie zum Beispiel Select Top 1000 accountid,name From account Where revenue > 50000, um die Ergebnisse auf die ersten 1.000 Konten zu beschränken.

NOLOCK nicht verwenden

Verwenden Sie beim Erstellen von Abfragen nicht den Tabellenhinweis NOLOCK. Dieser Hinweis verhindert, dass Dataverse Abfragen optimiert.

Einschränkungen

Der Dataverse-TDS-Endpunkt hat keine feste maximale Größenbeschränkung mehr. Stattdessen gibt es eine feste Zeitüberschreitung von fünf (5) Minuten. Mit der Einführung des Daten-Streamings können Sie so viele Daten abrufen, wie innerhalb eines Timeouts von fünf (5) Minuten möglich ist. Erwägen Sie die Verwendung von Datenintegrationstools wie Azure Synapse Link für Dataverse und dataflows für umfangreiche Datenabfragen, die mehr als fünf (5) Minuten erfordern. Weitere Informationen: Importieren und Exportieren von Daten

Die in den Abfrageergebnissen zurückgegebenen Daten werden in Universal Time Coordinated (UTC) formatiert. Zuvor wurden die Daten in lokaler Zeit zurückgegeben.

Das Abfragen von Daten mit SQL löst keine registrierten Plug-Ins in RetrieveMultipleRequest- oder RetrieveRequest-Mitteilungen aus. Jegliches Umschreiben der Abfrage oder der Ergebnisse, das normalerweise von einem solchen Plug-in durchgeführt wird, ist bei einer SQL-Abfrage nicht wirksam.

Abfragen, die den TDS-Endpunkt verwenden, werden unter den API-Grenzen des Dienstschutzes ausgeführt.

Der TDS Endpunkt kann nicht mit elastischen Tabellen verwendet werden. Mehr Informationen: Elastische Tabellen

Fehlerbehebung bei Verbindungsproblemen

Sehen wir uns einige bekannte Fehlerbedingungen und wie Sie diese beheben können.

Authentifizierung

Nur Microsoft Entra ID Authentifizierung wird für die SQL-Verbindung des Dataverse-Endpunkts unterstützt. Der bevorzugte Authentifizierungsmechanismus ist "Microsoft Entra ID – Universell" mit mehrstufiger Authentifizierung (MFA). "Microsoft Entra ID – Kennwort" funktioniert jedoch, wenn MFA nicht konfiguriert ist. Wenn Sie versuchen, andere Formen der Authentifizierung zu verwenden, könnten Sie Fehler wie den folgenden sehen.

• Fehler zurückgegeben bei Verwendung der Authentifizierung mit Microsoft Entra ID – Integrated.

Anmeldung fehlgeschlagen: Die HTTP-Anforderung wurde mit dem Client-Authentifizierungsschema 'anonym' untersagt. RequestId: TDS; 81d8a4f7-0d49-4d21-8f50-04364bdddd370; 2 Uhrzeit: 2020-12-17T01:10:59.8628578Z (.Net SqlClient Data Provider)"

• Fehler, der bei Verwendung der SQL Server Authentifizierung zurückgegeben wird.

Anmeldung fehlgeschlagen: Anforderung ist nicht authentifiziert. RequestId: TDS; 918aa372-ccc4-438a-813e-91b086355343; 1 Uhrzeit: 2020-12-17T01:13:14.4986739Z (.Net SqlClient Data Provider)"

• Fehler, der bei der Verwendung der Windows-Authentifizierung zurückgegeben wird.

„Anmeldung fehlgeschlagen: Request ist nicht authentifiziert. RequestId: TDS;fda17c60-93f7-4d5a-ad79-7ddfbb917979;1 Uhrzeit: 2020-12-17T01:15:01.0497703Z (.Net SqlClient Data Provider)

Blockierte Ports

Ein blockierter Port-Fehler kann etwa wie folgt aussehen.

Die Lösung besteht darin, zu überprüfen, ob die TCP-Ports 1433 oder 5558 vom Client nicht blockiert sind. Verwenden Sie dazu eine der folgenden Methoden, um die Sperre für die Ports wie im Folgenden beschrieben aufzuheben.

PowerShell verwenden, um die Verbindung mit dem TDS-Endpunkt zu überprüfen

1. Öffnen Sie ein PowerShell-Befehlsfenster.
2. Führen Sie den Test-connection-Befehl aus.
   Test-NetConnection -ComputerName <environment>.crm.dynamics.com -port 1433

Bei erfolgreicher Verbindung wird eine Zeile „TcpTestSucceeded : True“ zurückgegeben.

In einigen Fällen kann der Datenverkehr direkt auf IP-Ebene blockiert werden. Um zu überprüfen, ob die IP-Adresse ebenfalls funktioniert, nehmen Sie die IP-Adresse, die von der obigen Domänentestverbindung zurückgegeben wird, und ersetzen Sie den Wert des ComputerName-Parameters durch die IP-Adresse.

1. Verwenden Sie die vom obigen Befehl zurückgegebene Adresse als „RemoteAddress“
2. Befehl „Test-NetConnection -ComputerName <RemoteAddress> -port 1433“ ausführen

Dieser Befehl sollte „TcpTestSucceeded: True“ zurückgeben.

Eine Telnet-Sitzung zum TDS-Endpunkt herstellen

1. Installieren/Aktivieren von Telnet auf einem Microsoft Windows-Computer.
   1. Wählen Sie Start aus.
   2. Wählen Sie Control Panel aus.
   3. Wählen Sie Programme und Funktionen.
   4. Wählen Sie Windows-Features aktivieren oder deaktivieren aus.
   5. Wählen Sie die Option Telnet Client.
   6. Wählen Sie OK aus. Es erscheint ein Dialogfeld zur Bestätigung der Installation. Der Telnet-Befehl sollte nun verfügbar sein.
2. Führen Sie einen Telnet-Befehl in einem Befehlsfenster aus.
   telnet <environmentname>.crm.dynamics.com 1433

Wenn die Verbindung erfolgreich ist, werden Sie in einer aktiven Telnet-Sitzung platziert. Wenn sie nicht erfolgreich ist, erhalten Sie die Fehlermeldung:

„Verbindung zu <Umgebungsname>.crm.dynamics.com… Konnte keine Verbindung zum Host öffnen, auf Port 1433: Verbindung fehlgeschlagen.“

Diese Fehlermeldung bedeutet, dass der Port am Client blockiert wurde.

Portumleitung von nicht-SSL zu SSL

Die TDS-Verbindung kann bei Verwendung von Anwendungen von Drittanbietern aufgrund einer Portumleitung von 1433/5558 auf 443 fehlschlagen. Dieser Fehler tritt auf, weil die SSL-Überprüfungsregel die Kommunikation blockieren kann. Der Grund für die Blockierung ist eine „Umleitung vom Nicht-SSL-Port zum SSL-Port“. Die Lösung besteht darin, die Dataverse-TDS-Kommunikation auf Web-Proxys unter Verwendung von IP-Adressen zu erlaublisten.

Informationen zu den offiziellen IP-Adresswerten für den Zugriff auf den Dienst finden Sie unter IP-Adressen erforderlich.

Das Setzen von Hostnamen auf die Positivliste reicht nicht aus bei der Verbindung mit Dataverse-TDS, da die Portumleitung zwischen den Ports 1433/5558 und 433 über die IP-Adresse und nicht über den Hostnamen erfolgt.

Siehe auch

Wie sich Dataverse SQL von Transact-SQL unterscheidetEinführung in virtuelle Tabellen (Entitäten)
Daten mit FetchXml abfragenService Protection API-Grenzwerte