Saturday, February 15, 2014

Nerd Hobbyist: A 10 cent Web Interface on AD LDAP Queries


The Goal

Provide a web interface (web page) to create, save and execute Active Directory LDAP queries within a domain environment.  This version is predicated on using ASP, rather than ASP.NET.  This is sad and unfortunate, but it works.  I plan to post an update to this using ASP.NET, focused on "zero-cost" as the main constraint (i.e. no purchases required beyond the base operating system stuff).

Also, rather than just posting the code, I'm going to describe the process and the steps.  Otherwise, you will do the lazy thing (like I would) and just ignore my rambling and download it.  Then I'd get a ton of emails from you asking why something didn't work as you expected.

Can this be accomplished by other means?  Absolutely.  Are those other means easier to implement?  Maybe.  Are those other means cool?  Maybe.  Does Dave care?  Not really.

As for ASP: if you one of those folks who are already turning your nose up at this choice, note that there are still a shit-ton (that's a real number, trust me) of web pages still being served up via ASP, even on Microsoft web sites. So: nah-nah-nee-boo-boo.  So, for now, relax, drink up and enjoy my little bus stop cooking show.  Cheers!

The Ingredients

  • An Active Directory network environment (preferably 2008 domain-level or higher)
  • An IIS host (physical or virtual, server or client version).
  • A dedicated AD account for use with executing the queries.
  • A database for storing your precious LDAP queries in.
  • A tablespoon of ASP (vbscript) code and your favorite text/code editor application.
  • A few cups of coffee
The Recipe - Database Cookie Dough:
  1. If you don't have a SQL Server database server at your disposal, then install SQL Server Express (2012 is preferred, or whatever is the newest and shiniest).
  2. Create a database (preferred, to keep it separate from other databases).  Suggested name: adqueries.
  3. Create a table in that database.  Suggested name: reports.  Figure 1 (below) shows a variation on this with some additional columns.
  4. Create the following minimum columns:  ID, ReportName, Comment, QueryText
  5. Make ID type = Int, Identity(1,1) and primary key (not null).  Make ReportName type=varchar(255) (not null), Comment type=varchar(255) (null), and QueryText type=varchar(255) (not null).
  6. Grant permissions to the database and the table for however you prefer your spiffy web app to interact with it.  Integrated Security is fine, as is SQL security with an internal SQL account.  You can use text-book "role" based access management, but whatever you do, just make sure to grant access within SQL Server to allow that account to make a remote connection.
Figure 1 - Example Table Structure
For purposes of saving time, go ahead and manually stuff a few example reports/queries into the table.  That way when you finish your coffee and the first page (see below) you will have some sample rows to validate your web site interface to the database.
Figure 2 - Sample reports
Example queries/reports could be some really basic stuff like:
  • Report 1
    • ReportName= "Computers - Windows Server 2012"
    • Comment = "Domain computers running Windows Server 2012"
    • QueryText = "objectCategory='computer' and operatingSystem='Windows Server 2012*'"
  • Report 2
    • ReportName = "Computers - Windows 8"
    • Comment = "Domain computers running Windows 8.x"
    • QueryText = "objectCategory='computer' and operatingSystem='Windows 8*'"

The Web Sauce:
  1. Create a folder on your chosen web host.  This can be a Windows 7 or 8 computer, or a Windows Server 2008...2012 computer.  As long as it's a member of your target AD domain.  Suggested path/name: c:\inetpub\wwwroot\adqueries.
  2. Create a web site virtual folder linked to that shiny new folder.  It can use anonymous authentication if you feel comfortable with that, or do as you please.  If it works and doesn't cause the FBI to kick your door in and shine red LASER dots on your forehead, you will probably be alright.  Suggested site name: adqueries.
  3. Optional (but preferred): Convert the virtual folder into an IIS application.  Assign a domain account for the application pool process context.  That account should have rights to query whatever it is you want to query.  It does NOT have to be a domain administrators member, or anything that serious.  Click the "Test Connection" button to ensure it works.
  4. You will likely need a minimum of 4 code files:
    1. A main page which provides a list of saved LDAP queries, each of which are hyperlinked to a "run-query" type of page.  Suggested name: default.asp
    2. A "run-query" type of page.  Suggested name: report.asp
    3. A code library page, which will be included in each of the other pages (centralized code stuff).  Suggested name: my_asskicking_codestuff.asp.  Include it in the other pages using the standard top-line import method: (#include file=_my_asskicking_codestuff.asp)
    4. A form page for adding new, or editing existing, queries.  It should have text boxes for the following properties at a minimum (add more if you like):
      1. Report Name [textbox]
      2. Report Comment / Description [textbox]
      3. LDAP query code statement [textarea] or [textbox]
        suggested name: reportedit.asp
    5. OPTIONAL: You can code the form-page (mentioned above) to serve as both the "add new" and "edit" pages if you like, or make separate pages.  It doesn't matter as long as you're happy and it gets your mo-jo humming along.  For example: reportnew.asp, reportedit.asp
  5. Drink your coffee and start coding it up.
The First Query
  1. Here's an easy one:  objectCategory='computer' AND operatingSystem='Windows 8*'.  If you actually followed my suggestion earlier, then you'll want to try a new one, like 'Windows 7*' or whatever.  Keep mind you can make queries to search for almost anything: user accounts, groups, contacts, printers, shares, etc.
  2. Drink more coffee
The ASP Stuff
    I wanted to avoid this, but I suppose I can't totally ignore it.  I can ignore my neighbors (they ignore me anyway) but code is hard to ignore.

    The code library file:
    • Needs to include some basic stuff like variable definitions and Const definitions, which identify such things as ADSI values, ADO values, the name of your SQL Server and Data Source Name (DSN), and so on.  Example below:
         '------------------------------------
         ' comment: ADO and ADSI enumerations
         '------------------------------------
         Const ADS_SCOPE_SUBTREE = 2
         Const E_ADS_PROPERTY_NOT_FOUND = &H800500D
         
    Const adOpenForwardOnly = 0
         Const adOpenKeyset = 1
         Const adOpenDynamic = 2
         Const adOpenStatic = 3
         Const adLockReadOnly = 1
         Const adLockPessimistic = 2
         Const adLockOptimistic = 3
         Const adLockBatchOptimistic = 4
         Const adUseServer = 2
         Const adUseClient = 3
         Const adCmdText = &H0001
    • The ADSI/OLEDB connection stuff:  The standard interface between script code and Active Directory (for LDAP queries) is through an OLEDB provider.  For more info on this, check out http://msdn.microsoft.com/en-us/library/windows/desktop/aa746517(v=vs.85).aspx 
    • Take this example (credit: ActiveExperts.com: link) and use it to test by running it in a CMD console using RUNAS to verify the AD account you intend to use will work properly.
    • Adapt the VBscript code (above) to ASP by changing the wscript.echo methods to Response.Write (be sure to enclose the string results in HTML tags to format it properly).
    • Test it via your web browser.

    Yes.  I know you can do this with ASP.NET and Visual Studio Express, but if you're still working with ASP, like millions of other Earthlings, you still have options (and free web blogs).  If you REEeeeeeeaaally want some actual code, let me know (post a comment below).

    Now.  If you'll excuse me, I need to go pass out on my bed. Namaste.
    Post a Comment