Usar SQL para consultar datos

  • Artículo
  • Tiempo de lectura: 6 minutos

[Este tema es documentación preliminar y está sujeto a modificaciones. Tenga en cuenta que solo la conexión de datos SQL es una versión preliminar. Power BI está disponible de forma general (GA)]

Una conexión de datos SQL está disponible en el punto de conexión Microsoft Dataverse. 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.

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)

Nota

Se ha encontrado un problema de compatibilidad con la compilación de SQL Server Management Studio 18.9.2. Se está investigando una solución. Hasta que la solución esté disponible, use la compilación 18.9.1 de SQL Server Management Studio. Esta nota se actualizará una vez que haya una solución disponible.

También puede usar Estudio de administración de SQL Server (SSMS) versión 18.4 o posterior con la conexión SQL del punto de conexión de Dataverse. A continuación se proporcionan ejemplos de uso de SSMS con la conexión de datos SQL.

Tabla expandida de cuentas.

Seguridad y autenticación

Sólo se admite la autenticación Azure Active Directory. No se admiten la autenticación de SQL ni la autenticación de Windows. A continuación se 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.

Diálogo de conexió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 anexar ese número de puerto al nombre del servidor en el cuadro de diálogo Conectar al servidor en SSMS. Por ejemplo: myorgname.crm.dynamics.com;5558.

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

Consulta simple usando alias y ordenando.

La siguiente consulta muestra un JOIN.

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

Otra consulta usando un JOIN.

Power BI

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 funcionará 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.

Sugerencia

Los atributos partylist pueden consultarse combinando con la tabla activityparty, como se muestra a continuación.

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.

Limitaciones

Hay un límite de tamaño máximo de 80 MB para los resultados de consulta devueltos por el punto de conexión de Dataverse. Considere el uso de 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 80 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.

Solución de problemas de conexión

A continuación, se muestran 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 anexar ese número de puerto al nombre del servidor en el cuadro de diálogo Conectar al servidor en SSMS. Por ejemplo: myorgname.crm.dynamics.com;5558.

Autenticación

Solo se admite la autenticación de Azure Active Directory en la conexión SQL del punto acceso de Dataverse. El mecanismo de autenticación preferido es "Azure Active Directory - Universal" con autenticación multifactor (MFA). Sin embargo, "Azure Active Directory - Contraseña" funcionará 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 Azure Active Directory - 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.

Mensaje de error.

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 hacer lo que 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 devolverá la línea "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, estará en una sesión de Telnet activa. De lo contrario, recibirá 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".

Esto significa que el puerto se ha bloqueado en el cliente.

Consultar también

Usar FetchXML para crear una consulta
Límites de la API de protección de servicio