+ Post New Thread
Results 1 to 10 of 10
Coding Thread, Autocreate AD accounts from MSSQL in Coding and Web Development; Code: ' 2006-06-27 ' This script copies user details from MSSQL to Active directory for STUDENTS only. ' It has ...
  1. #1


    Join Date
    Jan 2006
    Posts
    8,202
    Thank Post
    442
    Thanked 1,032 Times in 812 Posts
    Rep Power
    339

    Autocreate AD accounts from MSSQL

    Code:
    ' 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.

  2. #2
    mark's Avatar
    Join Date
    Jun 2005
    Posts
    3,966
    Thank Post
    248
    Thanked 49 Times in 45 Posts
    Blog Entries
    2
    Rep Power
    46

    Re: Autocreate AD accounts from MSSQL

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

  3. #3


    Join Date
    Jan 2006
    Posts
    8,202
    Thank Post
    442
    Thanked 1,032 Times in 812 Posts
    Rep Power
    339

    Re: Autocreate AD accounts from MSSQL

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

  4. #4
    mark's Avatar
    Join Date
    Jun 2005
    Posts
    3,966
    Thank Post
    248
    Thanked 49 Times in 45 Posts
    Blog Entries
    2
    Rep Power
    46

    Re: Autocreate AD accounts from MSSQL

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

  5. #5

    Geoff's Avatar
    Join Date
    Jun 2005
    Location
    Fylde, Lancs, UK.
    Posts
    11,804
    Thank Post
    110
    Thanked 583 Times in 504 Posts
    Blog Entries
    1
    Rep Power
    224

    Re: Autocreate AD accounts from MSSQL

    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).

  6. #6


    Join Date
    Jan 2006
    Posts
    8,202
    Thank Post
    442
    Thanked 1,032 Times in 812 Posts
    Rep Power
    339

    Re: Autocreate AD accounts from MSSQL

    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.

  7. #7

    Geoff's Avatar
    Join Date
    Jun 2005
    Location
    Fylde, Lancs, UK.
    Posts
    11,804
    Thank Post
    110
    Thanked 583 Times in 504 Posts
    Blog Entries
    1
    Rep Power
    224

    Re: Autocreate AD accounts from MSSQL

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

  8. #8


    Join Date
    Jan 2006
    Posts
    8,202
    Thank Post
    442
    Thanked 1,032 Times in 812 Posts
    Rep Power
    339

    Re: Autocreate AD accounts from MSSQL

    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

  9. #9
    sahmeepee's Avatar
    Join Date
    Oct 2005
    Location
    Greater Manchester
    Posts
    795
    Thank Post
    20
    Thanked 70 Times in 42 Posts
    Rep Power
    34

    Re: Autocreate AD accounts from MSSQL

    Quote Originally Posted by 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).
    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 )

  10. #10

    TechMonkey's Avatar
    Join Date
    Dec 2005
    Location
    South East
    Posts
    3,291
    Thank Post
    226
    Thanked 405 Times in 302 Posts
    Rep Power
    162

    Re: Autocreate AD accounts from MSSQL

    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!

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 3
    Last Post: 1st August 2007, 10:00 PM
  2. Open Source CMS that can use MSSQL
    By ICTNUT in forum Windows
    Replies: 4
    Last Post: 29th March 2007, 03:08 PM
  3. WSUS & Sophus - MSSQL ??
    By tech_master in forum Windows
    Replies: 3
    Last Post: 18th January 2007, 06:22 PM
  4. MSSQL Trigger (i think) Question
    By k-strider in forum Coding
    Replies: 3
    Last Post: 28th November 2006, 12:15 PM
  5. MSSQL Trigger (i think) Question
    By k-strider in forum Coding
    Replies: 0
    Last Post: 27th November 2006, 11:45 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •