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.

Notes

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 le point de terminaison TDS 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 aussi utiliser SQL Server Management Studio (SSMS) version 18.12.1 ou ultérieure avec la connexion SQL du point de terminaison Dataverse. 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.

Notes

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 : nomdemonorg.crm.dynamics.com,5558.

Informations sur le chiffrement du point de terminaison : Protection des données de 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. En savoir plus : Afficher les données de table dans Power BI Desktop

Notes

Pour activer cette fonctionnalité, consultez le paramètre de point de terminaison TDS dans Gérer les paramètres des fonctionnalités. 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 le point de terminaison Dataverse, voir Différences entre Dataverse SQL et Transact-SQL.

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

Conseil

Les attributs partylist peuvent à la place être interrogés en se joignant à la table activityparty, comme illustré dans cet exemple suivant.

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

Les colonnes de recherche Dataverse sont représentées comme <lookup>id et le <lookup>name dans un jeu de résultats.

Comportements de type de colonne de choix

Les colonnes de choix Dataverse sont représentées comme <choice>Name et le <choice>Étiquette dans un jeu de résultats.

Conseil

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

Notes

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 SQL Azure actuelle 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, utiliser Select Top 1000 accountid,name From account Where revenue > 50000 a pour effet de limiter 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. Pensez à utiliser des outils d’intégration de données tels que Azure Synapse Link for Dataverse et des flux de données pour les requêtes de données volumineuses qui nécessitent plus de cinq (5) minutes pour s’exécuter. Pour plus d’informations : Importation et exportation de données

Conseil

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.

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 de données à l’aide de SQL ne déclenche aucun plug-in inscrit dans 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. Pour plus d’informations : Tables élastiques

Résolution des problèmes de connexion

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

Notes

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 : nomdemonorg.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 l’authentification Microsoft Entra ID - Intégré.

« É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 le 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électionnez un Panneau de configuration.
   3. Choisissez Programmes et fonctionnalités.
   4. Sélectionnez Activer ou désactiver des fonctionnalités Windows.
   5. Choisissez l’option Client Telnet.
   6. Cliquez sur 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 en cours à <environmentname>.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

Différences entre Dataverse SQL et Transact-SQL Se familiariser avec les tables virtuelles (entités)
Interroger des données à l’aide de FetchXml Limites de l’API de protection du service

Notes

Pouvez-vous nous indiquer vos préférences de langue pour la documentation ? Répondez à un court questionnaire. (veuillez noter que ce questionnaire est en anglais)

Le questionnaire vous prendra environ sept minutes. Aucune donnée personnelle n’est collectée (déclaration de confidentialité).