Tag Archives: Active Directory

Automatically creating Active Directory accounts for students – Part 1

Leave a comment

Active Directory logo
At the school where I work, student details are stored in a student administration system called SAS2000. This stores lots of information about each student like name, date of birth, year level, parents names, enrollment dates and their student ID number. The actual data is stored in a SQL Server database on one of our servers.

I wanted to create something that would be able to be hopefully modified by someone other that me in the future, so I chose to use vbscript, instead of my preferred language of Delphi for this task. At first I had code to query the Student table in the SQL Server database and then some functions to query Active Directory. This made the code balloon up in size and was getting to complicated.

Querying Active Directory with SQL Server

ADSI
After a bit of research on the internet I found that you could use a feature of SQL Server to show active directory accounts as a table. This involved creating a connection to AD in the linked servers section of Management Studio and then creating a view called AD_Student with the code shown below. This uses the OLEDB Provider for Microsoft Directory Services.

SELECT sn, givenName, cn, sAMAccountName, title, employeeID, facsimileTelephoneNumber, 
            mobile, mail, telephoneNumber, displayName
FROM OPENQUERY(ADSI,'SELECT displayName, telephoneNumber, mail, mobile, 
    facsimileTelephoneNumber,employeeID ,title,sAMAccountName, cn,givenName,sn  
         FROM  ''LDAP://domain.local/OU=Students,DC=domain,DC=local'' WHERE objectClass =''User'' ')
         AS tblADSIORD

You obviously need to replace LDAP://domain.local/OU=Students,DC=domain,DC=local with what is relevant to your Active Directory domain.

Linking data from Active Directory with a SQL Server table

In SAS2000 each student gets an ID code that is unique to them. Each AD user account also gets a unique GUID. My first idea was to create a table that linked the Student ID Code from the SAS2000 student table to the account GUID in active directory. Unfortunately I wasn’t able to work out how to get the GUID with VBScript. My next idea was to alter the AD Schema. After some further reading however, I came across an existing field in Active Directory called EmployeeID. Obviously students aren’t employees, but it is a near enough match. So I went through all the students accounts and manually added their Student ID Code from the SAS2000 table to the EmployeeID field in Active Directory.

From this I was able to create another view that uses a “NOT IN” query that results in a list of students that are not in Active Directory Yet.

SELECT ID, Code, FirstName, MiddleName, LastName, PreferredName, PostNominal, NName, FormerName, TransferCode, FC2kSourceID, FC2kID, FC2kCode, Cases21ID, Cases21Code, AddressID, LeavingAddrID, NewAddrID, PhoneFamily, PhoneHome, PhoneMobile, Email, Email2, FamilyID, DOB, NDOB, Sex, CreatedWhen, UpdatedBy, UpdatedWhen, Comments, COB, PlaceOfBirth, Religion, House, Form, Distance, StudentType, HomeGroup, Year, Class, Rank, Youngest, Oldest, EntryYearLevel, StartYear, StartDate, PrevSchoolID, PrevLevel, PreEnrolment, Inactive, InactiveFrom, InactiveTo, InactiveReason, Vsn
FROM dbo.SAS_Student
WHERE (Code COLLATE DATABASE_DEFAULT NOT IN (SELECT StudentID FROM dbo.AD_Student))

This query shows any students that are enrolled, but don’t yet have a Active Directory user account. See Part 2 for actually creating the account in Active Directory.