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
