Sync Active Directory users with a SQL database

• Author
• Recent Posts

Adam Bertram

Adam Bertram is a 20-year IT veteran, Microsoft MVP, blogger, and trainer.

Latest posts by Adam Bertram (see all)

• Create a certificate-signed RDP shortcut via Group Policy - Fri, Aug 9 2019
• Monitor web server uptime with a PowerShell script - Tue, Aug 6 2019
• How to build a PowerShell inventory script for Windows Servers - Fri, Aug 2 2019

Syncing AD users can be a big topic with lots of moving parts, so it's important to scope this down a bit. We're going to figure out how to create AD user accounts if they don't exist. We are not going to update existing user accounts, nor are we going to remove accounts that are not in our SQL database.

Every organization's SQL database with employee information is going to be different. The first step is to figure out which fields in the database table map to AD user attributes. I'll be assuming that the source SQL database has a single table called Employees consisting of the following columns:

• EmployeeID
• FirstName
• LastName
• Department
• Loc
• Phone
• Title

We'll need to pull all of the rows from this table, so we'll use the SqlServer PowerShell module available via the PowerShell Gallery (Install-Module -Name SqlServer). You will also need the ActiveDirectory module available via the Remote Server Administration Tools package from Microsoft.

Once we have downloaded and installed this, we'll then need to connect to our SQL server and query our Employees table. My SQL Server is set up with SQL authentication, so I'll pass the SQL username and password to a script I'm working on.

$employees = Invoke-Sqlcmd -ServerInstance '<SQL Server name>' -Database 'TSTestingDB' -Username '<SQL UserName>' -Password '<SQL Password>' -Query 'SELECT * FROM Employees'

This returns all of the employee records.

Employee records

Processing each employee

Once all employees are in a variable, I can then create a foreach loop to begin processing each one. You can see in the code snippet below that I'm:

• Coming up with a username for each employee based on the first initial/last name
• Checking to see if the username is present; if so, we simply return a verbose message
• If not, we then call New-AdUser and pass each property returned from each SQL row as parameter values to it

Here's the code:

foreach ($employee in $employees) {
    ## Come up with the username I'd like to create based on company policy
    $proposedUsername = '{0}{1}' -f $employee.'FirstName'.Substring(0, 1), $employee.'LastName'
    ## Check to see if the proposed username exists
    if (Get-AdUser -Filter "Name -eq '$proposedUsername'") {
        Write-Verbose -Message "The AD user [$proposedUsername] already exists."
    } else {
        ## If it does not exist, pass all of the information we have to New-AdUser
        ## This creates the user using the username we came up with above and all of the
        ## field values from the CSV file.
        $newUserParams = @{
            Name        = $proposedUsername
            Path        = "OU=$($employee.Loc),DC=yourdomain,DC=local"
            Department  = $employee.Department
            Enabled     = $true
            GivenName   = $employee.FirstName
            Surname     = $employee.LastName
            EmployeeID  = $employee.EmployeeID
            OfficePhone = $emplooyee.Phoney
        }
        New-AdUser @newUserParams
    }
}

Summary

Syncing AD users with a SQL database is similar to using CSVs, Excel spreadsheets, or other structured data formats. The trick is to ensure each property stored in the data source maps properly to an AD user's attribute. You have seen an example of this in the snippet above when passing parameters to New-Aduser.

I always prefer to teach tool-building rather than unique scripts, and this article is no exception. You will undoubtedly need to perform this process many times, so it's a great idea to build a PowerShell function to do this.

Below is an example of the function that could perform this same task with a single line! If your company policies are different, this will give you a great template to start with.

function Sync-ActiveDirectory {
    <#
        .SYNOPSIS
            Creates Active Directory groups, OUs, and users from a CSV file.
        .PARAMETER CsvFilePath
            The file path to the CSV file containing employee records.
    #>
    [OutputType('null')]
    [CmdletBinding(SupportsShouldProcess)]
    param
    (
        [Parameter(Mandatory)]
        [ValidateNotNullOrEmpty()]
        [string]$SQLDatabaseServer,

        [Parameter(Mandatory)]
        [ValidateNotNullOrEmpty()]
        [string]$SQLDatabaseName,

        [Parameter(Mandatory)]
        [ValidateNotNullOrEmpty()]
        [string]$SQLDatabaseTable,

        [Parameter(Mandatory)]
        [ValidateNotNullOrEmpty()]
        [string]$SQLUsername,

        [Parameter(Mandatory)]
        [ValidateNotNullOrEmpty()]
        [string]$SQLPassword
    )

    $ErrorActionPreference = 'Stop'

    ## Find the employees
    Write-Verbose -Message 'Finding employees...'
    $employees = Invoke-Sqlcmd -ServerInstance $SQLDatabaseServer -Database $SQLDatabaseName -Username $SQLUserName -Password $SQLPassword -Query "SELECT * FROM $SQLDatabaseTable"

    ## Create the users
    Write-Verbose -Message 'Syncing users....'
    foreach ($employee in $employees) {

        ## Check for and create the user
        $proposedUsername = '{0}{1}' -f $employee.FirstName.Substring(0, 1), $employee.LastName
        if (Get-AdUser -Filter "Name -eq '$proposedUsername'") {
            Write-Verbose -Message "The AD user [$proposedUsername] already exists."
        } else {
            $newUserParams = @{
                Name        = $proposedUsername
                Path        = "OU=$($employee.Loc),DC=techsnips,DC=local"
                Enabled     = $true
                GivenName   = $employee.FirstName
                Department  = $employee.Department
                Surname     = $employee.LastName
                EmployeeID  = $employee.EmployeeID
                OfficePhone = $employee.Phone
            }
            New-AdUser @newUserParams
        }
    }
}

After creating a function, there's now no need to remember all of that code. You'll simply need to run a single command!

Sync-ActiveDirectory -SQLDatabaseServer <DBSERVER> -SQLDatabaseName <DBName> -SQLDatabaseTable Employoees -SQLUsername <UserName> -SQLPassword <SQLPassword>