Utiliser SQL pour interroger les données

La couche commerciale Microsoft Dataverse fournit un point de terminaison de flux de données tabulaire (TDS) qui émule une connexion de données SQL. La connexion SQL fournit un accès en lecture seule aux données de table de l’environnement Dataverse cible, vous permettant ainsi d’exécuter des requêtes SQL sur les tables de données Dataverse. Aucune vue personnalisée des données n’est fournie. La connexion SQL au point de terminaison Dataverse utilise le modèle de sécurité Dataverse pour l’accès aux données. Les données peuvent être obtenues pour toutes les tables Dataverse auxquelles un utilisateur a accès.

Note

Seule la connexion de données SQL via les bibliothèques SQL Server Management Studio et .NET est en version préliminaire. Power BI est généralement disponible.

Conditions préalables

Le paramètre Activer TDS point de terminaison doit être activé dans votre environnement. Ce paramètre est activé par défaut. Pour plus d’informations : Gérer les paramètres des fonctionnalités

Prise en charge d’applications

La prise en charge des applications du point de terminaison TDS (SQL) pour Power BI et SQL Server Management Studio est décrite ci-après.

SQL Server Management Studio (Version préliminaire)

Vous pouvez également utiliser SQL Server Management Studio (SSMS) version 18.12.1 ou ultérieure avec la Dataverse connexion SQL point de terminaison. Des exemples d’utilisation de SSMS avec la connexion de données SQL sont affichés dans la figure.

Sécurité et authentification

Seule l’authentification Microsoft Entra ID est pris en charge. L’authentification SQL et l’authentification Windows ne sont pas prises en charge. La figure suivante montre un exemple de connexion à la connexion SQL dans SSMS. Notez que le nom du serveur est l’URL de l’adresse de l’organisation.

Note

Les ports 1433 et/ou 5558 doivent être activés pour utiliser le point de terminaison TDS à partir d’une application cliente telle que SSMS. Si vous activez uniquement le port 5558, l’utilisateur doit ajouter ce numéro de port au nom du serveur dans la boîte de dialogue Connecter au serveur de SSMS - par exemple : myorgname.crm.dynamics.com,5558.

Informations sur le cryptage point de terminaison : Protection des données en transit

Exemples de requêtes de données de table

Voici quelques exemples de requêtes composées dans SSMS. La première image montre une requête simple utilisant des alias et l’ordre des résultats.

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

Cette requête suivante montre un JOIN.

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

Power BI (disponibilité générale)

Vous pouvez utiliser l’option Analyser dans Power BI (Données>Tables>Analyser dans Power BI) dans Power Apps (https://make.powerapps.com) pour utiliser le connecteur Dataverse pour analyser les données dans Power BI Desktop. Plus d’informations : Afficher les données du tableau dans Power BI Desktop

Note

Pour activer cette fonctionnalité, consultez le paramètre TDS point de terminaison dans Gérer les paramètres de fonctionnalité. Une fois activé, vous devriez voir un bouton Analyser dans Power BI dans la barre de commandes de Power Apps.

Types d’opérations et de données prises en charge

Aucune opération qui tente de modifier des données (c’est-à-dire INSERT, UPDATE) ne fonctionne avec cette connexion de données SQL en lecture seule. Pour une liste détaillée des opérations SQL prises en charge sur Dataverse point de terminaison, voir En quoi Dataverse SQL diffère de Transact-SQL.

Les types de données suivants ne sont pas pris en charge avec la connexion SQL : Dataverse , binary, image, sql_variant, varbinary, virtual, HierarchyId, managedproperty, file, xml, partylist, timestamp. choices De plus, les types de tables « virtuel » et « audit » ne sont pas pris en charge pour le moment.

Astuce

partylist les attributs peuvent plutôt être interrogés en se joignant à la table comme indiqué dans l’exemple suivant. 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

Comportements de type de colonne de recherche

Dataverse Les colonnes de recherche sont représentées par <lookup>id et <lookup>nom dans un ensemble de résultats.

Comportements de type de colonne de choix

Dataverse Les colonnes de choix sont représentées par <nom de choix>et <étiquette de choix>dans un ensemble de résultats.

Astuce

Après avoir modifié les Étiquettes d’une colonne de choix, les personnalisations de la table doivent être publiées.

Note

L’inclusion d’un grand nombre d’étiquettes de choix dans votre requête aura un impact significatif sur les performances. Il est préférable d’utiliser moins de 10 Étiquettes, si possible. Étant donné que les étiquettes de choix sont localisées, la chaîne localisée est plus coûteuse à renvoyer.

Version SQL signalée

Le point de terminaison TDS Dataverse émule les capacités de requête en lecture seule Microsoft SQL Server sur la logique métier Dataverse. Dataverse renvoie la version actuelle de SQL Azure 12.0.2000.8 pour select @@version.

Guide de performances

Lorsque vous récupérez des données via le point de terminaison TDS, quelques modèles de requête clés doivent être utilisés. Décrits dans les sections suivantes, ces modèles de requête gérent les performances et la taille des ensembles de résultats.

Seules les colonnes nécessaires

Lors de la création d’une requête, ne renvoyez que les colonnes nécessaires. Cette technique facilite à la fois l’exécution de la requête et le transfert des résultats vers l’application cliente. En général, il est recommandé de maintenir le nombre de colonnes d’une requête inférieur à 100.

Choisir des colonnes

Les colonnes de choix sont aplaties en deux colonnes, ce qui facilite leur utilisation. Cependant, il est important d’appliquer tous les filtres et agrégations nécessaires à la partie valeur de la colonne de choix. La partie valeur peut comporter des index et est stockée dans la table de base. Cependant, la partie étiquette (nom « choicecolumn ») est stockée séparément, ce qui coûte plus cher à récupérer, et elle ne peut pas être indexée. L’utilisation d’un nombre important de colonnes d’étiquettes de choix peut générer une requête qui s’exécute plus lentement.

Utiliser les X premiers

Il est important d’utiliser une clause top dans vos requêtes pour éviter d’essayer de renvoyer toute la table de données. Par exemple, use Select Top 1000 accountid,name From account Where revenue > 50000 limite les résultats aux 1 000 premiers comptes.

Ne pas utiliser NOLOCK

Lors de la création de requêtes, n’utilisez pas l’indicateur de table NOLOCK. Cet indicateur empêche Dataverse d’optimiser les requêtes.

Limitations

Le point de terminaison TDS de Dataverse n’a plus de limite de taille maximale stricte. À la place, il y a un délai d’attente fixe de cinq (5) minutes. Avec l’introduction de la diffusion en continu de données, vous pouvez récupérer autant de données que possible dans le délai d’attente fixe de cinq (5) minutes. Envisagez d’utiliser des outils d’intégration de données tels que Azure Synapse Link for Dataverse et dataflows pour les requêtes de données volumineuses qui nécessitent plus de cinq (5) minutes pour être exécutées. Plus d’informations : Importation et exportation de données

Astuce

Pour réussir à maintenir la taille des données renvoyées dans des limites acceptables, utilisez le moins possible de colonnes de texte multiligne et de colonnes de choix.

Avertissement 

Le délai d’expiration de cinq (5) minutes peut être ajusté à deux (2) minutes en fonction de la complexité de la requête. Par exemple, les requêtes contenant SELECT *, NESTED FROMs and/or JOINs réduiront automatiquement la limite de délai d’expiration à deux (2) minutes, car ces requêtes exercent trop de pression sur le serveur lorsqu’elles sont laissées en exécution pendant une longue période. Il est conseillé d’éviter d’utiliser ces modèles dans SQL pour des performances maximales.

Les dates renvoyées dans les résultats de la requête sont au format UTC (Universal Time Coordinated). Auparavant, les dates étaient renvoyées à l’heure locale.

L’interrogation des données à l’aide de SQL n’affecte aucun plug-in enregistré sur les messages RetrieveMultipleRequest ou RetrieveRequest . Toute réécriture de la requête ou des résultats qui est normalement effectuée par ce plug-in ne prend pas effet pour une requête SQL.

Les requêtes utilisant le point de terminaison TDS s’exécutent dans les limites de l’API de protection de service.

Le point de terminaison TDS ne peut pas être utilisé avec des tables élastiques. Plus d’informations : Tables élastiques

Résolution des problèmes de connexion

Examinons quelques conditions d’erreur connues et comment les résoudre.

Note

Les ports 1433 et/ou 5558 doivent être activés pour utiliser le point de terminaison TDS à partir d’une application cliente telle que SSMS. Si vous activez uniquement le port 5558, l’utilisateur doit ajouter ce numéro de port au nom du serveur dans la boîte de dialogue Connecter au serveur de SSMS - par exemple : myorgname.crm.dynamics.com,5558.

Authentification

Seule l’authentification d’Microsoft Entra ID est prise en charge sur la connexion SQL de point de terminaison Dataverse. Le mécanisme d’authentification préféré est « Microsoft Entra ID - Universel » avec authentification multifacteur (MFA). Cependant, « Microsoft Entra ID - Mot de passe » fonctionne si la MFA n’est pas configurée. Si vous essayez d’utiliser d’autres formes d’authentification, vous pouvez voir des erreurs telles que les suivantes.

• Erreur renvoyée lors de l’utilisation de Microsoft Entra ID – Authentification intégrée.

« Échec de la connexion : la requête HTTP a été interdite avec le schéma d’authentification client ’Anonyme’. RequestId : TDS;81d8a4f7-0d49-4d21-8f50-04364bddd370 ; 2 Heure : 2020-12-17T01:10:59.8628578Z (fournisseur de données .Net SqlClient) »

• Erreur renvoyée lors de l’utilisation de l’authentification SQL Server .

« Échec de la connexion : La demande n’est pas authentifiée. RequestId : TDS;918aa372-ccc4-438a-813e-91b086355343 ; 1 Heure : 2020-12-17T01:13:14.4986739Z (fournisseur de données .Net SqlClient) »

• Erreur renvoyée lors de l’utilisation de l’authentification Windows .

« Échec de la connexion : La demande n’est pas authentifiée. RequestId : TDS;fda17c60-93f7-4d5a-ad79-7ddfbb917979 ; 1 Heure : 2020-12-17T01:15:01.0497703Z (fournisseur de données .Net SqlClient) »

Ports bloqués

Une erreur de port bloqué peut ressembler à ceci.

La solution consiste à vérifier que les ports TCP 1433 ou 5558 du client sont débloqués. Utilisez l’une des méthodes suivantes pour débloquer les ports comme décrit ci-dessous.

Utilisez PowerShell pour valider la connexion avec le point de terminaison TDS

1. Ouvrez une fenêtre d’invite de commandes PowerShell.
2. Exécutez la commande Test-connection.
   Test-NetConnection -ComputerName <environment>.crm.dynamics.com -port 1433

Si la connexion réussit, une ligne « TcpTestSucceeded : True » est renvoyée.

Dans certains cas, le trafic peut être bloqué directement au niveau de l’adresse IP. Pour valider que l’adresse IP fonctionne également, prenez l’adresse IP renvoyée par la connexion de test de domaine ci-dessus et remplacez la valeur du paramètre ComputerName par l’adresse IP.

3. Prenez l’adresse renvoyée par la commande ci-dessus comme RemoteAddress
4. Exécutez Test-NetConnection -ComputerName <RemoteAddress> -port 1433

Cette commande devrait retourner « TcpTestSucceeded : True »

Établir une session telnet avec le point de terminaison TDS

1. Sur un ordinateur Microsoft Windows, installez/activez telnet.
   1. Choisissez Démarrer.
   2. Sélectionner Panneau de configuration.
   3. Choisissez Programmes et fonctionnalités.
   4. Sélectionner Activer ou désactiver les fonctionnalités Windows.
   5. Choisissez l’option Client Telnet .
   6. Sélectionnez OK. Une boîte de dialogue apparaît pour confirmer l’installation. La commande telnet devrait être disponible dès maintenant.
2. Exécutez une commande telnet dans une fenêtre de commande.
   telnet <environmentname>.crm.dynamics.com 1433

Si la connexion est établie, vous êtes placé dans une session telnet active. En cas d’échec, vous recevez l’erreur :

"Connexion à <nom d’environnement>.crm.dynamics.com… Impossible d’ouvrir la connexion à l’hôte, sur le port 1433 : échec de la connexion ».

Ce message d’erreur signifie que le port est bloqué chez le client.

Redirection du port non SSL à SSL

La connexion TDS peut échouer lors de l’utilisation d’applications tierces en raison de la redirection du port de 1433/5558 à 443. Cet échec se produit car la règle d’inspection SSL peut bloquer la communication, la raison du blocage étant la « redirection du port non SSL au port SSL ». La solution est d’inclure sur la liste d’autorisation la communication TDS Dataverse sur les proxys web en utilisant les adresses IP.

Pour plus d’informations sur les valeurs d’adresse IP officielles pour accéder au service, consultez Adresses IP requises.

Répertorier les noms d’hôtes sur la liste d’autorisation n’est pas suffisant lors de la connexion à TDS Dataverse, car la redirection de port entre les ports 1433/5558 et 433 s’effectue via l’adresse IP, et non via le nom d’hôte.

Voir aussi

En quoi Dataverse SQL diffère de Transact-SQLCommencer avec les tables virtuelles (entités)
Interroger les données à l’aide de FetchXmlLimites de l’API Service Protection