A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Wednesday, April 27, 2011

Active Directory SSIS Data Source

Want to make one of these? See biml active directory ssis data source

The authoritative source for email addresses in my organization is Active Directory. Pretty much everything else comes from Payroll. When we rolled out the corporate credit card vendor, we had to send them a list of people, their unique identifier (clock number in our case) and their email address. We already had a process that pulled data from the payroll system so we just needed a way to tie in the AD information. We managed to get an existing attribute of AD filled out for all our people, employeeNumber, and that allowed us to build out the following package.

My initial approach was to use a linked server to AD from within SQL Server and my god, I'd rather write complex queries against mysql or Access than go through that again. I eventually ran into issues with certain datatypes not being able to be returned. I've since lost the link but I found a writeup that talked about fields like memberof being of type i8 which means you're humped. I ran into pain with doing any sort of a complex where clause as well. The i8 datatype will be an issue whether you use query from SSIS or SSMS. At any rate, I wanted to document what an approach could be for querying data from Active Directory.

Known issues, from least to most severe if you're going to copy/paste my solution.

  • Speed-the group lookup script is dog slow
  • MaxPageSize aka number of rows = 1000-How Active Directory Searches Work
  • Nested Groups-the script only pulls back the first degree children
With those disclaimers out of the way, the way I designed this package was to dump AD information to files that would be consumed by other processes. In the event something went wrong, I'd be able to have a known state to debug from instead of worrying about a LAN admin made a change while my process was running. The important thing I saw it was getting a list of all the people out and all the groups they belonged to as we had upcoming projects that needed clock number and email address but also needed group membership information.

Create a Connection Manager for ADO.NET . Use the ".Net Providers for OleDb\OLE DB Provider for Microsoft Directory Services" For me, it was the second, non-default, folder. For Server or file name: use your domain controller. It should look something like this.

From World of Whatever

Grab a data reader source and wire it up to your new connection manager. This query will pull back all the items in AD that are Users (sAMAccountType = 805306368)

SELECT
    distinguishedName
,   mail
,   samaccountname
,   Name
,   employeeNumber
,   objectSid
,   userAccountControl
,   givenName
,   middleName
,   sn
FROM
    'LDAP://DC=domain,DC=net'
WHERE
    sAMAccountType = 805306368
ORDER BY
    sAMAccountName ASC

I use the Raw File Destination (binary) to hold all the Active Directory information because they are faster and don't require the hassle of defining connection managers. Data coming out of AD is either integers or unicode text streams. I know my data well enough to know unicode isn't a concern. Converting to strings, I used a length of 28 for objectSid, 64 for sn, givenName, middleName, 255 for samaccountname and Name and 256 for distinguishedName and mail. It's been a few years since I wrote the package so I can't recall the why on those lengths. Your mileage may vary.

From World of Whatever

IsActive - this one bit me in the butt. For the userAccountControl a value of 512 is an active AD account, 514 is a disabled account. I have that in documentation from another team. Unfortunately, that's not quite accurate. That field is really a bitmask. A year or two after we implemented my package, we learned that inactive people were showing up in feeds which was impossible, we filter them (514s) out. I then learned that a 514 is really just a 512 (NORMAL_ACCOUNT) ORed with 2 (ACCOUNTDISABLE). An automated process had been implemented that created accounts with expired passwords or some such nonsense which had a net result of disabled accounts reading as 546 (regular became 544 - Account Enabled - Require user to change password at first logon).

The group lookup is an asynchronous script that writes a row to the output buffer for each group they are a member of.

Imports System
Imports System.Data
Imports System.Collections.Generic
Imports System.DirectoryServices
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

    Dim User As DirectoryEntry

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        Try
            'MsgBox("LDAP://" + Row.distinguishedName)
            User = New DirectoryEntry("LDAP://" + Row.distinguishedName)
            With GroupUserOutputBuffer
                For Each group As String In User.Properties("memberOf")
                    GroupUserOutputBuffer.AddRow()
                    GroupUserOutputBuffer.sAMAccountName = Row.sAMAccountName
                    GroupUserOutputBuffer.employeeNumber = Row.employeeNumber
                    GroupUserOutputBuffer.distinguishedName = Row.distinguishedName
                    GroupUserOutputBuffer.memberOf = group
                Next
            End With
        Catch ex As Exception
            'ErrorOutputBuffer.AddRow()
            'ErrorOutputBuffer.groupdistinguishedName = Row.
        End Try
    End Sub

End Class

Below is an example of the Users and groups file if you want to visualize it

Users.raw
sAMAccountNameuserAccountControlemployeeNumberdistinguishedNameisActivemailnamefirstNamemiddleNamelastName
AAchoo512123456789CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=netTrueAAchoo@domain.netAngela AchooAngelaAchoo
Groups.raw
sAMAccountNameemployeeNumberdistinguishedNamememberOf
AAchoo123456789CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=netCN=Mobile Users,OU=Home,DC=domain,DC=net
AAchoo123456789CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=netCN=REPORT_VIEWER,OU=Prod,OU=ApplicationGroups,DC=domain,DC=net
AAchoo123456789CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=netCN=REPORT_VIEWER2,OU=Prod,OU=ApplicationGroups,DC=domain,DC=net
AAchoo123456789CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=netCN=REPORT_ACCESS,OU=Prod,OU=ApplicationGroups,DC=domain,DC=net
AAchoo123456789CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=netCN=Feed_Exclustion,OU=Prod,OU=ApplicationGroups,DC=domain,DC=net
AAchoo123456789CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=netCN=National_Accounts,OU=SALES,OU=Home,DC=domain,DC=net
AAchoo123456789CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=netCN=Interclass Exchanges,OU=Resource Mailboxes/Distribution Lists,OU=CUST,OU=Home,DC=domain,DC=net
AAchoo123456789CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=netCN=Remote_Users,OU=APPS_GROUPS,OU=Home,DC=domain,DC=net
AAchoo123456789CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=netCN=Primary SMTP Group,OU=SALES,OU=Home,DC=domain,DC=net
AAchoo123456789CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=netCN=Corporate Card,OU=ACCT,OU=Home,DC=domain,DC=in,DC=net

References

7 comments:

Sekowski said...

Bill, is it possible to send me the sample project? I'm having some problems recreating it.

Bill said...

You bet, either leave your address (I won't publish) or contact me at billspam.fellows@gmail.spam.com but be sure and hold the spam

Simon J said...

I am in a similar position to 'Sekowski'. This looks like it may be just what I need. Would it be possible to send me a copy of the project?

Bill Fellows said...

Sure thing Simon J, either get a hold of me on the above email (without the word spam) or provide a method to contact you. Your blogger profile doesn't provide me enough information to get a hold of you

abowater said...

Bill, thanks for posting this information. Like Sekowski and Simon I cannot seem to get the SQL syntax correct for the where clause for eliminating inactive accounts. If possible I wouldl like a copy of your sample project as well. Thanks in advance. I did send you an email.

shilpa said...

Hi Bill, I am having problems with the script component task. Would it be possible for you to send me the project? Thanks so much(address is shilpas.amr@gmail.com)

Kelly Korzen said...

I too would love to take a crack at this code. could you send me the project if you still have it? thanks! harrigan_kelly@hotmail.com