SQL zur Abfrage von Daten verwenden

Die Microsoft Dataverse-Unternehmensebene stellt einen Endpunkt für den tabellarischen Datenstrom (TDS) 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 Dataverse Endpunkt 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 nicht 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 den geringsten Rechten mit Datenzugriffsberechtigung nur den Tabellen zu, auf die Ihre Benutzer zugreifen müssen, und erteilen Sie dem Benutzer die Berechtigung "Zugriff auf TDS-Endpunkt erlauben".

Unterstützung von Anwendungen

Die Unterstützung von TDS (SQL)-Endpunktanwendungen für Power BI und SQL Server Management Studio wird als Nächstes beschrieben.

SQL Server Management Studio (Vorschau)

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

Sicherheit und Authentifizierung

Es wird nur die Microsoft Entra ID-Authentifizierung unterstützt. SQL-Authentifizierung und Windows-Authentifizierung 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 In Power BI analysieren (Daten>Tabellen>In Power BI analysieren) in Power Apps verwenden (https://make.powerapps.com), um den Dataverse-Konnektor zu verwenden, um die Daten in Power BI Desktop zu analysieren. Weitere Informationen: Tabellendaten anzeigen in Power BI Desktop

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-Operationen auf dem Dataverse-Endpunkt finden Sie unter Wie sich Dataverse SQL von Transact-SQL unterscheidet.

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-Suchspalten werden als <lookup>id und <lookup>name in einer Ergebnismenge dargestellt.

Verhalten von Auswahlspaltentypen

Dataverse-Auswahlspalten werden als <choice>Name und <choice>Label in einer Ergebnismenge dargestellt.

SQL-Berichtsversion

Der Dataverse TDS-Endpunkt emuliert Nur-Lese-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, alle Aggregate und Filter für den Wertteil der Auswahlspalte durchzuführen. 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 großen Anzahl von Auswahlbeschriftungsspalten kann zu einer langsameren 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 die Select Top 1000 accountid,name From account Where revenue > 50000 Grenzwerte die Ergebnisse auf die ersten 1.000 Konten.

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 des festgelegten Timeouts von fünf (5) Minuten abgeschlossen werden können. Ziehen Sie die Verwendung von Datenintegrationstools wie Azure Synapse Link for Dataverse und Dataflows für große Datenabfragen in Betracht, die mehr als fünf (5) Minuten für den Abschluss brauchen. 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 die Microsoft Entra ID-Authentifizierung wird auf der Dataverse-Endpunkt-SQL-Verbindung unterstützt. Der bevorzugte Authentifizierungsmechanismus ist „Microsoft Entra ID – Universal“ mit Multi-Faktor-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 bei Verwendung der Authentifizierung Microsoft Entra ID – Integriert.

„Anmeldung fehlgeschlagen: Der HTTP-Anfrage wurde mit dem Client-Authentifizierungsschema 'Anonymous' untersagt. RequestId: TDS;81d8a4f7-0d49-4d21-8f50-04364bddd370;2 Time: 2020-12-17T01:10:59.8628578Z (.Net SqlClient Data Provider)“

• Fehler bei Verwendung der SQL Server-Authentifizierung.

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

• Fehler bei Verwendung der Windows-Authentifizierung.

„Anmeldung fehlgeschlagen: Request ist nicht authentifiziert. RequestId: TDS;fda17c60-93f7-4d5a-ad79-7ddfbb917979;1 Time: 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 TDS zu überprüfen Endpunkt

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.

3. Nehmen Sie die vom obenstehenden Befehl zurückgegebene Adresse als „RemoteAddress“
4. 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 Sie auf einem Microsoft Windows-Computer Telnet.
   1. Wählen Sie Start aus.
   2. Wählen Sie Systemsteuerung.
   3. Wählen Sie Programme und Funktionen.
   4. Wählen Sie Windows Funktionen ein- oder ausschalten.
   5. Wählen Sie die Option Telnet Client.
   6. Klicken Sie auf OK. 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, eine Dataverse-TDS-Kommunikation auf Web-Proxys unter Verwendung von IP-Adressen auf die Positivliste zu setzen.

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 unterscheidetErste Schritte mit virtuellen Tabellen (Entitäten)
Daten mit FetchXml abfragenService Protection API-Grenzwerte