Re: Copy Active directory Users to a SQL DB table - Daily


"Richard Mueller [MVP]" <rlmueller-nospam@xxxxxxxxxxxxxxxxxxxx> wrote in
message news:e%23K5L9StHHA.1184@xxxxxxxxxxxxxxxxxxxxxxx

"Yas" <yasar1@xxxxxxxxx> wrote in message
news:1182556634.039711.251590@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On 21 Jun, 04:56, "Richard Mueller [MVP]" <rlmueller-
nos...@xxxxxxxxxxxxxxxxxxxx> wrote:
Yas wrote:

I am trying to write a script using VBScript that gets a lise of all
active users in Active Directory incl specified attributes 1,2,3..etc
(if possible also specify which users to leave out eg.users in OU x)
and then adds them to a table in a MS SQL database. Table will have
corresponding columns..name, email,samAccountName,Attrib1,Attrib2 etc

And then daily scheduled runs of the script updates the SQL table from
data in AD with any modifications made to the accounts and adds any
new users that were not there before.

Is this possible using VBScript? sorry am restricted to only using
vbscript. If so, any help or hints or ideas would be greatly
appreciated.

Yes it can be done, using one ADO connection to retrieve the information
from Active Directory and another to update the SQL Server database
table.
The example below is not tested. I assume the same field names in the
SQL
table as the attribute names in AD. The tricky part is constructing the
T-SQL statements as strings, with all the commas, spaces, and single
quotes
in the correct places. If there are errors, echo the T-SQL statements so
you
can see them. It's usually a simple syntax mistake.
===========
Option Explicit

Dim adoADCommand, adoADConnection, strBase, strFilter, strAttributes
Dim objRootDSE, strDNSDomain, strQuery, adoADRecordset
Dim strConnect, adoSQLConnection, adoSQLCommand, adoSQLRecordset
Dim strNTName, strDN, strFirst, strMiddle, strLast, strDisplay
Dim arrDesc, strItem, strDesc
Dim intCount

' Setup ADO objects for Active Directory.
Set adoADCommand = CreateObject("ADODB.Command")
Set adoADConnection = CreateObject("ADODB.Connection")
adoADConnection.Provider = "ADsDSOObject"
adoADConnection.Open "Active Directory Provider"
adoADCommand.ActiveConnection = adoADConnection

' Search entire Active Directory domain.
Set objRootDSE = GetObject("LDAP://RootDSE";)
strDNSDomain = objRootDSE.Get("defaultNamingContext")
strBase = "<LDAP://"; & strDNSDomain & ">"

' Filter on all user objects.
strFilter = "(&(objectCategory=person)(objectClass=user))"

' Comma delimited list of attribute values to retrieve.
strAttributes = "sAMAccountName,distinguishedName,givenName," _
& "initials,sn,displayName,description"

' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
adoADCommand.CommandText = strQuery
adoADCommand.Properties("Page Size") = 100
adoADCommand.Properties("Timeout") = 30
adoADCommand.Properties("Cache Results") = False

' Connection string for SQL database.
' This assumes Windows integrated authentication.
strConnect = "DRIVER=SQL Server;" _
& "Trusted_Connection=Yes;" _
& "DATABASE=MyDatabase;" _
& "SERVER=MyServer"

' Connect to SQL Server database.
Set adoSQLConnection = CreateObject("ADODB.Connection")
adoSQLConnection.ConnectionString = strConnect

' Create Recordset object for SQL Server database.
Set adoSQLRecordset = CreateObject("ADODB.Recordset")
adoSQLRecordset.ActiveConnection = adoSQLConnection

' Create Command object for SQL Server database.
Set adoSQLCommand = CreateObject("ADODB.Command")
adoSQLCommand.ActiveConnection = adoSQLConnection

' Run the query on Active Directory.
Set adoADRecordset = adoADCommand.Execute

' Enumerate the resulting recordset.
Do Until adoADRecordset.EOF
' Retrieve values for users in Active Directory.
strNTName = adoADRecordset.Fields("sAMAccountName").Value
strDN = adoADRecordset.Fields("distinguishedName").Value
strFirst = adoADRecordset.Fields("givenName").Value
strMiddle = adoADRecordset.Fields("initials").Value
strLast = adoADRecordset.Fields("sn").Value
strDisplay = adoADRecordset.Fields("displayName").Value
' The description field is multi-valued, although there
' can never be more than one value. ADO retrieves the
' value as either Null or an array of one string value.
arrDesc = adoADRecordset.Fields("description").Value
If IsNull(arrDesc) Then
strDesc = ""
Else
For Each strItem In arrDesc
strDesc = strItem
Next
End If
' Check if user exists in table ADUsers of SQL database.
adoSQLRecordset.Source = "SELECT COUNT(*) AS NumRecords " _
& "FROM dbo.ADUsers " _
& "WHERE sAMAccountName = '" & strNTName & "'"
adoSQLRecordset.Open
intCount = CInt(adoSQLRecordset.Fields("NumRecords").Value)

If (intCount = 0) Then
' User is new, add to SQL table.
adoSQLCommand.CommandText = "INSERT INTO dbo.ADUsers " _
& "(sAMAccountName, distinguishedName, givenName, " _
& "initials, sn, displayName, description) " _
& "VALUES('" & strNTName & "', '" & strDN & "', '" _
& strFirst & "', '" & strMiddle & "', '" & strLast _
& "', '" & strDisplay & "', '" & strDesc & "')
Else
' User already in SQL table, update the existing record.
adoSQLCommand.CommandText = "UPDATE dbo.ADUsers " _
& "SET distinguishedName = '" & strDN & "', " _
& "givenName = '" & strFirst & "', " _
& "initials = '" & strMiddle & "', " _
& "sn = '" & strLast & "', " _
& "displayName = '" & strDisplay & "', " _
& "description = '" & strDesc & "' " _
& "WHERE sAMAccountName = '" & strNTName & "'"
End If
' Run the T-SQL statement on the SQL database.
adoSQLCommand.Execute
adoSQLRecordset.Close

' Move to the next record in the recordset of AD users.
adoADRecordset.MoveNext
Loop

' Clean up.
adoADRecordset.Close
adoADConnection.Close
adoSQLConnection.Close


This is great, thank you! :-) I modified it a bit to suite my needs
and it works well.
I was wondering does anyone know of a way to exclude certain OUs when
searcihing Active directory? I've used UserAccountControl to exclude
disabled users but wouldlike to exclude certain OUs ie. the ones that
contain system Admins etc not being able to find anything ont he Net
that works.

Thanks

Yas


There's no way to either include or exclude OU's in the filter. This is
because there is no attribute of AD objects for the "Parent" container.
You would have to parse the distinguishedName (DN), but you can only
specify full distinguishedNames (no wildcards allowed with any DN
attributes).

The solution is to return all user objects, then in the loop where you
enumerate the recordset, parse the DN of each and exclude those in the
OU's you want to skip. Even here, the best way to tell the OU is to bind
to the user object (with the DN) and use the Parent method of the object,
which returns the DN of the parent OU/container. Unfortunately, Parent is
a property method, not an attribute, so ADO cannot retrieve it.

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

There might be other options depending on the OU structure. If, for example,
all regular users were contained in OU's nested within a custom userOU in
the root, and all admin users in a custom adminOU in the root, a search done
against userOU would necessarily exclude administrators. Our structure runs
along these lines, and the reason is to avoid delegating admin access over
adminstrative accounts too widely.

Also, if the real reason for excluding OU's containing administrative
accounts is to exclude all (and only) administrative accounts, it might be
more accurate to do as Richard suggests, but exclude only accounts that can
be identified as having admin rights. That can get a little tricky unless
you have a good understanding of what the differences are between a
non-privileged user account, and an admin account. If admin means domain
admin, this is relatively easy; if it also includes OU admins, well...

/Al


.

Relevant Pages

  • Re: Grant Administrative Access to a Domain Controller
    ... Anyone with a good understanding of AD and Windows security will easily see ways of compromising the environment. ... Do not give enhanced rights to Domain Controllers to anyone you don't trust with Domain and/or Enterprise Admins. ... Just know that minimal access can be parlayed into even more access and try as you might, you cannot secure Active Directory from people with server operator or admin or several other levels of access rights on a DC. ...
    (microsoft.public.windows.server.active_directory)
  • Re: Copy Active directory Users to a SQL DB table - Daily
    ... active users in Active Directory incl specified attributes 1,2,3..etc ... Dim strConnect, adoSQLConnection, adoSQLCommand, adoSQLRecordset ... ' Connect to SQL Server database. ... ' Create Recordset object for SQL Server database. ...
    (microsoft.public.windows.server.scripting)
  • Re: Copy Active directory Users to a SQL DB table - Daily
    ... from Active Directory and another to update the SQL Server database table. ... Dim strConnect, adoSQLConnection, adoSQLCommand, adoSQLRecordset ... Set adoSQLConnection = CreateObject ...
    (microsoft.public.windows.server.scripting)
  • Re: Unlock acct permissions
    ... > "Active Directory Design and Deployment" ... > and which Joe referenced. ... >> These are some of the best books out there right now for AD Admin level ... How do I get DSACLS to run on a specific account? ...
    (microsoft.public.win2000.active_directory)
  • Re: Copy Active directory Users to a SQL DB table - Daily
    ... And then daily scheduled runs of the script updates the SQL table from ... from Active Directory and another to update the SQL Server database table. ... Dim strConnect, adoSQLConnection, adoSQLCommand, adoSQLRecordset ...
    (microsoft.public.windows.server.scripting)