Bringing Active Directory Information to SQL Server Database

Many comments praise the use of adding a linked server to AD and creating a view to capture user information.  There is a 1000 record limit, so that presents an obstacle to work around.  I struggled with permissions and never got a linked server to AD to work.  I’m not a Domain Admin and have limited exposure to AD in general.  Links to those methods are below.

Using Power shellflat filesSSIS package and Agent Job I created system to capture AD information on an hourly basis.  I experimented with the Power Shell Active Directory module a lot.  Having never had access to AD, it was a whole new world learning the structure and query syntax.  I made scripts for user group membership, computers  and here is the script to capture user information.

Import-module ActiveDirectory

#————————————————————-

# User details list

#————————————————————-
$cr = “`r” #Carriage Return
$tq = “`”” #Text Qualifier
#Column Headings
“{0}{1}{2}{3}{4}{5}{6}{7}{8}{9}{10}{11}{12}{13}{14}{15}{16}{17}{18}{19}{20}{21}{22}{23}{24}{25}{26}{27}{28}{29}{30}{31}”-f`

(“AccountExpirationDate”+”|”),`
($tq+”AccountExpires”+$tq+”|”),`
($tq+”assistant”+$tq+”|”),`
($tq+”Deleted”+$tq+”|”),`
($tq+”Department”+$tq+”|”),`
($tq+”DistinguishedName”+$tq+”|”),`
($tq+”EmailAddress”+$tq+”|”),`
($tq+”EmployeeNumber”+$tq+”|”),`
($tq+”Enabled”+$tq+”|”),`
($tq+”extensionAttribute4″+$tq+”|”),`
($tq+”extensionAttribute5″+$tq+”|”),`
($tq+”extensionAttribute6″+$tq+”|”),`
($tq+”extensionAttribute7″+$tq+”|”),`
($tq+”extensionAttribute9″+$tq+”|”),`
($tq+”extensionAttribute11″+$tq+”|”),`
($tq+”extensionAttribute12″+$tq+”|”),`
($tq+”extensionAttribute13″+$tq+”|”),`
($tq+”extensionAttribute15″+$tq+”|”),`
($tq+”GivenName”+$tq+”|”),`
($tq+”HomePhone”+$tq+”|”),`
($tq+”Initials”+$tq+”|”),`
($tq+”LastLogonDate”+$tq+”|”),`
($tq+”Manager”+$tq+”|”),`
($tq+”MobilePhone”+$tq+”|”),`
($tq+”Modified”+$tq+”|”),`
($tq+”Name”+$tq+”|”),`
($tq+”Office”+$tq+”|”),`
($tq+”OfficePhone”+$tq+”|”),`
($tq+”SamAccountName”+$tq+”|”),`
($tq+”Surname”+$tq+”|”),`
($tq+”Title”+$tq),`
$cr|
Out-File -Width 2147483647 -Encoding ASCII -FilePath \\LG0123SQL\Process\Spool\AdUser.txt
#Data Rows
Get-ADUser -Filter * -Properties * |
ForEach-object {
$AccountExpirationDate = $_.AccountExpirationDate
$AccountExpires = $_.AccountExpires
$assistant = $_.assistant
$Deleted = $_.Deleted
$Department = $_.Department
$DistinguishedName = $_.DistinguishedName
$EmailAddress = $_.EmailAddress
$EmployeeNumber = $_.EmployeeNumber
$Enabled = $_.Enabled
$extensionAttribute4 = $_.extensionAttribute4
$extensionAttribute5 = $_.extensionAttribute5
$extensionAttribute6 = $_.extensionAttribute6
$extensionAttribute7 = $_.extensionAttribute7
$extensionAttribute9 = $_.extensionAttribute9
$extensionAttribute11 = $_.extensionAttribute11
$extensionAttribute12 = $_.extensionAttribute12
$extensionAttribute13 = $_.extensionAttribute13
$extensionAttribute15 = $_.extensionAttribute15
$GivenName = $_.GivenName
$HomePhone = $_.HomePhone
$Initials = $_.Initials
$LastLogonDate = $_.LastLogonDate
$Manager = $_.Manager
$MobilePhone = $_.MobilePhone
$Modified = $_.Modified
$Name = $_.Name
$Office = $_.Office
$OfficePhone = $_.OfficePhone
$SamAccountName = $_.SamAccountName
$Surname = $_.Surname
$Title = $_.Title
“{0}{1}{2}{3}{4}{5}{6}{7}{8}{9}{10}{11}{12}{13}{14}{15}{16}{17}{18}{19}{20}{21}{22}{23}{24}{25}{26}{27}{28}{29}{30}{31}”-f`
([string]$_.AccountExpirationDate+”|”),`
($tq+[string]$_.AccountExpires+$tq+”|”),`
($tq+[string]$_.assistant+$tq+”|”),`
($tq+[string]$_.Deleted+$tq+”|”),`
($tq+[string]$_.Department+$tq+”|”),`
($tq+[string]$_.DistinguishedName+$tq+”|”),`
($tq+[string]$_.EmailAddress+$tq+”|”),`
($tq+[string]$_.EmployeeNumber+$tq+”|”),`
($tq+[string]$_.Enabled+$tq+”|”),`
($tq+[string]$_.extensionAttribute4+$tq+”|”),`
($tq+[string]$_.extensionAttribute5+$tq+”|”),`
($tq+[string]$_.extensionAttribute6+$tq+”|”),`
($tq+[string]$_.extensionAttribute7+$tq+”|”),`
($tq+[string]$_.extensionAttribute9+$tq+”|”),`
($tq+[string]$_.extensionAttribute11+$tq+”|”),`
($tq+[string]$_.extensionAttribute12+$tq+”|”),`
($tq+[string]$_.extensionAttribute13+$tq+”|”),`
($tq+[string]$_.extensionAttribute15+$tq+”|”),`
($tq+[string]$_.GivenName+$tq+”|”),`
($tq+[string]$_.HomePhone+$tq+”|”),`
($tq+[string]$_.Initials+$tq+”|”),`
($tq+[string]$_.LastLogonDate+$tq+”|”),`
($tq+[string]$_.Manager+$tq+”|”),`
($tq+[string]$_.MobilePhone+$tq+”|”),`
($tq+[string]$_.Modified+$tq+”|”),`
($tq+[string]$_.Name+$tq+”|”),`
($tq+[string]$_.Office+$tq+”|”),`
($tq+[string]$_.OfficePhone+$tq+”|”),`
($tq+[string]$_.SamAccountName+$tq+”|”),`
($tq+[string]$_.Surname+$tq+”|”),`
($tq+[string]$_.Title+$tq),`
$cr
} |
Out-File -Width 2147483647 -Encoding ASCII -FilePath \\LG0123SQL\Process\Spool\AdUser.txt -append # output

The output does need to be a UNC path. Once the flat files are created then SSIS and T-sql can take it from there to update the database of users and computers.  The project is an inventory of users, computers and the software installed on their computers.  The firm users have some expensive software and the licensing must be closely tracked.

Here are the links to other discussion on pulling in AD to SQL Server.

http://codebetter.com/brendantompkins/2003/12/19/create-a-sql-server-view-of-your-ad-users

http://dbatasks.blogspot.co.uk/2013/03/getting-data-out-of-active-directory.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: