Syncing Active Directory (AD) users with a SQL database is a common task many IT administrators encounter. In this article, we're going to dive in and see how to make this happen using a PowerShell script.
Contents of this article

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.

This returns all of the employee records.

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:

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.

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

Join the 4sysops PowerShell group!

Your question was not answered? Ask in the forum!

0
Share
1 Comment
  1. Gerardo 2 weeks ago

    Great article Adam, do you have info regarding synchronization between AD and Oracle DB without an IdM solution?

     

    Thank you,

    Gerardo Comerci

    0

Leave a reply

Your email address will not be published. Required fields are marked *

*

© 4sysops 2006 - 2020

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account