Scripting the Active Directory

By Henning Eiben in Windows 0 Comment

Background

Well, sometimes the built-in MMC snap-in doesn’t do it all. In this case you need to do more than just add another user. Of course there a bunch of different ways to access your Active Directory. You most certainly can write a nifty c# programm, which could do all kinds of operations. But a lot of times you will end up write some little VBScript, just because it’s so darmn simple.

The Story

For the purpose of illustrating the usage of Active Directory scripting I have a little sample setup. Assume you have a database containing employee data and you want to sync this data with your Active Directory instance. You could further use this data in Active Directory to enable Active Directory aware programs (like SharePoint 🙂 ) to access this data.

The Basics

OK; to get started I will show a little script, which access the Active Directory and does some mass-updating off user-accounts. For example say you want to add employee-numbers to the users accounts.

First of all, you need to connect to the directory. For this you need to know the root path of your Active Directory, or as a convenience you might as well use LDAP://RootDSE instead. OK, let’s get connected to our Active Directory; the code below assumes you have a recordset called rstEmployees which holds our employee data. So we iterate over the recordset:

Sub UpdateActiveDirectoryEntries
        On Error Resume Next
        Dim objRoot, objUser
          Dim intEmployeeCount
        Set objRoot = GetObject(strRootADPath)
        WScript.Echo "Processing records ..."
        rstEmployees.MoveFirst
        While Not rstEmployees.EOF
                WScript.Echo "processing:" & rstEmployees.Fields("EmployeeName") & " (" & rstEmployees.Fields("EmployeeNumber") & ")"
                  Set objUser = GetObject("LDAP://CN=" & rstEmployees.Fields("EmployeeName") & "," & strAllEmployeeADPath)
                  If objUser Is Nothing Then
        WScript.Echo "User '" & rstEmployees.Fields("EmployeeName") & "' could not be found!"
                  Else
                        ShowUser objUser
                          WScript.Echo "Writing ..."
                          objUser.Put "EmployeeID", rstEmployees.Fields("EmployeeNumber").Value
                          objUser.Put "EmployeeNumber", rstEmployees.Fields("EmployeeNumber").Value
                          ' objUser.PutEx ADS_PROPERTY_CLEAR, "EmployeeNumber",  vbNullString
                          objUser.SetInfo
                          ShowUser objUser
                  End If
                  Set objUser = Nothing
                  rstEmployees.MoveNext
          Wend
  End Sub

Validating eMail-address via Regular Expressions

By Henning Eiben in Programming 0 Comment

Regular expressions are supported via the RegExp object by VBScript since version 5 of the scripting engine:

 Function RegExpTest(sEmail)
   RegExpTest = false
   Dim regEx, retVal
   Set regEx = New RegExp
   ' Create regular expression:
   regEx.Pattern ="^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
   ' Set pattern:
   regEx.IgnoreCase = true
   ' Set case sensitivity.
   retVal = regEx.Test(sEmail)
   ' Execute the search test.
   If not retVal Then
     exit function
   End If
   RegExpTest = true
 End Function

Import von Binär-Daten in den MS SQL-Server mit ADO

By Henning Eiben in Programming 0 Comment

Der hier beschriebene Source-Code bezieht sich auf die Verwendung von VBScript. Die Verwendung in VB oder VBA ist analog.

Um Dateien als Binary importieren zu können muss zunächst eine Stream-Objekt erstellt werden, welches die zu importierende Datei repräsentiert:

 Dim strFile
 strFile = "C:\Temp\Test.jpg"
 Dim objStream
 Set objStream = CreateObject("ADODB.Stream")
 objStream.Open
 objStream.Type = adTypeBinary
 objStream.LoadFromFile(strFile)

Anschließend kann eine normale ADO Verbindung mit dem SQL-Server aufgebaut werden. In diesem Fall wird mit Windows-Authentifizierung eine Verbindung mit der Datenbank Test gemacht:

 Dim cnn, rst
 Set cnn = CreateObject("ADODB.Connection")
 cnn.Open "Provider=SQLOLEDB.1; Data Source=(local); " & "Integrated Security=SSPI;Initial Catalog=Test;"
 Set rst = CreateObject("ADODB.Recordset")

In dieser Datenbank ist eine Tabelle Table1 vorhanden, die über zwei Spalten verfügt: FileName und Bild, wobei Bild vom Datentyp image ist. Der Import wird in eine Transaktion gekapselt:

 cnn.BeginTrans
 rst.Open "Table1", cnn, adOpenForwardOnly, adLockOptimistic
 rst.AddNew
 rst.Fields("FileName").Value = strFile
 rst.Fields("Bild").AppendChunk(objStream.Read)
 rst.Update
 rst.close
 cnn.CommitTrans

Schließlich werden noch alle Objekte wieder freigegeben:

 ' Clean-Up
 objStream.Close
 cnn.Close
 Set rst = Nothing
 Set objStream = Nothing
 Set cnn = Nothing

Das komplette Skript kann auch als VBS-Datei gedownloaded werden.

Link: How to load a Binary File into an Oracle BLOB with ADO