Autocreate AD accounts from MSSQL

[CyberNerd]

' 2006-06-27

' This script copies user details from MSSQL to Active directory for STUDENTS only.

' It has been written for exporting data from the CMIS MSSQL structure into

' AD that is specific to our site. It will require modification for your

' site and comes without warranty.

' 

'    This program is free software; you can redistribute it and/or modify

'    it under the terms of the GNU General Public License as published by

'    the Free Software Foundation; either version 2 of the License, or

'    (at your option) any later version.

'

'    This program is distributed in the hope that it will be useful,

'    but WITHOUT ANY WARRANTY; without even the implied warranty of

'    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

'    GNU General Public License for more details.



' Perform some connection setup.

Set objADConnection = CreateObject("ADODB.Connection")

objADConnection.Open "Provider=ADsDSOObject;"



Set objADCommand = CreateObject("ADODB.Command")

objADCommand.ActiveConnection = objADConnection



Const adOpenStatic = 3

Const adLockOptimistic = 3

Const adUseClient = 3

Const adBookMarkFirst = 1

Set objCMISConnection = CreateObject("ADODB.Connection")

Set objCMISRecordset = CreateObject("ADODB.Recordset")

objCMISConnection.Open "DSN=ADSYNC;", "adsync", "password"

objCMISRecordset.CursorLocation = adUseClient



' Read in a name or username (or part thereof) that is know to CMIS.

WScript.StdOut.Write "Enter a name or username that is known to CMIS: "

qUser = WScript.StdIn.ReadLine

WScript.Echo

If Len(qUser) = 0 Then

    WScript.Quit

End If



' Query the CMIS ADSync view.

sql = "SELECT * FROM ADSync " & _

      " WHERE (displayName LIKE '%" & qUser & "%'" & _

      "    OR  sAMAccountName LIKE '%" & qUser & "%') " & _

      "   AND leftSchool = 'N' "' & _

'      "   AND employeeType = 'STUDENT'"



' Query CMIS...

objCMISRecordset.Open sql, objCMISConnection, adOpenStatic, adLockOptimistic



' If more than one record was returned, present the user with a list to choose from.

If objCMISRecordset.RecordCount > 1 Then

    idNum = 1

    While Not objCMISRecordset.EOF

         WScript.Echo " " & idNum & ". " & objCMISRecordSet("displayName") & "; " & objCMISRecordset("sAMAccountName") & "; " & objCMISRecordset("employeeId") & "; " & objCMISRecordSet("department") & "; " & objCMISRecordset("dateOfBirth")

         objCMISRecordset.MoveNext

	 idNum = idNum + 1

    Wend



    WScript.Echo

    WScript.StdOut.Write "Choose user (1-" & (idNum-1) & ") or 'q' to quit: "

    row = WScript.StdIn.ReadLine

    If Left(row, 1) = "q" Then

        WScript.Quit

    End If



    row = CInt(row)

    If row > (idNum - 1) Or row < 1 Then WScript.Quit



    ' Move to the selected record (counting from the first record).

    objCMISRecordSet.Move (row - 1), adBookMarkFirst



    WScript.Echo

End If



If Not objCMISRecordset.EOF Then

    dateOfBirth  = Left(Trim(objCMISRecordset("dateOfBirth")), 2) & Mid(Trim(objCMISRecordset("dateOfBirth")), 4, 2) & Right(Trim(objCMISRecordset("dateOfBirth")), 2)

    employeeId   = Trim(objCMISRecordset("employeeId"))

    employeeType = Trim(objCMISRecordset("employeeType"))

    lastName     = Trim(objCMISRecordset("sn"))

    firstName    = Trim(objCMISRecordset("givenName"))

    middleName   = Trim(objCMISRecordset("middleName"))

    initials     = Left(Trim(objCMISRecordset("middleName")), 1)

    calledName   = Trim(objCMISRecordset("calledName"))

    If Len(calledName) = 0 Then

	displayName  = firstName & " " & lastName

    Else

	displayName  = calledName & " " & lastName

    End If

    department   = Trim(objCMISRecordset("department"))

    studentId    = Trim(objCMISRecordSet("secondId"))

    If Len(objCMISRecordSet("sAMAccountName")) > 0 Then

	adUsername = objCMISRecordSet("sAMAccountName")

    Else

        adUsername = ""

    End If



    yearOfEntry = ""

    if objCMISRecordset("yearGroup") = "7" then yearOfEntry = "05"

    if objCMISRecordset("yearGroup") = "8" then yearOfEntry = "04"

    if objCMISRecordset("yearGroup") = "9" then yearOfEntry = "03"

    if objCMISRecordset("yearGroup") = "10" then yearOfEntry = "02"

    if objCMISRecordset("yearGroup") = "11" then yearOfEntry = "01"

    if objCMISRecordset("yearGroup") = "12" then yearOfEntry = "00"

    if objCMISRecordset("yearGroup") = "13" then yearOfEntry = "99"



    If employeeType = "STUDENT" Then

      description = "Student " & yearOfEntry & " / " & dateOfBirth

    Else

      description = "Staff_mail"

    End If



    WScript.Echo "Found '" & objCMISRecordSet("displayName") & "' (ID: " & employeeId & "; ADUsername: " & adUsername & ")"



    Dim objUser

    QueryAD employeeId, objUser



    WScript.Echo ""

    WScript.Echo "+==========================================================+"

    WScript.Echo "| Field        | CMIS/Source Value   | AD Value            |"

    WScript.Echo "+==========================================================+"

    WScript.Echo "| AD Username  | " & pad(adUsername, 20) & "< " & pad(getADValue(objUser, "sAMAccountName"), 20) & "|"

    WScript.Echo "| First Name   | " & pad(firstName, 20) & "> " & pad(getADValue(objUser, "givenName"), 20) & "|"

    WScript.Echo "| Middle Name  | " & pad(middleName, 20) & "> " & pad(GetADValue(objUser, "middleName"), 20) & "|"

    WScript.Echo "| Initials     | " & pad(initials, 20) & "> " & pad(GetADValue(objUser, "initials"), 20) & "|"

    WScript.Echo "| Last Name    | " & pad(lastName, 20) & "> " & pad(GetADValue(objUser, "sn"), 20) & "|"

    WScript.Echo "| Display Name | " & pad(displayName, 20) & "> " & pad(GetADValue(objUser, "displayName"), 20) & "|"

    WScript.Echo "| Description  | " & pad(description, 20) & "> " & pad(GetADValue(objUser, "description"), 20) & "|"

    WScript.Echo "| Department   | " & pad(department, 20) & "> " & pad(GetADValue(objUser, "department"), 20) & "|"

    WScript.Echo "| Employee ID  | " & pad(employeeId, 20) & "> " & pad(GetADValue(objUser, "employeeId"), 20) & "|"

    WScript.Echo "+==========================================================+"

    WScript.Echo ""



    WScript.StdOut.Write "Copy user data from CMIS/Source to Active Directory? (y/n) "

    yesNo = WScript.StdIn.Read(1)



    If yesNo = "Y" Or yesNo = "y" Then

        If Len(firstName) > 0 Then objUser.Put "givenName", firstName

	If Len(middleName) > 0 Then objUser.Put "middleName", middleName

	If Len(initials) > 0 Then objUser.Put "initials", initials

	If Len(lastName) > 0 Then objUser.Put "sn", lastName

	If Len(displayName) > 0 Then objUser.Put "displayName", displayName

	If Len(description) > 0 Then objUser.Put "description", description

	If Len(department) > 0 Then objUser.Put "department", department

	objUser.SetInfo

	WScript.Echo

	WScript.Echo "Active Directory account updated!"

	If employeeType = "STUDENT" Then

            If Len(adUsername) > 0 Then

                updateSQL = "UPDATE STUD_ADMIN.NSTUPERSONAL SET ADUsername = '" & Replace(objUser.Get("sAMAccountName"), "'", "''") & "' WHERE StudentId = '" & studentId & "'"

                objCMISConnection.Execute updateSQL

                WScript.Echo "Updated CMIS ADUsername field!"

            Else

                updateSQL = "UPDATE STUD_ADMIN.NSTUPERSONAL SET ADUsername = '" & Replace(objUser.Get("sAMAccountName"), "'", "''") & "', ADCreationDate = GETDATE() WHERE StudentId = '" & studentId & "'"

	        objCMISConnection.Execute updateSQL

                WScript.Echo "Updated CMIS ADUsername and ADCreationDate fields!"

            End If

        Else

	    If Len(adUsername) > 0 Then

                updateSQL = "UPDATE STUD_ADMIN.LECTDETS SET ADUsername = '" & Replace(objUser.Get("sAMAccountName"), "'", "''") & "' WHERE LecturerId = '" & studentId & "'"

                objCMISConnection.Execute updateSQL

                WScript.Echo "Updated CMIS ADUsername field!"

	    Else

                updateSQL = "UPDATE STUD_ADMIN.LECTDETS SET ADUsername = '" & Replace(objUser.Get("sAMAccountName"), "'", "''") & "', ADCreationDate = GETDATE() WHERE LecturerId = '" & studentId & "'"

    	        objCMISConnection.Execute updateSQL

                WScript.Echo "Updated CMIS ADUsername and ADCreationDate fields!"

            End If

	End If

    End If



    Set objUser = Nothing

End If

objCMISRecordset.Close

objCMISConnection.Close



objADConnection.Close

Set objADCommand = Nothing

Set objADConnection = Nothing



' Query AD for the specified employeeId, putting the AD user object in the supplied variable.

Sub QueryAD(employeeId, objUser)

    WScript.Echo "Querying Active Directory for '" & employeeId & "'"

    objADCommand.CommandText = "<LDAP://ADserver.example.com/dc=example,dc=com>;" & _

                               "(&(employeeId=" & employeeId & "));" & _

                               "ADsPath,distinguishedName;subtree"

    Set objADRecordSet = objADCommand.Execute



    If Not objADRecordset.EOF Then

        strADsPath = objADRecordset.Fields("ADsPath")

        Set objUser = GetObject(strADsPath)

    Else

	WScript.Echo "Could not find AD user with employeeID '" & employeeId & "'"

    End If



    objADRecordSet.Close

    Set objADRecordSet = Nothing

End Sub



' Pad a string with spaces to the specified length.

Function pad(strToPad, length)

    tmp = strToPad

    If Len(tmp) > length Then

        pad = Left(tmp, length)

    Else

	Do While Len(tmp) < length

	    tmp = tmp & " "

	Loop

	pad = tmp

    End If

End Function



' Get an value from the AD user object, ignoring errors.

Function GetADValue(objUser, strValue)

    On Error Resume Next

    GetADValue = objUser.Get(strValue)

    If Len(GetADValue) = 0 Then

        GetADValue = ""

    End If

End Function

Not my code.
will need modification for your site. Please use, distribute and update any fixes - works for us though.

[mark]

Wow! Can this be done with SIMS .net too?

[CyberNerd]

I'm not too sure about SIMS but assuming its a MSSQL database, you'd just need to know the table structure.

[mark]

Thanks CN - just saw the other thread. This would be so useful for schools.

[Geoff]

It's against the SIMs EULA though. You can't access the data directly. You can only access the data via the SIMs API. You can only use the SIMs API if you have a developer license from Capita (read expensive).

[CyberNerd]

It's against the SIMs EULA though. You can't access the data directly. You can only access the data via the SIMs API. You can only use the SIMs API if you have a developer license from Capita (read expensive).

I can see the headlines now: "MultiMillion Pound IT firm sells hard-up schools shoddy software then sues them for improving it" -I never had a future in media (or law) -I've disributed this as proof of concept, nothing more.

[Geoff]

They wouldn't sue the school directly. It's bad publicity and there's no money in it. They'd sue the LEA instead.

[CyberNerd]

Thats fine, we've not listened to the LEA in long time, they use SIMS !
it would more be difficult to sue for something they dont use

[sahmeepee]

It's against the SIMs EULA though. You can't access the data directly. You can only access the data via the SIMs API. You can only use the SIMs API if you have a developer license from Capita (read expensive).

Not quite right Geoff! Although you can't read from the database directly, you can read from the SIMS database via their reporting SDK gratis. They have fairly good reasons for this:

(From Capita's Dan Clark on the SIMS forums)

We do not support direct access to the SIMS SQL Database for a variety of reasons - the 3 main ones being Validation, Security and Change.

In the case of validation, there is a huge set of business rules that get applied to the data both before it is read and written - to manipulate the type and format of the data returned; therefore meaning that direct queries will not always return the correct data (just identifying students on roll is not as easy as it actually seems - fields in the SIMS database are not actually "as they seem").

In security terms, you actually have to either change the database or server configuration to add physical SQL Users, or you have to use a dbo equivillent user to access the database - both of which result in security issues.Lastly, the Database Schema constantly changes and therefore there will be no way to keep up with the changes.

So whats the solution? Actually - there is a 3rd party SDK in SIMS that allows you to extract the data , by automatically running reports and returning data via XML or CSV. Email me for the documentation, or, if this does not suite, we can look at other solutions.

I've got the docs for this recently from Dan Clark (the tools are already in your SIMS install! The executables start with "command"). It's not perfect, but at least a csv/xml output could be imported into a database without human intervention given a little effort.

The bad news - because it works via the reporting mechanism it's read-only. For the purposes of this thread that shouldn't be a problem.

I'm somewhat surprised they can't find room in their 250MB SIMS install for this 50KB SDK manual. I will check with Dan Clark to see if I can post the file on here.

You might also like to consider this post from Phil Neal on the moodle forums on Monday 26th June:

Capita got together with Microsoft, RM and Granada over 2 years ago and proposed an open standard to Becta/DfES for moving data from an MIS to a VLE. The link is based on IMS. Becta has not taken up this proposal.

We decided that in spite of not getting traction we should progress the proposal and the link has now been built into SIMS. It will be included in our software in the autumn. The Moodle community can develop a link if they would like to. Details will be made available on our web site.

Phil Neal

Director SIMS

Apologies for the long post, but I think some of these developments could really help people in here. (BTW Thanks to Karl for finding the 2nd quote )

[TechMonkey]

Did you get any reply about post the SDK documentation? Just been looking at the very post you quoted, which lead me here from google, and can't find the SDK in our SIMS install. Been looking at doing this for an age!