Criar contas no seu Active Directory automaticamente de uma lista do SQL, usando o PowerShell – Versão mais detalhada

Publicado por Luiz Carlos Gabriel Pacheco Castello Branco em

Vamos demonstrar como automatizar o processo de criação de conta no AD a partir de informações de um banco de dados MS SQL (do banco do seu RH, por exemplo), podendo diminuir a atividade da equipe de suporte, agilizar os processos para a empresa, sem aumentar custos e até evitar a necessidade de um gerenciador de identidade. Caso ache esse post difícil de entender, leia primeiro nosso post Criar contas no seu Active Directory automaticamente de uma lista do SQL, usando o PowerShell – Versão mais simples

Antes de mais nada, para executar todo esse fluxo serão necessários várias informações do seu ambiente. Por não termos essas informações, deixaremos algumas partes em cinza de forma genérica, para que você altere conforme a sua realidade.

Para executar o que propomos nesse post, definimos 5 grandes etapas macro que precisam ser executadas.

Explicaremos cada uma delas:

Etapa 1 – Elaborar o fluxo

Para executar a sincronização de dados entre duas bases é necessário um valor único que tenha tanto no cadastro do nosso banco de RH, quanto no AD. No nosso caso vamos simular que é um campo matrícula (mas poderia ser CPF, ou outro campo), essa informação precisa estar cadastrada em algum campo do AD. Aqui iremos considerar que este campo está cadastrado no campo Pager do usuário no AD (altere para a sua realidade);

Tendo a premissa de qual será o campo único entre as duas bases, o primeiro passo é gerar a lista de usuários do AD, com o campo Pager :

Get-ADUser -filter * -Properties pager | ft name,samaccountname,pager

Agora você deve analisar (e corrigir):

  • Existem usuários SEM matricula? Para ver isso, execute o comando (em cada domínio, se possuir mais de um):
  • Existem inconsistências (erros de cadastro, fora de padrão, etc.)? Será necessário corrigir todas as contas e garantir a integridade/qualidade dos dados cadastrados no AD, antes de começar os procedimentos;

Os blocos de scripts a partir daqui devem ser executados na mesma sessão do PowerShell ISE, pois uma variável de saída em uma etapa pode ser necessária em outra parte do script.

Etapa 2 – Obter os dados do SQL

Para executar esse procedimento, você precisará dos dados do dados do banco (lembre-se de criar um usuário que tenha permissão apenas de leitura e apenas nos dados necessários) e do comando (a sintaxe do select) que será executado. Tendo esses dados, utilize esse script (altere as partes em cinza conforme sua realidade):

# VARIAVEIS (ALTERE CONFORME SUA REALIDADE)
$BD_Server = “NOME_DO_SEU_SERVIDOR“
$BD_Instance = “”
$BD_User = “NOME_DO_USUARIO“
$BD_Password = “SENHA_DO_USUARIO“
$BD_Database = “NOME_DA_DATABASE“
$BD_Query = “select * from Tabela_Func where HABILITADO LIKE ‘S%’ OR HABILITADO LIKE ‘Y%’“
# FUNCAO OBTER OS DADOS
function GenericSqlQuery ($SQLSERVER, $SQLINSTANCE, $SQLUSER, $SQLPASS, $SQLDATABASE, $SQLQUERY) {
    If ($SQLINSTANCE -eq “”)
    {
        $connectionString = “Server=”+$SQLSERVER+”;uid=”+$SQLUSER+”; pwd=”+$SQLPASS+”;Database=”+$SQLDATABASE+”;Integrated Security=False;”
    }
    Else
    {
        $connectionString = “Server=”+$SQLSERVER+”\”+$SQLINSTANCE+”;uid=”+$SQLUSER+”; pwd=”+$SQLPASS+”;Database=”+$SQLDATABASE+”;Integrated Security=False;”
    }
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString
    $connection.Open()
        $command = $connection.CreateCommand()
        $command.CommandText = $SQLQUERY
        $result = $command.ExecuteReader()
        $Result_SqlQuery = new-object “System.Data.DataTable”
        $Result_SqlQuery.Load($result)
    $connection.Close()
    return $Result_SqlQuery
}
# EXECUTANDO A QUERY NO BANCO
$table = GenericSqlQuery $BD_Server $BD_Instance $BD_User $BD_Password $BD_Database $BD_Query

Confirme se funcionou corretamente, executando o comando (e verificando a saída da variável):

$table

Caso sua query de banco não tenha usado trim (para apagar os espaços em branco, no final de cada informação). Você pode verificar se suas variáveis estão com espaço (pode copiar para a área de transferência com o comando $table | fl | clip, abrir no notepad e verificar, por exemplo)

Caso precise, use o seguinte código para fazer o trim do seu array:

# PADRONIZANDO OS DADOS (TRIM)
$KeysArray = @()
$columns = ($table | Get-Member -MemberType NoteProperty).Name
$table | ForEach-Object {
    $linha = $_
    $KeyData = $null
    $KeyData = New-Object PSObject
    $columns | ForEach-Object {
        $Name = $_
        $KeyData | Add-Member -MemberType NoteProperty -Name $Name -Value         $linha.$Name.Trim(” “)
    }
    $KeysArray += $KeyData
}
$table = $KeysArray

Por fim, confira se sua lista possuí cadastros repetidos (dependendo do sistema o cadastro de um funcionário pode se repetir quando ele sai e volta para a empresa, por exemplo). Se for esse o caso, filtre os registros únicos baseado no valor que está usando padrão entre o banco e o AD (em nosso caso, definimos que seria matrícula) com o comando:

$table = $table | select -Unique Matricula

Etapa 3 – Obter os dados do AD

Agora vamos para os dados do Active Directory. Vamos considerar aqui que você possuí 2 domínios e que cadastrará ambos, se não for o caso, altere a linha $ActiveDirectories com a quantidade de domínios (inclua o nome do controlador de domínio) que você possuí (mais ou menos):

# VARIAVEIS (ALTERE CONFORME SUA REALIDADE)
# CASO TENHA MAIS DE UM DOMINIO, O PRIMEIRO DA LISTA SERA O DOMINIO PADRAO ONDE OS USUARIOS SERAO CRIADOS
$ActiveDirectories = “1_DC.1_DOMINIO.com.br”,”2_DC.2_DOMINIO.com.br”,”3_DC.3_DOMINIO.com.br”
#INICIO DO SCRIPT
If ($ActiveDirectories.count -gt 1)
{
    $Default_ActiveDirectory = $ActiveDirectories[0]
}
Else
{
    $Default_ActiveDirectory = $ActiveDirectories
}
$lista_ad = $null
$ActiveDirectories | Foreach-object {
    $lista = $null
    $server = $_
    $domain = $server.Replace($server.split(“.”)[0]+”.”,””).tolower()
    $dn_domain = “dc=”+$domain.Replace(“.”,”,dc=”)
    Try
    {
        $lista = Get-ADUser -filter {pager -like “*”} -server $server -searchbase $dn_domain -Properties pager | select samaccountname,pager
    }
    catch
    {
        Write-Host “Falhou” -ForegroundColor Red
        Break
    }
    $lista | Add-Member -membertype noteproperty -name “DC” -value $server
    $lista_ad += $lista
}
Write-Host “Concluido” -ForegroundColor Green

Para confirmar se funcionou corretamente, execute o comando (e veja se retorna todos os objetos desejados):

$lista_ad

Etapa 4 – Comparar os dados

Para continuar funcionando a partir daqui, vamos considerar os campos em comum nas duas consultas (em nosso exemplo a matrícula) existem e estão iguais em ambos (não existem diferenças de forma de escrita, etc.). Lembre-se que você precisará ajustar, conforme a sua realidade.

Para criar uma lista apenas com as informações que existem no banco e não existem no AD (utilizando como referência nosso Post Comparando membros de dois arrays no PowerShell), use o seguinte código:

$novos_usuarios = $table | Where {$lista_ad -NotContains $_.pager}

Verifique se a lista contem apenas novas contas a serem criadas. Para isso, verifique os dados existentes nessa lista com o comando:

$novos_usuarios

Etapa 5 – Criar no AD

Para a próxima parte é importante que você verifique os nomes dos campos (das colunas) que estavam no banco. Vamos colocar como exemplo que os nomes são Nome e Matricula, sendo diferente altere as partes em cinza abaixo:

$senha_padrao = “DEFINA_UMA_SENHA“
$default_domain = $Default_ActiveDirectory.Replace($server.split(“.”)[0]+”.”,””).tolower()
$default_dn_domain = “dc=”+$domain.Replace(“.”,”,dc=”)
$novos_usuarios | Foreach-object {
    $BD_Nome = $_.Nome
    $BD_Matricula = $_.Matricula
    New-ADUser -server $Default_ActiveDirectory -Path “CN=Users,$default_dn_domain” -Name $BD_Nome -sAMAccountName $BD_Matricula -UserPrincipalName “$BD_Matricula@$default_domain” -EmailAddress “$BD_Matricula@$default_domain” -OtherAttributes @{pager=”$BD_Matricula”} -AccountPassword (ConvertTo-SecureString -AsPlainText “$senha_padrao” -Force) -Enabled $false
    write-host “usuario $BD_Nome criado”
}

Executando todos os códigos acima (testando com calma cada parte até que entenda o que você está fazendo e tendo certeza de todo o fluxo) você acabou de criar um script para criação automática dos usuários no AD. Podendo criar uma rotina/agendamento na frequência que deseja.

Lembrando que, por uma questão de segurança, em nosso exemplo criamos as contas desabilitadas.

Opcional: Possíveis evoluções ao código

Como falamos, deixamos esse Post o mais genérico possível que lhe permita entender e evoluir conforme sua necessidade. Porém sugerimos as seguintes melhorias:

  • Enviar um e-mail ao gestor da pessoa (obtendo o usuário com maior cargo da mesma OU, por exemplo);
  • Você pode utilizar uma senha randômica e segura, conforme descrevemos em nosso post um Script para gerar uma senha randômica no PowerShell e enviar essa nova senha ao Gestor da área (já descrito como uma evolução, acima);
  • Organizar o nome:
    • Colocar em maiúsculo e minúsculo;
    • Separar nome e sobrenome;
  • Definir o melhor login;
  • Obter (do SQL) o cargo e:
    • Informar (no AD) durante a criação;
    • Criar um padrão dos grupos de acesso, baseado nos cargos e usar esse ACL na criação;
  • Criar o usuário na OU correta (por exemplo, obtendo o centro de custo do SQL, adicionando essa informação na descrição das OUs e consultar esse valor para definir a OU);
  • Associar as licenças do Office 365.

Podemos lhe ajudar em qualquer uma dessas evoluções (essas, inclusive, já fizemos algumas vezes) ou em outras. Se for esse o caso, sempre deixamos nosso contato no final do post.

Fontes/Referências

NVLAN – Comparando membros de dois arrays no PowerShell
NVLAN – Criar contas no seu Active Directory automaticamente de uma lista do SQL, usando o PowerShell – Versão mais simples
NVLAN – Script para gerar uma senha randômica no PowerShell
NVLAN – Script Powershell para sugerir login de rede de um novo usuário

Mais Informações

Esperamos ter ajudado da melhor forma possível e estaremos sempre a disposição para mais informações.

Se você tem interesse em algum assunto específico, tem alguma dúvida que precisa de ajuda, ou quer sugerir um post, entre em contato conosco pelo e-mail equipe@nvlan.com.br.

NVLAN - Consultoria
Categorias: Microsoft Active Directory
Tags:

Posts relacionados

Microsoft Active Directory POSTs

Configurações e Práticas para seu Active Directory

O Active Directory é uma ferramenta essencial em ambientes de uma rede corporativa, mas em muitos casos este ambiente é instalado e feito com poucas configurações iniciais. Neste post vamos sugerir algumas práticas para se Leia mais…

Microsoft Active Directory

Obter informações de Hardware dos Computadores que estão em uma OU, com o PowerShell

Vamos mostrar o código para realizar essa consulta. Abra o PowerShell com uma conta que tenha permissão de administrador nos desktops e execute o seguinte: Fontes/Referências NVLAN – Como obter o Distinguished Name (DN) no Leia mais…

Microsoft Active Directory

Como habilitar a lixeira do AD

É possível habilitar o Active Directory Recycle bin para restaurar objetos deletados. Abaixo os passos para restaurar uma conta e habilitar a lixeira do AD: Recuperando uma conta sem a lixeira do AD Caso precise Leia mais…