Usar SQL para consultar datos

La capa empresarial de Microsoft Dataverse proporciona un punto de conexión de flujo de datos tabulares (TDS) que emula una conexión de datos SQL. La conexión SQL proporciona acceso de solo lectura a los datos de la tabla del entorno de destino de Dataverse, lo que le permite ejecutar consultas SQL en las tablas de datos de Dataverse. No se han proporcionado vistas personalizadas de los datos. La conexión SQL del punto de conexión de Dataverse usa el modelo de seguridad de Dataverse para el acceso a los datos. Se pueden obtener datos para todas las tablas de Dataverse a las que tiene acceso un usuario.

Nota

Solo la conexión de datos SQL a través de SQL Server Management Studio y las bibliotecas .NET está en versión preliminar. Power BI está disponible en general.

Requisitos previos

La opción Habilitar punto de conexión TDS debe estar habilitada en el entorno. Esta opción está habilitada de forma predeterminada. Más información: Administrar la configuración de las características

Soporte de aplicaciones

A continuación se describe la compatibilidad de aplicaciones de punto de conexión TDS (SQL) para Power BI y SQL Server Management Studio.

SQL Server Management Studio (versión preliminar)

También puede usar Estudio de administración de SQL Server (SSMS) versión 18.12.1 o posterior con la conexión SQL del punto de conexión de Dataverse. En la figura se muestran ejemplos de uso de SSMS con la conexión de datos SQL.

Seguridad y autenticación

Sólo se admite la autenticación Microsoft Entra ID. No se admiten la autenticación de SQL ni la autenticación de Windows. La siguiente figura muestra un ejemplo de cómo iniciar sesión en la conexión SQL en SSMS. Observe que el nombre del servidor es la dirección URL de la organización.

Nota

Los puertos 1433 y/o 5558 deben estar habilitados para usar el extremo de TDS desde una aplicación cliente como SSMS. Si solo habilita el puerto 5558, el usuario debe agregar ese número de puerto al nombre del servidor en el cuadro de diálogo Conectar al servidor de SSMS, por ejemplo: myorgname.crm.dynamics.com,5558.

Información sobre el cifrado de puntos de conexión: Protección de datos en tránsito

Consultas de datos de tabla de ejemplo

A continuación hay un par de consultas de ejemplo compuestas en SSMS. La primera imagen muestra una consulta simple usando alias y ordenamiento de resultados.

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

La siguiente consulta muestra un JOIN.

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

Power BI (disponibilidad general)

Puede usar la opción Analizar en Power BI (Datos > Tablas > Analizar en Power BI) en Power Apps (https://make.powerapps.com) para usar el conector de Dataverse para analizar datos en Power BI Desktop. Más información: Ver datos de tabla en Power BI Desktop

Nota

Para habilitar esta característica, consulte la configuración de punto de conexión TDS en Administrar la configuración de características. Una vez habilitada, debería ver un botón Analizar en Power BI en la barra de comandos de Power Apps.

Tipos de datos y operaciones compatibles

Cualquier operación que trate de modificar datos (es decir, INSERT, UPDATE) no funciona con esta conexión de datos SQL de solo lectura. Para obtener una lista detallada de las operaciones SQL admitidas en punto de conexión de Dataverse, consulte Cómo se diferencia el SQL de Dataverse de Transact-SQL.

Los siguientes tipos de datos de Dataverse no son compatibles con la conexión SQL: binary, image, sql_variant, varbinary, virtual, HierarchyId, managedproperty, file, xml, partylist, timestamp, choices. Además, los tipos de tablas 'virtual' y 'audit' no son compatibles en este momento.

Sugerencia

Los atributos partylist pueden consultarse combinando con la tabla activityparty, como se muestra en el siguiente ejemplo.

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

Comportamientos de tipo de columna de búsqueda

Las columnas de búsqueda de Dataverse se representan como <lookup>id y <lookup>nombre en un conjunto de resultados.

Comportamientos de tipo de columna de elección

Las columnas de elección de Dataverse se representan como <choice>Nombre y <choice>Etiqueta en un conjunto de resultados.

Sugerencia

Después de realizar cambios en las etiquetas de una columna de opciones, es necesario que se publiquen las personalizaciones de la tabla.

Nota

Incluir una gran cantidad de etiquetas de opciones en su consulta tendrá un impacto significativo en el rendimiento. Es mejor usar menos de 10 etiquetas si es posible. Debido a que las etiquetas de elección están localizadas, la cadena localizada es más costosa de devolver.

Versión SQL con informe

El punto de conexión Dataverse TDS emula las capacidades de consulta de solo lectura de Microsoft SQL Server sobre la lógica de negocios de Dataverse. Dataverse devuelve la versión actual de SQL Azure 12.0.2000.8 para select @@version.

Guía de rendimiento

Cuando recupere datos a través del punto de conexión TDS, hay algunos patrones de consulta clave que deben utilizarse. Estos patrones de consulta, que se describen en las siguientes secciones, gestionan el rendimiento y el tamaño de los conjuntos de resultados.

Solo las columnas necesarias

Al crear una consulta, devuelva solo las columnas necesarias. Esta técnica ayuda tanto a la ejecución de la consulta como a la transferencia de los resultados a la aplicación cliente. En general, se recomienda mantener una consulta de menos de 100 columnas.

Columnas de elección

Las columnas de elección se aplanan en dos columnas, lo que mejora la facilidad de uso. Sin embargo, es importante realizar agregados y filtros en la parte de valor de la columna de elección. La parte de valor puede tener índices y se almacena en la tabla base. Sin embargo, la parte de etiqueta (nombre 'choicecolumn') se almacena por separado, lo que cuesta más recuperarla y no se puede indexar. El uso de una cantidad significativa de columnas de etiquetas de elección puede generar una consulta con un rendimiento más lento.

Usar X superior

Es importante utilizar una cláusula superior en sus consultas para evitar intentar devolver toda la tabla de datos. Por ejemplo, use Select Top 1000 accountid,name From account Where revenue > 50000 limita los resultados a las primeras 1000 cuentas.

No use NOLOCK

Al crear consultas, no utilice la sugerencia de tabla NOLOCK. Esta sugerencia impide que Dataverse optimice las consultas.

Limitaciones

El punto de conexión de TDS de Dataverse ya no tiene un límite de tamaño máximo estricto. En cambio, hay un tiempo de espera fijo de cinco (5) minutos. Con la introducción de la transmisión de datos, puede recuperar tantos datos como se puedan completar en el tiempo de espera fijo de cinco (5) minutos. Considere la posibilidad de utilizar herramientas de integración de datos como Azure Synapse Link for Dataverse y flujos de datos para consultas de datos grandes que devuelven más de 5 MB de datos. Más información: Importación y exportación de datos

Sugerencia

Para ayudar a mantener el tamaño de los datos devueltos dentro de límites aceptables, use la menor cantidad posible de columnas de texto de varias líneas y columnas de opciones.

Las fechas devueltas en los resultados de la consulta tienen el formato de hora universal coordinada (UTC). Anteriormente, las fechas se devolvían en la hora local.

La consulta de datos mediante SQL no activa ningún complemento registrado en los mensajes de RetrieveMultipleRequest ni de RetrieveRequest. Por lo tanto, cualquier reescritura de la consulta o resultados que normalmente se realizarían con dicho complemento no tendrá efecto para una consulta SQL.

Las consultas que utilizan el punto de conexión TDS se ejecutan bajo los límites de la API de protección del servicio.

El TDS punto de conexión no se puede usar con tablas elásticas. Más información: Tablas elásticas

Solución de problemas de conexión

Veamos algunas condiciones de error conocidas y cómo resolverlas.

Nota

Los puertos 1433 y/o 5558 deben estar habilitados para usar el extremo de TDS desde una aplicación cliente como SSMS. Si solo habilita el puerto 5558, el usuario debe agregar ese número de puerto al nombre del servidor en el cuadro de diálogo Conectar al servidor de SSMS, por ejemplo: myorgname.crm.dynamics.com,5558.

Autenticación

Solo se admite la autenticación de Microsoft Entra ID en la conexión SQL del punto acceso de Dataverse. El mecanismo de autenticación preferido es "Microsoft Entra ID - Universal" con autenticación multifactor (MFA). Sin embargo, "Microsoft Entra ID - Contraseña" funciona si MFA no está configurado. Si intenta utilizar otras formas de autenticación, verá errores como los siguientes.

• Error devuelto al usar la autenticación Microsoft Entra ID - Integrada.

"Error de inicio de sesión: se prohibió la solicitud HTTP con el esquema de autenticación de cliente 'Anónimo'. RequestId: TDS;81d8a4f7-0d49-4d21-8f50-04364bddd370;2 Time: 2020-12-17T01:10:59.8628578Z (.Net SqlClient Data Provider)"

• Error devuelto al usar la autenticación de SQL Server.

“Error de inicio de sesión. Solicitud no autenticada. RequestId: TDS;918aa372-ccc4-438a-813e-91b086355343;1 Time: 2020-12-17T01:13:14.4986739Z (.Net SqlClient Data Provider)"

• Error devuelto al usar la autenticación de Windows.

“Error de inicio de sesión. Solicitud no autenticada. RequestId: TDS;fda17c60-93f7-4d5a-ad79-7ddfbb917979;1 Time: 2020-12-17T01:15:01.0497703Z (.Net SqlClient Data Provider)"

Puertos bloqueados

Un error de puerto bloqueado puede tener la siguiente apariencia.

La solución es verificar que los puertos TCP 1433 o 5558 del cliente estén desbloqueados. Utilice uno de los siguientes métodos para desbloquear los puertos, tal y como se describe a continuación.

Use PowerShell para validar la conexión con punto de conexión TDS

1. Abra una ventana de comandos de PowerShell.
2. Ejecute el comando Test-connection.
   Test-NetConnection -ComputerName <environment>.crm.dynamics.com -port 1433

Si la conexión se realiza correctamente, se devuelve la línea "TcpTestSucceeded : True".

En algunos casos, el tráfico se puede bloquear directamente a nivel de IP. Para validar que la dirección IP también funciona, tome la dirección IP devuelta de la conexión de prueba de dominio anterior y reemplace el valor del parámetro ComputerName con la dirección IP.

3. Tome la dirección devuelta del comando anterior como "RemoteAddress"
4. Ejecute Test-NetConnection -ComputerName <RemoteAddress> -puerto 1433

Este comando debería devolver "TcpTestSucceeded: True"

Establezca una sesión de telnet con el punto de conexión TDS

1. En ordenador Microsoft Windows,, instale o habilite Telnet.
   1. Elija Inicio.
   2. Seleccione Panel de control.
   3. Elija Programas y características.
   4. Seleccione Activar o desactivar las características de Windows.
   5. Elija la opción Cliente Telnet.
   6. Seleccione Aceptar. Aparece un cuadro de diálogo para confirmar la instalación. El comando de Telnet debería estar disponible ahora.
2. Ejecute un comando de Telnet en una ventana de comandos.
   telnet <environmentname>.crm.dynamics.com 1433

Si se establece correctamente la conexión, se le colocará en una sesión de Telnet activa. De lo contrario, recibe el error:

"Conectando a <environmentname>.crm.dynamics.com… No se pudo abrir la conexión para el host, en el puerto 1433: error de conexión".

Este mensaje de error significa que el puerto se ha bloqueado en el cliente.

Redirección de puerto de no SSL a SSL

La conexión TDS puede fallar cuando se utilizan aplicaciones de terceros debido a la redirección de puertos del 1433/5558 al 443. Este error se produce porque la regla de inspección SSL puede bloquear la comunicación, siendo el motivo del bloqueo la "redirección de un puerto no SSL a un puerto SSL". La solución es incluir en la lista de permitidos Comunicación TDS Dataverse en servidores proxy web mediante direcciones IP.

Para obtener información sobre los valores de dirección IP oficiales para acceder al servicio, consulte Direcciones IP requeridas.

Listar los nombres de host en la lista de permitidos no es suficiente cuando se conecta a Dataverse TDS porque la redirección de puertos entre los puertos 1433/5558 y 433 se realiza a través de la dirección IP, no a través del nombre de host.

Consulte también

En qué se diferencia Dataverse SQL de Transact-SQL Introducción a las tablas virtuales (entidades)
Consultar datos usando FetchXml Límites de la API de protección de servicios

Nota

¿Puede indicarnos sus preferencias de idioma de documentación? Realice una breve encuesta. (tenga en cuenta que esta encuesta está en inglés)

La encuesta durará unos siete minutos. No se recopilan datos personales (declaración de privacidad).