Courion Tip – LDAP/AD Queries

I have working SSIS packages that do an LDAP bind and pull in user information directly. I want to post the *.dtsx package here, however, I need to make very sure that no confidential information gets uploaded.

Also, it’s entirely possible that it won’t work when I get done. I will copy the *.dtsx to a virtual machine, and sanitize it there. All of which is to say, “stay tuned”.

Update – I finally have these cleaned enough where I am POSITIVE there is no confidential information. Unfortunately, wordpress only allows certain file types. So, I renamed it from RAW_AD.dtsx to RAW_AD.dtsx.docx (click here to download it).

Once you get it up and running, you might need this link to modify it for your needs.

A brief walk – thru (and, NO, these are not best practices – I’m just trying to get your system to collect information. You should tweak it!!)

  • Create a domain admin level account called courion, with a password of ‘password’.
  • Create a database called Courion (unless, of course, you already have one), and give the user courion complete access to it.
  • Start up Business Intellegence Development Studio.
  • File, New, Project, “Integration Services Project”
  • Give it a name that matches your standards, and a file location that makes sense. For me, I used “D:\SSIS Packages” and called the project “RAW_AD” – since it will be doing an import of Active Directory data and minimal manipulation of the data.
  • On the far right side, right mouse click on the words “SSIS Packages”, and choose “Add Existing Package”
    • Change the package location from “SQL Server” to “File System”
    • Click on the 3 “dots” to browse to the file you downloaded from this site (after you took the .docx off). Note that it will copy it to your new solution, so that you don’t need to worry about saving the original – it won’t be touched.
    • Click on Open, and OK.
    • You will probably get an error message about something not being in the right cryptographic state, don’t worry about this now.
  • Double click on the “RAW_AD.dtsx” and it should open up.
  • At the bottom of the screen, under “Connection Managers”, you should see 2 – “Courion Database” and “SMTP Connection Manager”.  For now, ignore the SMTP connection.
    • Double click on the “Courion Database” connection manager.
    • Change the name of the SQL Server to match your environment.
    • Choose whichever authentication matches your environment, but I’d suggest “Use Windows Authentication” – for now.
    • Choose your Courion database under “Connect to Database”. If you can’t choose it from the drop down box, then you probably have the server name incorrect, or you have chosen the wrong instance.
    • Click on “Test Connection” – if the connection fails, then the entire thing will not work. You must fix this.
    • Click on “OK”
  • Repeat, if you want, with the SMTP connector. (though it’s configuration is much simpler)
  • Create the required tables:
    • RAW_AD_temp: Right click on the box that says “Drop and recreate RAW_AD_temp”, and select “Execute Task”. While this isn’t strictly necessary, you’re probably seeing some error messages. By creating this table, the errors/warnings will go away.
    • RAW_AD. Download Create RAW_AD.sql.Again, to get it to upload, I had to rename it, putting a “.docx” at the end of the name.  Open it with any text editor, and then copy it’s contents into your SQL server management studio. Execute the sql statement, and you should be all set creating this table. You might want to create this table on your own. However, since the column names and data types might not match what I chose, you might generate errors. Save that for a future experiment.

At this point, you should probably save your work, and exit out of BIDS (Business Intelligence Development Studio). Then go back into BIDS, and open your project. Why? Mostly, to clear out any error messages that you see and verify that any existing errors/warnings are ‘real’ ones, and not just cached/queued ones.

If you don’t see any error messages, then it’s probably time to test your package. You might wonder about the greyed out boxes – some are for email notifications, and the others are ideas I haven’t fleshed out completely. Perhaps I will post updates to this, but for now, since I am assuming you are new to this, please ignore them.

To run the SSIS package, click on the “Debug” menu, then “Start Debugging” menu item. As each step of the process completes, it should turn green for success, or red for failure. Please read any pop-ups, as they should help you debug the SSIS package.

A few final notes:

  • Data types and data length. There are several places that modifications need to be made, and I have been very careful to avoid generating errors and/or warnings.
    • RAW_AD and RAW_AD_temp tables. Don’t forget that the temp table gets rebuilt on the fly, so you need to edit the script, not just the table.
    • The “Inputs and Outputs” of the “Script Transformation Editor”
    • The vbscript itself
  • Rebuilding the vbscript – to rebuild the vbscript that does the ldap bind:
    • Double click on the box that says “RAW_AD temp – Dataflow”
    • Double click on the box that says “RAW_AD Query Script”
    • Click on “Edit Script”
    • A new window should open, called “ssisscript – Integration Services Script Component”
    • In the upper right of the screen, right mouse click on “ad_query_vbs” and select “Rebuild”
    • Click on “File” and “Save All”
    • Close out this window

There is certainly room for improvement. For instance, instead of dropping all the data from the RAW_AD table every time, a slowly changing dimension could be used. The authentication for SQL Server could be stored in a variable (in a configuration file) making the code portable. And, of course, what data is collected could be changed.

Additional notes – SQL server supports a ‘checksum’ calculation, for instance : This makes a WHOLE lot more sense for checking to see if a user’s information has been changed than a HUGE query. That, coupled with triggers, will ensure that the information is up to date.


Posted June 7, 2012 by mmdmurphy in Courion Tip

Tagged with

%d bloggers like this: