Usar o SQL para consultar dados

A camada de negócios do Microsoft Dataverse fornece um ponto de extremidade TDS (Fluxo de Dados Tabular) que emula uma conexão de dados SQL. A conexão com o SQL fornece acesso somente leitura aos dados da tabela do ambiente Dataverse de destino, permitindo que você execute consultas SQL nas tabelas de dados do Dataverse. Nenhuma exibição personalizada dos dados é fornecida. A conexão SQL do endpoint Dataverse usa o modelo de segurança do Dataverse para acesso a dados. Os dados podem ser obtidos para todas as tabelas do Dataverse às quais um usuário tem acesso.

Pré-requisitos

A configuração Habilitar ponto de extremidade do TDS deve ser habilitada em seu ambiente. Essa configuração está habilitada por padrão. Mais informações: Gerenciar configurações de recursos

Para prevenir a fuga de dados, ative o controle de acesso no nível do usuário para o ponto final TDS. Atribua uma função de segurança de privilégios mínimos com permissão de acesso a dados apenas às tabelas que seus usuários precisam acessar e conceda o privilégio diverso Permitir que o usuário acesse o ponto de extremidade do TDS.

Suporte a aplicativos

O suporte a aplicativos de ponto de extremidade TDS (SQL) para o Power BI e o SQL Server Management Studio é descrito em seguida.

SQL Server Management Studio

Você também pode usar o SQL Server Management Studio (SSMS) versão 18.12.1 ou posterior com a conexão SQL do ponto de extremidade do Dataverse. Exemplos de uso do SSMS com a conexão de dados SQL são mostrados na figura.

Segurança e autenticação

Somente a autenticação da ID do Microsoft Entra tem suporte. Não há suporte para autenticação sql e autenticação do Windows. A figura a seguir mostra um exemplo de como entrar na conexão SQL no SSMS. Observe que o nome do servidor é a URL de endereço da organização.

Observação

As portas 1433 e/ou 5558 precisam ser habilitadas para usar o ponto de extremidade TDS de um aplicativo cliente, como o SSMS. Se você habilitar apenas a porta 5558, o usuário deverá acrescentar esse número de porta ao nome do servidor na caixa de diálogo Conectar ao Servidor do SSMS , por exemplo: myorgname.crm.dynamics.com.5558.

Informações sobre criptografia de ponto de extremidade: proteção de dados em trânsito

Exemplo de consultas de dados de tabela

Aqui estão algumas consultas de exemplo compostas no SSMS. A primeira imagem mostra uma consulta simples usando aliases e ordenação 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

Esta próxima consulta mostra um JOIN.

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

Power BI (Disponibilidade Geral)

Você pode usar a opção Analisar no Power BI (Dados>Tabelas>Analisar no Power BI) no Power Apps (https://make.powerapps.com) para usar o conector do Dataverse para analisar dados no Power BI Desktop. Mais informações: Exibir dados de tabela no Power BI Desktop

Observação

Para habilitar esse recurso, consulte a configuração do ponto de extremidade do TDS em Gerenciar configurações de recurso. Depois de habilitado, você deverá ver um botão Analisar no Power BI na barra de comandos do Power Apps.

Operações e tipos de dados com suporte

Qualquer operação que tente modificar dados: (ou seja, inserção, atualização) não funciona com essa conexão de dados SQL somente leitura. Para obter uma lista detalhada de operações SQL com suporte no ponto de extremidade do Dataverse, consulte Como o DATAverse SQL difere do Transact-SQL.

Os seguintes tipos de dados do Dataverse não têm suporte com a conexão SQL: binary, , image, sql_variant, varbinary, , virtual, HierarchyId, managedproperty, file, , xml, partylist, timestamp. choices Além disso, não há suporte para os tipos de tabelas 'virtual' e 'audit' no momento.

Dica

partylist em vez disso, os atributos podem ser consultados unindo-se à activityparty tabela, conforme mostrado neste próximo exemplo.

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

Comportamentos do tipo de coluna de pesquisa

As colunas de pesquisa do Dataverse são representadas como <ID de pesquisa> e <nome de pesquisa> em um conjunto de resultados.

Comportamentos dos tipos de coluna de escolha

As colunas de escolha do Dataverse são representadas como <escolha Nome>e <escolha Rótulo>em um conjunto de resultados.

Dica

Depois de fazer alterações nos rótulos de uma coluna de escolha, a tabela precisa ter personalizações publicadas.

Observação

A inclusão de um grande número de rótulos de escolha em sua consulta tem um impacto significativo no desempenho. É melhor usar menos de 10 rótulos, se possível. Como os rótulos de escolha são localizados, a cadeia de caracteres localizada é mais cara de retornar.

Versão do SQL relatada

O endpoint TDS do Dataverse emula as capacidades de consulta em modo somente leitura do Microsoft SQL Server sobre a lógica de negócios do Dataverse. O Dataverse retorna o SQL Azure atual versão 12.0.2000.8 para select @@version.

Diretrizes de desempenho

Quando você recupera dados por meio do endpoint TDS, há alguns principais padrões de consulta que devem ser usados. Descritos nas próximas seções, esses padrões de consulta gerenciam o desempenho e o tamanho dos conjuntos de resultados.

Somente colunas necessárias

Ao criar uma consulta, retorne apenas as colunas necessárias. Essa técnica ajuda na execução da consulta e também na transferência dos resultados para o aplicativo cliente. Em geral, é recomendável manter uma consulta abaixo de 100 colunas.

Colunas de escolha

As colunas de escolha são transformadas em duas colunas, o que melhora a usabilidade. No entanto, é importante fazer agregações e aplicar filtros na seção de valores da coluna de escolha. A parte do valor pode ter índices e é armazenada na tabela base. No entanto, a parte do rótulo (nome 'choicecolumn') é armazenada de forma separada, o que torna mais caro para acessar e impede que seja indexada. O uso de um número significativo de colunas de rótulo de escolha pode gerar uma consulta de execução mais lenta.

Usar o X Superior

É importante usar uma cláusula superior em suas consultas para impedir a tentativa de retornar toda a tabela de dados. Por exemplo, use Select Top 1000 accountid,name From account Where revenue > 50000 limita os resultados para as primeiras 1.000 contas.

Não use NOLOCK

Ao criar consultas, não use a dica de tabela NOLOCK. Essa dica impede que o Dataverse otimize as consultas.

Limitações

O ponto de extremidade do TDS do Dataverse não tem mais um limite de tamanho máximo rígido. Em vez disso, há um tempo limite fixo de cinco (5) minutos. Com a introdução do streaming de dados, você pode recuperar a quantidade máxima de dados possível dentro do tempo limite fixo de cinco minutos. Considere o uso de ferramentas de integração de dados, como o Link do Azure Synapse para Dataverse e fluxos de dados para consultas de dados grandes que exigem mais de cinco (5) minutos para serem concluídas. Mais informações: Importando e exportando dados

Dica

Para ajudar a manter o tamanho dos dados retornados dentro dos limites aceitáveis, use o mínimo possível de colunas de texto de várias linhas e colunas de escolha.

Aviso

O tempo limite de cinco (5) minutos pode ser ajustado para dois (2) minutos, dependendo da complexidade da consulta. Por exemplo, as consultas que contêm , SELECT * ajustam NESTED FROMs and/or JOINsautomaticamente o limite de tempo limite para dois (2) minutos, pois essas consultas colocam muita pressão no servidor quando deixadas em execução por um longo tempo. É recomendável evitar o uso desses padrões no SQL para obter o máximo de desempenho.

As datas retornadas nos resultados da consulta são formatadas como UTC (Universal Time Coordinated). Anteriormente, as datas eram retornadas no horário local.

A consulta de dados usando SQL não dispara nenhum plug-in registrado nas mensagens RetrieveMultipleRequest ou RetrieveRequest. Qualquer reescrita da consulta ou dos resultados normalmente executados por esse plug-in não entra em vigor para uma consulta SQL.

As consultas que utilizam o endpoint TDS são executadas dentro dos limites da API de proteção de serviço.

O endpoint TDS não pode ser usado com tabelas elásticas. Mais informações: tabelas elásticas

Solução de problemas de conexão

Vamos dar uma olhada em algumas condições de erro conhecidas e como resolvê-las.

Observação

As portas 1433 e/ou 5558 precisam ser habilitadas para usar o ponto de extremidade TDS de um aplicativo cliente, como o SSMS. Se você habilitar apenas a porta 5558, o usuário deverá acrescentar esse número de porta ao nome do servidor na caixa de diálogo Conectar ao Servidor do SSMS , por exemplo: myorgname.crm.dynamics.com.5558.

Authentication

Somente a autenticação Microsoft Entra ID é suportada na conexão SQL do endpoint do Dataverse. O mecanismo de autenticação preferencial é "Microsoft Entra ID – Universal" com autenticação multifator (MFA). No entanto, "ID do Microsoft Entra – Senha" funcionará se a MFA não estiver configurada. Se você tentar usar outras formas de autenticação, poderá ver erros como o seguinte.

• Erro retornado ao usar Microsoft Entra ID – Integrated autenticação.

Falha no login: a solicitação HTTP foi proibida com o esquema de autenticação de cliente 'Anonymous'. RequestId: TDS; 81d8a4f7-0d49-4d21-8f50-04364bddd370; 2 Hora: 2020-12-17T01:10:59.8628578Z (Provedor de Dados SqlClient do .Net)

• Erro retornado ao usar a autenticação do SQL Server .

Falha ao fazer login: a requisição não é autenticada. RequestId: TDS;918aa372-ccc4-438a-813e-91b086355343;1 Hora: 2020-12-17T01:13:14.4986739Z (Provedor de Dados SqlClient do .Net)

• Erro retornado ao usar a autenticação do Windows .

"Falha no logon: a solicitação não está autenticada. RequestId: TDS; fda17c60-93f7-4d5a-ad79-7ddfbb917979; 1 Hora: 2020-12-17T01:15:01.0497703Z (Provedor de Dados SqlClient do .Net)

Portas bloqueadas

Um erro de porta bloqueada pode ser semelhante ao seguinte.

A solução é verificar se as portas TCP 1433 ou 5558 do cliente estão desbloqueadas. Use um dos métodos a seguir para desbloquear as portas, conforme descrito a seguir.

Usar o PowerShell para validar a conexão com o endpoint TDS

1. Abra uma janela de comando do PowerShell.
2. Execute o comando Test-connection.
   Test-NetConnection -ComputerName <environment>.crm.dynamics.com -port 1433

Se a conexão for bem-sucedida, uma linha "TcpTestSucceeded: True" será retornada.

Em alguns casos, o tráfego pode ser bloqueado diretamente no nível de IP. Para validar se o endereço IP também está funcionando, pegue o endereço IP retornado da conexão de teste de domínio acima e substitua o valor do parâmetro ComputerName pelo endereço IP.

3. Pegue o endereço retornado pelo comando acima e utilize-o como "RemoteAddress".
4. Executar o Test-NetConnection -ComputerName <RemoteAddress> -port 1433

Esse comando deve retornar "TcpTestSucceededed: True"

Estabelecer uma sessão de telnet para o endpoint TDS

1. Em um computador Microsoft Windows, instale/habilite a telnet.
   1. Escolha Iniciar.
   2. Selecione Painel de Controle.
   3. Escolha Programas e recursos.
   4. Selecione Ativar ou desativar recursos do Windows.
   5. Escolha a opção Cliente Telnet .
   6. Selecione OK. Uma caixa de diálogo é exibida para confirmar a instalação. O comando telnet agora deve estar disponível.
2. Execute um comando telnet em uma janela Comando.
   telnet <environmentname>.crm.dynamics.com 1433

Se a conexão for bem-sucedida, você será colocado em uma sessão de telnet ativa. Se não tiver êxito, você receberá o erro:

"Conectando-se ao <environmentname.crm.dynamics.com>... Não foi possível abrir a conexão com o host, na porta 1433: falha na conexão."

Essa mensagem de erro significa que a porta está bloqueada no cliente.

Redirecionamento de porta de não SSL para SSL

A conexão TDS pode falhar ao usar aplicativos de terceiros devido ao redirecionamento de porta do 1433/5558 para o 443. Essa falha ocorre porque a regra de inspeção SSL pode bloquear a comunicação, onde o motivo do bloqueio é "redirecionamento da porta não SSL para a porta SSL". A solução é incluir a comunicação TDS do Dataverse na lista de permissões em proxies da Web usando endereços IP.

Para obter informações sobre os valores de endereço IP oficiais para acessar o serviço, consulte IP-Addreses-Required.

Permitir listagem de nomes de host não basta ao conectar-se ao Dataverse TDS porque o redirecionamento de porta entre as portas 1433/5558 para 433 está acontecendo via endereço IP, em vez de pelo nome do host.

Consulte também

Como o Dataverse SQL difere do Transact-SQLIntrodução às tabelas virtuais (entidades)
Consultar dados usando FetchXmlLimites da API de Proteção de Serviço