Technical Article

Extract More Than 1000 Active Directory Accounts

,

This is a simple method to load a SQL database with active directory account information without running into the 1000 record limit.  You will need to change the SERVERNAME, PORT, DATABASENAME, USERID, AND PASSWORD values in the connection strings to your own values.  You will also need to change the LDAP connection string values ('LDAP://SERVERNAME/ou=OUName,dc=host,dc=domain,dc=tld') to your own values.

-- Create a table to hold the OU paths.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ OU](
[ADsPath] [ntext] NULL,
[rec_id] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


-- Set up an OLEDB connection to Microsoft Directory 
-- Services provider and query LDAP to load the OU table.  
-- Alternately, enter the OU values into the table manually 
-- in the form

-- LDAP://servername/OU=ou,DC=host,DC=domain,DC=tld 

-- to extract from specific OUs

SELECT ADsPath FROM 'LDAP://SERVERNAME/ou=OUName,dc=host,dc=domain,dc=tld' WHERE objectCategory='organizationalUnit'


-- Create a table to hold the accounts.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Accounts](
[manager] [varchar](512) NULL,
[company] [varchar](512) NULL,
[department] [varchar](512) NULL,
[title] [varchar](512) NULL,
[facsimileTelephoneNumber] [varchar](512) NULL,
[info] [varchar](512) NULL,
[ipPhone] [varchar](512) NULL,
[mobile] [varchar](512) NULL,
[pager] [varchar](512) NULL,
[homePhone] [varchar](512) NULL,
[HomeDrive] [varchar](512) NULL,
[HomeDirectory] [varchar](512) NULL,
[ScriptPath] [varchar](512) NULL,
[ProfilePath] [varchar](512) NULL,
[userWorkstations] [varchar](512) NULL,
[samAccountName] [varchar](512) NULL,
[userPrincipalName] [varchar](512) NULL,
[c] [varchar](512) NULL,
[postalCode] [varchar](512) NULL,
[st] [varchar](512) NULL,
[l] [varchar](512) NULL,
[streetAddress] [varchar](512) NULL,
[mail] [varchar](512) NULL,
[telephoneNumber] [varchar](512) NULL,
[physicalDeliveryOfficeName] [varchar](512) NULL,
[displayName] [varchar](512) NULL,
[sn] [varchar](512) NULL,
[initials] [varchar](512) NULL,
[givenName] [varchar](512) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


' Run a vbscript to load the Accounts table.

Option Explicit

Dim adoCommand, _
adoConnection, _
strBase, _
strFilter, _
strAttributes

Dim objRootDSE, _
strDNSDomain, _
strQuery, _
adoRecordset

Dim givenName, _
initials, _
sn, _
displayName, _
physicalDeliveryOfficeName, _
telephoneNumber, _
mail, _
streetAddress, _
l, _
st, _
postalCode, _
c, _
userPrincipalName, _
samAccountName, _
userWorkstations, _
ProfilePath, _
ScriptPath, _
HomeDirectory, _
HomeDrive, _
homePhone, _
pager, _
mobile, _
ipPhone, _
info, _
facsimileTelephoneNumber, _
title, _
department, _
company, _
manager

Dim sqlCommand, _
sqlConnection 

Dim  adPath, _
adSql, _
adConnection

' Setup ADO objects.

Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")

Set sqlConnection = CreateObject("ADODB.Connection")
Set sqlCommand = CreateObject("ADODB.Command")

Set adPath = CreateObject("ADODB.Recordset")
Set adConnection = CreateObject("ADODB.Connection")
Set adSql= CreateObject("ADODB.Command")

'OU=OUName,DC=host,DC=domain,DC=tld

adSql.CommandText = "select adspath from OU"
adConnection.Open "Driver={SQL Server};server=SERVERNAME,PORT;" & _
"database=DATABASENAME_In;uid=USERID;pwd=PASSWORD;"
adSql.ActiveConnection = adConnection
Set adPath = adSql.Execute

Do Until adpath.EOF
    adoConnection.Provider = "ADsDSOObject"
    adoConnection.Open "Active Directory Provider"
    adoCommand.ActiveConnection = adoConnection

    ' Search entire Active Directory domain.

    strDNSDomain = adPath("ADsPath")
    strBase = "<"& strDNSDomain &">"

    ' Filter on user objects.

    strFilter = "(&(objectClass=user))"

    ' Comma delimited list of attribute values to retrieve.

    strAttributes = "givenName," & _
    "initials," & _
    "sn," & _
    "displayName," & _
    "physicalDeliveryOfficeName," & _
    "telephoneNumber," & _
    "mail," & _
    "streetAddress," & _
    "l," & _
    "st," & _
    "postalCode," & _
    "c," & _
    "userPrincipalName," & _
    "samAccountName," & _
    "userWorkstations," & _
    "ProfilePath," & _
    "ScriptPath," & _
    "HomeDirectory," & _
    "HomeDrive," & _
    "homePhone," & _
    "pager," & _
    "mobile," & _
    "ipPhone," & _
    "info," & _
    "facsimileTelephoneNumber," & _
    "title," & _
    "department," & _
    "company," & _
    "manager,"

    ' Construct the LDAP syntax query.

    strQuery = strBase & ";" & strFilter & ";" & strAttributes

    adoCommand.CommandText = strQuery
    adoCommand.Properties("Page Size") = 1000
    adoCommand.Properties("Timeout") = 30
    adoCommand.Properties("Cache Results") = False

    ' Run the query.

    Set adoRecordset = adoCommand.Execute
  
    ' Enumerate the resulting recordset.

    Do Until adoRecordset.EOF

        ' Retrieve values and display.

        givenName= adoRecordset.Fields("givenName").Value
        initials= adoRecordset.Fields("initials").Value
        sn=adoRecordset.Fields("sn").Value
        displayName= adoRecordset.Fields("displayname").Value
        physicalDeliveryOfficeName = adoRecordset.Fields("physicalDeliveryofficename").Value
        telephoneNumber= adoRecordset.Fields("telephonenumber").Value
        mail=adoRecordset.Fields("mail").Value
        streetAddress=adoRecordset.Fields("streetaddress").Value
        l=adoRecordset.Fields("l").Value
        st=adoRecordset.Fields("st").Value
        postalCode=adoRecordset.Fields("postalcode").Value
        c=adoRecordset.Fields("c").Value
        userPrincipalName=adoRecordset.Fields("userprincipalname").Value
        samAccountName=adoRecordset.Fields("samAccountName").Value
        userWorkstations=adoRecordset.Fields("userworkstations").Value
        ProfilePath=adoRecordset.Fields("profilepath").Value
        ScriptPath=adoRecordset.Fields("Scriptpath").Value
        HomeDirectory=adoRecordset.Fields("Homedirectory").Value
        HomeDrive=adoRecordset.Fields("homedrive").Value
        homePhone=adoRecordset.Fields("homephone").Value
        pager=adoRecordset.Fields("pager").Value
        mobile=adoRecordset.Fields("mobile").Value
        ipPhone=adoRecordset.Fields("ipphone").Value
        info=adoRecordset.Fields("Info").Value
        facsimileTelephoneNumber=adoRecordset.Fields("facsimileTelephonenumber").Value
        title=adoRecordset.Fields("title").Value
        department=adoRecordset.Fields("department").Value
        company=adoRecordset.Fields("company").Value
        manager=adoRecordset.Fields("manager").Value
        
        SQLConnection.Open "Driver={SQL Server};server=SERVERNAME,PORT;" & _
        "database=DATABASENAME_In;uid=USERID;pwd=PASSWORD;"
        Set sqlCommand.ActiveConnection = sqlConnection

        SQLCommand.CommandText = "SET QUOTED_IDENTIFIER OFF " & _
        "INSERT INTO " & _
        "Accounts " & _
        "(samAccountName," & _
        "givenName," & _
        "initials," & _
        "sn," & _
        "displayName," & _
        "physicalDeliveryOfficeName," & _
        "telephoneNumber," & _
        "mail," & _
        "streetAddress," & _
        "l," & _
        "st," & _
        "postalCode," & _
        "c," & _
        "userPrincipalName," & _
        "userWorkstations," & _
        "ProfilePath," & _
        "ScriptPath," & _
        "HomeDirectory," & _
        "HomeDrive," & _
        "homePhone," & _
        "pager," & _
        "mobile," & _
        "ipPhone," & _
        "info," & _
        "facsimileTelephoneNumber," & _
        "title," & _
        "department," & _
        "company," & _
        "manager," & _
        "VALUES(" & _
        chr(34) & sAMAccountName & chr(34) & "," & _
        chr(34) & givenName & chr(34) & "," & _
        chr(34) & initials & chr(34) & "," & _
        chr(34) & sn & chr(34) & "," & _
        chr(34) & displayName & chr(34) & "," & _
        chr(34) & physicalDeliveryOfficeName & chr(34) & "," & _
        chr(34) & telephoneNumber & chr(34) & "," & _
        chr(34) & mail & chr(34) & "," & _
        chr(34) & streetAddress & chr(34) & "," & _
        chr(34) & l & chr(34) & "," & _
        chr(34) & st & chr(34) & "," & _
        chr(34) & postalCode & chr(34) & "," & _
        chr(34) & c & chr(34) & "," & _
        chr(34) & userPrincipalName & chr(34) & "," & _
        chr(34) & userWorkstations & chr(34) & "," & _
        chr(34) & ProfilePath & chr(34) & "," & _
        chr(34) & ScriptPath & chr(34) & "," & _
        chr(34) & HomeDirectory & chr(34) & "," & _
        chr(34) & HomeDrive & chr(34) & "," & _
        chr(34) & homePhone & chr(34) & "," & _
        chr(34) & pager & chr(34) & "," & _
        chr(34) & mobile & chr(34) & "," & _
        chr(34) & ipPhone & chr(34) & "," & _
        chr(34) & info & chr(34) &"," & _
        chr(34) & facsimileTelephoneNumber & chr(34) & "," & _
        chr(34) & title & chr(34) & "," & _
        chr(34) & department & chr(34) & "," & _
        chr(34) & company & chr(34) & "," & _
        chr(34) & manager & chr(34) & ")" 

        sqlCommand.CommandType = 1
        sqlCommand.Execute
        sqlConnection.Close 
        
        ' Move to the next record in the recordset.

        adoRecordset.MoveNext
        
    Loop
    
    adoRecordset.Close
    adoConnection.Close
    
   ' Move to the next record in the recordset.

    adPath.MoveNext
    
Loop 

' Clean up.

adPath.Close      
adConnection.Close

Rate

5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (2)

You rated this post out of 5. Change rating