Referência técnica do conector SQL genérico

Este artigo descreve o conector SQL genérico. O artigo se aplica aos seguintes produtos:

• Microsoft Identity Manager 2016 (MIM2016)
• Microsoft Entra ID

Por MIM2016, o Conector está disponível para download no Centro de Download da Microsoft.

Para ver esse conector em ação, consulte o artigo Generic SQL Connector step-by-step .

Observação

A ID do Microsoft Entra agora fornece uma solução baseada em agente leve para provisionar usuários em um banco de dados SQL, sem a necessidade de uma implantação de sincronização do MIM. Recomendamos usá-lo para provisionamento de usuário de saída. Saiba mais.

Visão geral do conector SQL genérico

O conector SQL genérico permite que você integre o serviço de sincronização com um sistema de banco de dados que oferece conectividade ODBC.

Partindo de um ponto de vista detalhado, os seguintes recursos têm suporte na versão atual do conector:

Recurso	Suporte
Fonte de dados conectada	O conector é compatível com todos os drivers ODBC de 64 bits*. Ele foi testado com o seguinte: Microsoft SQL Server & SQL Azure IBM DB2 11.5.8 Oracle 11g Oracle 12c e 18c Oráculo 21c e 23c MySQL 5.x MySQL 8.x Postgres
Cenários	Gerenciamento de ciclo de vida do objeto Gerenciamento de senha
Operações	Importação completa e importação delta, exportação Para exportar: Adicionar, Excluir, Atualizar e Substituir Definir senha, alterar senha
Esquema	Detecção dinâmica de objetos e atributos

Pré-requisitos

Para usar o conector, verifique se você tem os seguintes itens no servidor de sincronização:

• Microsoft .NET 4.6.2 Framework ou posterior
• Drivers de cliente ODBC de 64 bits
• Se você estiver usando o conector para se comunicar com o Oracle 12c, isso exigirá o Oracle Instant Client 12.2.0.1 ou mais recente com o pacote ODBC.
• Se você estiver usando o conector para se comunicar com o Oracle 18c-23c, isso exigirá que o Oracle Instant Client 18-23 ou mais recente com o Pacote ODBC e a variável de sistema NLS_LANG seja definida para suportar caracteres UTF8, por exemplo, NLS_LANG=AMERICAN_AMERICA. AL32UTF8.
• Esse conector usa instruções preparadas para SQL e várias instruções por transação. Alguns sistemas RDBM podem ter problemas em seus drivers ODBC relacionados ao tratamento de transações, instruções SQL preparadas pelo lado do servidor e várias instruções na mesma transação. Configure suas opções de conexão DSN de acordo para garantir que essas instruções sejam enviadas corretamente ao seu banco de dados., Por exemplo, o MySQL ODBC Driver versão 8.0.32 precisa das opções NO_SSPS=1 e MULTI_STATEMENTS=1. Outras opções como "confirmação automática" ou "confirmação somente em operações bem-sucedidas" podem afetar a forma como as exportações em lote são tratadas; Consulte o administrador do banco de dados para obter detalhes. Para solucionar problemas durante a exportação, defina o tamanho do lote de exportação como 1 e habilite o log detalhado do conector.

A implantação desse conector pode exigir alterações na configuração do banco de dados, bem como alterações de configuração no MIM. Para implantações que envolvem a integração do MIM com um servidor de banco de dados de terceiros em um ambiente de produção, recomendamos que os clientes trabalhem com seu fornecedor de banco de dados ou um parceiro de implantação para obter ajuda, orientação e suporte para essa integração.

Permissões na fonte de dados conectada

Para criar ou executar qualquer uma das tarefas com suporte no conector SQL genérico, você deve ter:

• db_datareader
• db_datawriter

Portas e protocolos

Para as portas necessárias ao funcionamento do driver ODBC, confira a documentação do fornecedor do banco de dados.

Criar um novo conector

Para criar um conector SQL genérico, em Serviço de Sincronização selecione Agente de Gerenciamento e Criar. Selecione o Conector SQL genérico (Microsoft) .

Conectividade

O conector usa um arquivo DSN ODBC para conectividade. Crie o arquivo DSN usando Fontes de Dados ODBC encontrado no menu Iniciar em Ferramentas Administrativas. A ferramenta administrativa, cria um DSN de Arquivo para que ele possa ser fornecido ao conector.

A tela Conectividade é a primeira quando você cria um novo conector SQL genérico. Primeiro, forneça as seguintes informações:

• Caminho do arquivo DSN
• Autenticação
  • Nome do usuário
  • Senha

O banco de dados deve dar suporte a um dos seguintes métodos de autenticação:

• Autenticação do Windows: o banco de dados de autenticação usa as credenciais do Windows para verificar o usuário. O nome de usuário/senha especificados são usados para autenticar com o banco de dados. Essa conta precisa de permissões para o banco de dados.
• Autenticação do SQL: o banco de dados de autenticação usa o nome de usuário/senha definido na tela Conectividade para se conectar ao banco de dados. Se você armazenar a nome de usuário/senha no arquivo DSN, as credenciais fornecidas na tela Conectividade terão precedência.
• Autenticação do Banco de Dados SQL do Azure: para obter mais informações, consulte Conectar-se ao Banco de Dados SQL por meio da autenticação do Microsoft Entra.

DN é Âncora: se você selecionar esta opção, o DN também será usado como atributo de âncora. Ele pode ser usado para uma implementação simples, mas também tem as seguintes limitações:

• O conector dá suporte a apenas um tipo de objeto. Portanto, qualquer atributo de referência só pode fazer referência ao mesmo tipo de objeto.

Tipo de Exportação: Substituir Objeto: durante a exportação, quando apenas alguns atributos tiverem sido alteradas, o objeto inteiro com todos os atributos será exportado e substituirá o objeto existente.

Esquema 1 (Detectar tipos de objeto)

Nesta página você configurará como o conector localizará tipos de objeto diferentes no banco de dados.

Cada tipo de objeto é apresentado como uma partição e configurado ainda mais em Configurar Partições e Hierarquias.

Método de detecção do Tipo de objeto: o conector dá suporte a esses métodos de detecção do tipo de objeto.

• Valor Fixo: forneça a lista de tipos de objeto como uma lista separada por vírgulas. Por exemplo: User,Group,Department.
  
• Tabela/exibição/procedimento armazenado: forneça o nome da tabela/exibição/procedimento armazenado e, em seguida, o nome da coluna que fornece a lista de tipos de objeto. Se você usar um procedimento armazenado, forneça também parâmetros para ele no formato [Nome]:[Direção]:[Valor]. Forneça cada parâmetro em uma linha separada (use Ctrl + Enter para obter uma nova linha).
  
• Consulta SQL: essa opção permite que você forneça uma consulta SQL que retorna uma única coluna com tipos de objeto, por exemplo, SELECT [Column Name] FROM TABLENAME. A coluna retornada deve ser do tipo cadeia de caracteres (varchar).

Esquema 2 (Detectar tipos de atributo)

Nesta página, você configurará como os nomes e tipos de atributos serão detectados. As opções de configuração são listadas para cada tipo de objeto detectado na página anterior.

Método de detecção do tipo de atributo: o conector dá suporte a esses métodos de detecção do tipo de atributo com cada tipo de objeto detectado na tela Esquema 1.

• Tabela/exibição/procedimento armazenado: forneça o nome da tabela/exibição/procedimento armazenado que deve ser usado para localizar nomes de atributo. Se você usar um procedimento armazenado, forneça também parâmetros para ele no formato [Nome]:[Direção]:[Valor]. Forneça cada parâmetro em uma linha separada (use Ctrl + Enter para obter uma nova linha). Para detectar nomes de atributo em um atributo de valores múltiplos, forneça uma lista separada por vírgulas de tabelas ou exibições. Não haverá suporte para cenários de valores múltiplos quando a tabela pai e filho tiverem os mesmos nomes de coluna.
• Consulta SQL: essa opção permite que você forneça uma consulta SQL que retorna uma única coluna com nomes de atributo, por exemplo, SELECT [Column Name] FROM TABLENAME. A coluna retornada deve ser do tipo cadeia de caracteres (varchar).

Esquema 3 (Definir âncora e DN)

Esta página permite que você configure a âncora e o atributo DN para cada tipo de objeto detectado. Você pode selecionar vários atributos para tornar a âncora exclusiva.

• Atributos de valores múltiplos e boolianos não são listados.

• O mesmo atributo não pode usar DN e âncora, a menos que DN é Âncora seja selecionado na página Conectividade.

• Se DN é Âncora estiver selecionado na página Conectividade, esta página só solicitará o atributo DN. Esse atributo será usado também como o atributo de âncora.

  

Esquema 4 (Definir tipo de atributo, referência e direção)

Essa página permite que você configure o tipo de atributo, como inteiro, binário ou Booliano, e a direção para cada atributo. Todos os atributos da página Esquema 2 estão listados como atributos de valores múltiplos.

• DataType: usado para mapear o tipo de atributo para os tipos conhecidos do mecanismo de sincronização. O padrão é usar o mesmo tipo, conforme detectado no esquema SQL, mas DateTime e referência não são facilmente detectáveis. Para esses você precisa especificar DateTime ou Referência.
• Direção: você pode definir a direção do atributo para Importar, Exportar ou ImportExport. ImportExport é o padrão.

Observações:

• Se um tipo de atributo não for detectável pelo conector, ele usará o tipo de dados String.
• Tabelas aninhadas podem ser consideradas tabelas de banco de dados de uma coluna. O Oracle armazena as linhas de uma tabela aninhada sem nenhuma ordem específica. No entanto, quando você recupera a tabela aninhada em uma variável de PL/SQL, as linhas recebem subscritos consecutivos, começando em 1. Que lhe dá acesso do tipo matriz a linhas individuais.
• VARRYS não têm suporte no conector.

Esquema 5 (Definir partição para atributos de referência)

Nesta página você configura todos os atributos de referência a cuja partição (tipo de objeto) um atributo está se referindo.

Se você usar DN é âncora, deverá usar o mesmo tipo de objeto do qual está fazendo referência. Não é possível referenciar outro tipo de objeto.

Observação

A partir da atualização de março de 2017 há uma opção para "*" e quando essa opção é escolhida todos os tipos de membro possíveis serão importados.

Importante

Desde maio de 2017 o "*" também conhecido como qualquer opção foi alterado para dar suporte ao fluxo de importação e exportação. Se você quiser usar essa opção, a exibição/tabela de vários valores deve ter um atributo que contém o tipo de objeto.

Se "*" for selecionado, o nome da coluna com o tipo de objeto também deverá ser especificado.

Após a importação você verá algo semelhante à imagem a seguir:

Parâmetros Globais

A página Parâmetros Globais é usada para configurar importação Delta, formato de data/hora e método de senha.

O conector SQL genérico dá suporte aos seguintes métodos de importação Delta:

• Gatilho: consulte Gerar exibições de Delta usando gatilhos.
• Marca d'água: uma abordagem genérica que pode ser usada com qualquer banco de dados. A consulta de marca-d'água será preenchida com base no fornecedor do banco de dados. Uma coluna de marca-d'água deve estar presente em cada tabela/exibição usada. Essa coluna deve controlar inserções e atualizações para as tabelas e suas tabelas dependentes (de valores múltiplos ou filho). Os relógios entre o serviço de sincronização e o servidor de banco de dados devem ser sincronizados. Caso contrário, algumas entradas na importação delta podem ser omitidas.
  Limitação:
  • A estratégia de marca-d'água não dá suporte a exclusão de objetos.
• Instantâneo(funciona somente com o Microsoft SQL Server) Gerando exibições de Delta usando instantâneos
• Acompanhamento de alterações(funciona somente com o Microsoft SQL Server) About Acompanhamento de alterações
  Limitações:
  • Atributo de âncora e DN devem ser parte da chave primária para o objeto selecionado na tabela.
  • Consulta SQL não tem suporte durante importação e exportação com acompanhamento de alterações.

Parâmetros adicionais: especifique o fuso horário do servidor de banco de dados que indica o local em que o servidor de banco de dados está localizado. Esse valor é usado para dar suporte aos vários formatos de atributos de data e hora.

O conector sempre armazena data e data e hora no formato UTC. Para converter corretamente datas e horas, o fuso horário do servidor de banco de dados e o formato usado devem ser especificados. O formato deve ser expresso no formato .NET.

Durante a exportação, todos os atributos de data e hora devem ser fornecidos ao conector no formato de hora UTC.

Configuração de senha: o conector fornece recursos de sincronização de senha e dá suporte a definição e alteração de senha.

O conector fornece dois métodos para dar suporte à sincronização de senha:

• Procedimento armazenado: esse método requer dois procedimentos armazenados para dar suporte a definição e alteração de senha. Digite todos os parâmetros para adicionar e alterar a operação de senha em Definir procedimento armazenado de senha e Alterar parâmetros de procedimento armazenado de senha como nos exemplos abaixo.
• Extensão de senha: este método requer DLL de extensão de senha (você precisa fornecer o nome DLL de extensão que está implementando a interface IMAExtensible2Password ). O assembly de extensão de senha deve ser colocado na pasta de extensão para que o conector possa carregar a DLL no runtime.

Você também deve habilitar o Gerenciamento de senhas na página Configurar Extensão .

Configurar partições e hierarquias

Na página de partições e hierarquias, selecione todos os tipos de objeto. Cada tipo de objeto está em sua própria partição.

Você também pode substituir os valores definidos na página Conectividade ou Parâmetros Globais.

Configurar Âncoras

Esta página é somente leitura, pois a âncora já foi definida. O atributo de âncora selecionado sempre é acrescentado com o tipo de objeto para garantir que ele permaneça exclusivo em todos os tipos de objeto.

Configurar parâmetros da etapa de execução

Essas etapas são configuradas em perfis de execução do conector. Essas configurações fazem o trabalho real de importar e exportar dados.

Importação completa e Delta

O conector SQL genérico oferece suporte a importação completa e Delta usando estes métodos genérico:

• Tabela
• Visualizar
• Procedimento armazenado
• Consulta SQL

Tabela/Exibição
Para importar atributos de valores múltiplos de um objeto, você precisa fornecer o nome de tabela/exibição em Nome de tabela/exibições de valores múltiplos e as respectivas condições de junção em Condição de junção com a tabela principal. Se houver mais de uma tabela com vários valores na fonte de dados, use a união para uma única exibição.

Importante

O agente de gerenciamento do SQL Genérico só pode trabalhar com uma tabela com vários valores. Não coloque no Nome de tabela/exibições de vários valores mais de um nome de tabela. É a limitação do SQL Genérico.

Exemplo: você deseja importar o objeto de funcionário e todos os seus atributos de valores múltiplos. Há duas tabelas chamadas Funcionário (tabela principal) e Departamento (valores múltiplos). Faça o seguinte:

• Digite Funcionário em Tabela/Exibição/SP.
• Digite Departamento em Nome da tabela/exibições de valores múltiplos.
• Digite a condição de junção entre Funcionário e Departamento em Condição de Junção, por exemplo, Employee.DEPTID=Department.DepartmentID.

Procedimentos armazenados

• Se você tem muitos dados, é recomendável implementar a paginação com os procedimentos armazenados.
• Para o procedimento armazenado der suporte à paginação, forneça o Índice inicial e o final. Consulte: Paginação eficiente em grandes quantidades de dados.
• @StartIndex e @EndIndex são substituídos em tempo de execução pelo respectivo valor do tamanho de página configurado na página Configurar Etapa. Por exemplo, quando o conector recupera a primeira página e o tamanho da página é definido como 500, nessa situação @StartIndex é 1 e @EndIndex é 500. Esses valores aumentam quando o conector recupera páginas subsequentes e altera o valor de @StartIndex e @EndIndex.
• Para executar o procedimento armazenado com parâmetros, forneça os parâmetros no formato [Name]:[Direction]:[Value] . Forneça cada parâmetro em uma linha separada (use Ctrl + Enter para obter uma nova linha).
• O conector do SQL genérico também dá suporte à operação de importação de Servidores Vinculados no Microsoft SQL Server. Se informações precisarem ser recuperadas de uma Tabela no servidor Vinculado, a Tabela deverá ser fornecida no formato: [ServerName].[Database].[Schema].[TableName]
• O conector SQL genérico só dá suporte a objetos com estrutura semelhante (nome de alias e tipo de dados) entre informações de etapas de execução e detecção de esquema. Se o objeto selecionado do esquema e as informações fornecidas na etapa de execução forem diferentes, o conector do SQL será não poderá dar suporte a esse tipo de cenário.

Consulta SQL

Importante

CRLF ou caractere de nova linha serve como um separador entre várias instruções.

Exemplo de consulta SQL com paginação - consulta incorreta, não funcionará quando o caractere de nova linha for usado:

WITH A AS 
  (select dense_rank() over (order by BusinessEntityID) 
    rownumber, BusinessEntityID, DeptID, NationalIDNumber, LoginID, JobTitle, BirthDate, MaritalStatus, HireDate, ModifiedDate, Password 
    from Employees
  ) select * from A where rownumber between @StartIndex and @EndIndex

Exemplo de consulta SQL com paginação - consulta correta:

WITH A AS (select dense_rank() over (order by BusinessEntityID) rownumber, BusinessEntityID, DeptID, NationalIDNumber, LoginID, JobTitle, BirthDate, MaritalStatus, HireDate, ModifiedDate, Password from Employees) select * from A where rownumber between @StartIndex and @EndIndex

• Consultas com vários conjuntos de resultados não têm suporte.
• A consulta SQL dá suporte à paginação e fornece o Índice Inicial e o Índice Final como uma variável para dar suporte à paginação.

Importação de delta

A configuração de Importação de Delta exige alguma configuração adicional, em comparação com a Importação Completa.

• Se você escolher a abordagem de instantâneo ou gatilho para acompanhar as alterações delta, forneça banco de dados de uma tabela de histórico ou instantâneo na caixa Nome de banco de dados de Instantâneo ou tabela de Histórico .
• Você também precisa fornecer a condição de junção entre a tabela de histórico e tabela principal, por exemplo, Employee.ID=History.EmployeeID
• Para controlar a transação na tabela principal da tabela de histórico, você deve fornecer o nome da coluna que contém as informações da operação (Adicionar/Atualizar/Excluir).
• Se você escolher a Marca d'água para acompanhar as alterações delta, forneça o nome da coluna que contém as informações de operação em Nome de Coluna de Marca d'água.
• A coluna alterar atributo de tipo é necessária para o tipo de alteração. Essa coluna mapeia uma alteração que ocorre na tabela primária ou tabela de valores múltiplos para um tipo de alteração na exibição de delta. Esta coluna pode conter o tipo de alteração Modify_Attribute para a alteração de nível de atributo ou um tipo de alteração Adicionar, Modificar ou Excluir, para um tipo de alteração de nível de objeto. Se for algo diferente do valor padrão de Adicionar, Modificar ou Excluir, você poderá definir esses valores usando essa opção.

Export

O conector SQL genérico oferece suporte à exportação usando quatro métodos:

• Tabela
• Visualizar
• Procedimento armazenado
• Consulta SQL

Tabela/Exibição
se você escolher a opção de Tabela/Exibição, o conector gerará as respectivas consultas para fazer a Exportação.

Procedimentos armazenados

Se você escolher a opção de Procedimento Armazenado, a Exportação exigirá três procedimentos armazenados diferentes para executar operações Inserir/Atualizar/Excluir.

• Adicionar nome do SP: esse SP será executado se houver objetos para inserção no conector, na respectiva tabela.
• Atualizar nome do SP: esse SP será executado se houver objetos para atualização no conector, na respectiva tabela.
• Excluir nome do SP: esse SP será executado se houver objetos para exclusão no conector, na respectiva tabela.
• Atributo selecionado do esquema usado como um valor de parâmetro para o procedimento armazenado. Por exemplo, @EmployeeName: INPUT: EmployeeName (NomeFuncionário está selecionado no esquema do conector, e o conector substitui o respectivo valor durante a exportação)
• Para executar o procedimento armazenado com parâmetros, forneça os parâmetros no formato [Name]:[Direction]:[Value] . Forneça cada parâmetro em uma linha separada (use Ctrl + Enter para obter uma nova linha).

Consulta SQL

Se você escolher a opção de consulta SQL, a Exportação exigirá três consultas diferentes para executar operações Inserir/Atualizar/Excluir.

• Consulta Insert: essa consulta será executada se houver objetos para inserção no conector, na respectiva tabela.
• Consulta Update: esta consulta será executada se houver objetos para atualização no conector, na respectiva tabela.
• Consulta Delete: esta consulta será executada se houver objetos para exclusão no conector, na respectiva tabela.
• Atributo selecionado do esquema, usado como um valor de parâmetro na consulta, por exemplo, Insert into Employee (ID, Name) Values (@ID, @EmployeeName)

Importante

CRLF ou caractere de nova linha serve como um separador entre várias instruções.

Exemplo de consulta SQL de atualização em várias etapas - o caractere de nova linha é usado para separar instruções SQL:

update Employee set jobTitle=@JOBTITLE where BusinessEntityID=@BUSINESSENTITYID
insert into ChangeLog VALUES (@BUSINESSENTITYID)

Solução de problemas

• Para saber mais sobre como habilitar o registro em log para solucionar problemas do conector, confira How to Enable ETW Tracing for Connectors.