Active directory queries

I had to spend lot of time trying to figure how to write queries on active directory using SSIS. I searched the internet for quite some time with no concrete examples. I did find examples using scripts but none for querying using ADO objects in SSIS. So I thought there might be others like me out there who might need some help .. so here goes

some of the Field names
- givenName = First name of user
- sn = Last name of user
- memberof = list of groups that the user belongs to
- cn = Display name
- objectGUID = Unique value for each record
- samAccountName = windows Login Name

The "FROM" clause would need to contain the complete domain controller name
so if you domain is called "corp.company.us.com", then your from clause would be like this
select field1, field2 from
FROM 'LDAP://DC=corp,DC=commpany,DC=us,DC=us'

if you want to fetch only the users you will need to specify a where clause like WHERE objectClass='user'

I had a requirement where I had to fetch users belonging to a particular group. I had a lot of problem finding this query. I finally learned that your where clause needs to have the whole heirarchy of the group specified. For eg, If you want to find all users in a Group called "Finance" your where clause needs to include

> memberOf = 'CN=Finance,DC=corp,DC=commpany,DC=us,DC=us'

Now if the department is within a Orgnization unit or OU called "AllUsers", then qour query needs to specify that too
> memberOf = 'CN=Finance,OU=AllUsers,DC=corp,DC=commpany,DC=us,DC=us'

Similarly you need to first check how the groups, users are created within your active directory and make changes to your query .. do remember to add the domain controller information too ..

Sample query finding all users within finance group where the finance group is plaed within a OU=Allusers

select cn
FROM 'LDAP://DC=corp,DC=commpany,DC=us,DC=us'
WHERE objectClass='user'
and memberOf = 'CN=Finance,OU=AllUsers,DC=corp,DC=commpany,DC=us,DC=us'

Hope this helps someone out there ..

Querying Active directory - how to fetch more than 1000 records

I had developed and tested a SSIS package which queried active directory and imported the users into a SQL server table. This was working fine till I deployed this on the client server. Some of the AD users were not getting imported. The users that were not been imported also has the same attributes and belonged to the same OU and domain as the users that were successfully imported. So I got confused and started investigating.

After some hours of various changes to the package and testing, I noticed that only 1000 records were been fetched every time. This surprised me and so I changed the query to fetch only one user which was not getting imported earlier (realised later, that this should have been my first thing I should have tested). This user now got successfully imported. So I concluded that issue was indeed with the records been limited to just 1000.

So now I started searching the internet for a solutions to this wierd problem. There were various solutions where the pagesize could be set when you user directory service objects but I could not find a solution on how to set the pagesize when queried through SSIS. So I started looking whether there is any policy that is set on active directory server and whether it is defaulted to 1000. This is when I found this article
http://support.microsoft.com/default.aspx?scid=kb;en-us;315071&sd=tech

Using the instructions in that article, I did the following
1. At the Ntdsutil.exe command prompt, type LDAP policies, and then press ENTER.
2. At the LDAP policy command prompt, type connections, and then press ENTER.
3. At the server connection command prompt, type connect to server , and then press ENTER.
4. At the server connection command prompt, type q, and then press ENTER to return to the previous menu.
5. At the LDAP policy command prompt, type Show Values, and then press ENTER.

You will see all the values set and can confirm whether the MaxPageSize is set to 1000

This is what Microsoft says about "MaxPageSize"
MaxPageSize - This value controls the maximum number of objects that are returned in a single search result, independent of how large each returned object is. To perform a search where the result might exceed this number of objects, the client must specify the paged search control. This is to group the returned results in groups that are no larger than the MaxPageSize value. To summarize, MaxPageSize controls the number of objects that are returned in a single search result.
Default value: 1,000

We can change this value by running the command
Set MaxPageSize

I am yet to test this on the client's server and will get back whether this helped in solving the problem or not. But this looks promising :-)

System.Object(), SSIS, Active directory and MemeberOf property

For the last couple of months I have working on sharepoint solutions and one of the requirement involved in fetching all users and groups from active directory. The data fetched had to be then inserted into a local SQL databaseb. As this information changes with time, the process had to be run once daily. So I opted on using SSIS to automate the whole process

It seemed pretty straight forward. I used a OLEDB connection of "Microsoft data services" to read the data from active directory. As a test I fetched couple of fields like the cn, name etc and everything worked fine, till I decieded to get the groups that the users belong to. I knew that it would be part of the "memberof" property. I used the same approach and I noticed that value gets inserted as "System.Object[]" into the database. I figured out that the value is returned as a blobcolumn and anyway I tried to parse it, it always gave me the same value. I tried

Dim lByte() As Byte = CType(dataRow(0), Byte()) .. where datarow(0) is the memberof field
System.Text.Encoding.Unicode.GetChars(lByte)

... also tried converting the column to string, collection, byte etc etc ..

and many other options .. all gave me the same result or an error .. I was really getting frustrated and I searched the net for solution. There are some examples on using SSIS and active directory and no where do they talk about how to read value from this column. So I came back to trying it on the own. I wanted to check the type of this column. But I could not add a breakpoint into a "script component" (and that is another story) .. I learned that I could add a breakpoint into a "script task" and so I copied my code over there and added breakpoint to see what data is fetched.

I saw that the column actually contains a array value. I had never seen this before .. The content of a single column looked like
(0) CN=CN1,OU=OU1,DC=corp1,DC=corp2
(1) CN=CN2,OU=OU1,DC=corp1,DC=corp2
(2) CN=CN3,OU=OU1,DC=corp1,DC=corp2

so i converted the column value to "system.array". and got the information that I needed ...

Hope this helps some people out there .. and not have to spend hours or days looking for a solution.

Rejo