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.

Anmerkung

Nur die SQL-Datenverbindung über SQL Server Management Studio und .NET-Bibliotheken befindet sich in der Vorschau. Power BI ist allgemein verfügbar.

Anforderungen

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

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 Dataverse Endpunkt SQL-Verbindung 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.

Anmerkung

Die Ports 1433 und/oder 5558 müssen aktiviert werden, um den TDS-Endpunkt von einer Client-Anwendung wie SSMS aus zu verwenden. Wenn Sie nur Port 5558 aktivieren, muss der Benutzer diese Portnummer an den Servernamen im Dialogfeld Verbinden an Server von SSMS anhängen – zum Beispiel: myorgname.crm.dynamics.com,5558.

Informationen zur Endpunkt-Verschlüsselung: Datenschutz während 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 Analysieren in Power BI (Daten>Tabellen>Analysieren in Power BI) in Power Apps (https://make.powerapps.com) verwenden, um den Dataverse Connector zum Analysieren von Daten in Power BI Desktop zu verwenden. Weitere Informationen: Tabellendaten anzeigen in Power BI Desktop

Anmerkung

Informationen zum Aktivieren dieser Funktion finden Sie in der TDS-Einstellung Endpunkt unter Funktionseinstellungen verwalten. Nach der Aktivierung sollte in der Befehlsleiste von die Schaltfläche Power BI Analysieren in Power Apps angezeigt werden.

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 ausführliche Liste der unterstützten SQL-Operationen auf Dataverse Endpunkt finden Sie unter So unterscheiden sich Dataverse SQL und Transact-SQL.

Die folgenden Dataverse Datentypen werden bei 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.

Trinkgeld

partylist Attribute können stattdessen durch Verbinden mit der activityparty Tabelle abgefragt werden, wie im nächsten Beispiel gezeigt.

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 Nachschlagespalten werden in einem Ergebnissatz als <Lookup>ID und <Lookup>Name dargestellt.

Verhalten von Auswahlspaltentypen

Dataverse Auswahlspalten werden in einem Ergebnissatz als <Auswahl>Name und <Auswahl>Bezeichnung dargestellt.

Trinkgeld

Nachdem Sie Änderungen an Beschriftungen für eine Auswahlspalte vorgenommen haben, müssen die Anpassungen für die Tabelle veröffentlicht werden.

Anmerkung

Das Einbeziehen einer großen Anzahl von Auswahlbezeichnungen in Ihre Abfrage hat erhebliche Auswirkungen auf die Leistung. Verwenden Sie nach Möglichkeit weniger als zehn Beschriftungen. Da Auswahlbezeichnungen lokalisiert sind, ist die Rückgabe der lokalisierten Zeichenfolge teurer.

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. Beispielsweise beschränkt use Select Top 1000 accountid,name From account Where revenue > 50000 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. Erwägen Sie den Einsatz von Datenintegrationstools wie Azure Synapse Link for Dataverse und Dataflows für große Datenabfragen, deren Ausführung länger als fünf (5) Minuten dauert. Weitere Informationen: Daten importieren und exportieren

Trinkgeld

Um die Größe der zurückgegebenen Daten innerhalb akzeptabler Grenzen zu halten, verwenden Sie so wenig mehrzeilige Textspalten und Auswahlspalten wie möglich.

Warnung

Das Timeout von fünf (5) Minuten kann je nach Komplexität der Abfrage auf zwei (2) Minuten angepasst werden. Beispielsweise wird bei Abfragen, die SELECT *, NESTED FROMs and/or JOINs enthalten, das Timeout-Limit automatisch auf zwei (2) Minuten Anpassen gesetzt, da diese Abfragen bei längerer Ausführung den Server zu stark belasten. Um eine optimale Leistung zu erzielen, wird empfohlen, die Verwendung dieser Muster in SQL zu vermeiden.

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 Trigger keine in den RetrieveMultipleRequest oder RetrieveRequest Nachrichten registrierten Plug-Ins. 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. Weitere Informationen: Elastische Tabellen

Fehlerbehebung bei Verbindungsproblemen

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

Anmerkung

Die Ports 1433 und/oder 5558 müssen aktiviert werden, um den TDS-Endpunkt von einer Client-Anwendung wie SSMS aus zu verwenden. Wenn Sie nur Port 5558 aktivieren, muss der Benutzer diese Portnummer an den Servernamen im Dialogfeld Verbinden an Server von SSMS anhängen – zum Beispiel: myorgname.crm.dynamics.com,5558.

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.

• Bei Verwendung der Microsoft Entra ID – integrierte Authentifizierung wurde ein Fehler zurückgegeben.

„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)“

• Bei Verwendung der SQL Server -Authentifizierung wurde ein Fehler zurückgegeben.

„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)“

• Bei Verwendung der Windows -Authentifizierung wurde ein Fehler zurückgegeben.

„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. Führen Sie den Befehl Test-NetConnection -ComputerName <RemoteAddress> -Port 1433 aus.

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. Auswählen Systemsteuerung.
   3. Wählen Sie Programme und Funktionen.
   4. Auswählen Windows-Funktionen aktivieren oder deaktivieren.
   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 zum <Umgebungsnamen> wird hergestellt.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-Adreses-Required.

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

So unterscheidet sich Dataverse SQL von Transact-SQLErste Schritte mit virtuellen Tabellen (Entitäten)
Datenabfrage mit FetchXmlService Protection API Limits