SQL Server & other stuff related

Querying Active Directory on SQL Server using CLR

In my previous article Querying Active Directory on SQL Server using T-SQL I was showing the possibilities of querying the Active Directory using T-SQL, specifically using linked server and OPENQUERY statement or without linked server using the OPENROWSET statement.

This is an updated article and includes modification coming upon some of the comments to resolve some issues with large number of returned AD properties.

There ware mentioned some limitations of querying the AD using T-SQL and CLR will help us to bypass those limitations. Especially the limit of 1000 records returned as we can use pagination in the CLR code effectively.

For the purpose I’m creating a CLR Stored Procedure which will take several argument and return a result set. The reason I’m going to use a CLR Stored Procedure is, that stored procedure will allow me to return dynamic result set. It means I can specify properties of AD to return and those will be returned as columns of the result set. In case we go through the CLR Table Valued function, we had to create a separate function for each properties combination we would like to return.

The class contains three public methods (CLR Stored Procedures) QueryAD, QueryADUName and QueryADAuth. The first one will query AD using current user credentials and default authentication method, second will query AD using provided user credentials and default authentication method and in the third one we can specify also authentication method.

Those methods call a private method SearchAD, which takes care about the AD Searching and return the returns the result to client.

After the comment from Chris, I’ve updated my sample codes to have also the optional parameter pageSize which allow reduce the size of a Page for Paged Search used for querying the AD and avoid insufficient memory problems when querying higher amount of AD attributes.

I’ve also added a parameter rowsLimit which allows limit the maximum number of rows returned, which can be useful especially when querying very large AD. There is added a break into the loop which iterates the results from AD when the number of precessed imtes reach the limit.

UPDATE:

Finally I’ve also updated the private static void SearchAD method so now you pass the properties not as a comma separated list but a semicolon separated list. You can use a comma to specify the return length of each property. If the length is not specified, then the method will use a default 4000 characters length. This update should finally solve the issues with large number of properties returned when previously all were returned as nvarchar(4000)

The updated part of the code is the for loop which is processing properties and generating result set metadata, starting with the comment //Trim properties and prepare result set metadata, also process specified lengths

END OF UPDATE:

To be able to compile the code for example using Visual C# Express it is necessary to add reference to the System.DirectoryServices assembly in the project.

Once we compile the code and create say ADServices.dll assembly, we can register that assembly and CLR Stored procedures in our database.

Because the ActiveDirectory class is using System.DirectoryServices assembly, we will have to use UNSAFEPERMISSION_SET for our assembly and so the database using that assembly has to be TRUSTWORTHY.

ALTER DATABASE TestDB3 SET TRUSTWORTHY ON WITH ROLLBACK IMMEDIATE;

As we are using the System.DirectoryServices assembly, we have to register it in our database prior registering our assembly, otherwise we will not be able to register it.

As we use a stored procedure for querying AD, then we cannot work directly with the result further (OK.. On Denali it will be possible thanks to the EXECUTE WITH RESULT SETS. On SQL Server 2005 and 2008 we could store the results e.g. to table variable to temp table and then work with the results as normally. From the CLR code we can see, that the result set contains all the AD properties we have passed as parameter and the order is exactly the same as in the input parameter. The data type of each returned column is nvarchar with length which was specified in the properties list. If no length was specified or the length was less than one or grater than 4000 then the return type is nvarchar(4000).

From the examples above we can see, that once we create CLR stored procedures for querying the AD, the queries to AD are quite easy. Comparing the the T-SQL and Linked Server or OPENROWSET solution we have much greater possibilities and what is most important, we are not limited to 1000 results from our query, so we can easily query all the object in AD.

40 thoughts on “Querying Active Directory on SQL Server using CLR”

Awesome post!
I have been hunting for this information forever! I’m getting an erro though when I try to return 50+ fields from AD:
Msg 701, Level 17, State 123, Procedure usp_QueryAD, Line 0

There is insufficient system memory in resource pool ‘default’ to run this query.

I can get back 381 rows before I get the error. This is on SQL 2008 developer edition on Vista Enterprise. I would like to know this wont be a problem when I put in on a live test server. Any help would be appreciated!

I’ve built a detailed switch statement that I can send you via email which sets the field lengths to more accurate values depending on the AD Attribute. By shortening the fields, I’m able to get the entire 50+ field result set for 1200+ users. It’s yours if you want it.

the true is, tha I never need to retrieve such big amount of attributes from AD. In that case shortenning the return fileds helps.

In that example I have chosen the nvarchar(400) as return type, as this is the default return type, when you use the T-SQL solution. The OPENQUERY or OPENROWSET returns nvarchar(4000).

If you do not need an universal procedure, you can customize it as you have described and return exact data types you want, back to the SQL Server.

Another option, could be pass return lengths of the fileds tot he CLR stored procedure. eg. when specifying the list of attributes, it could be possible to specify return lengths. If not specified, then the default one could be used. It could be passed e.g. as comma and semicolon separated values 'sn,50;cn,50;ADsPath'.

Then in the CLR proc you first split the list by semicolons and then by commas.
In this way you will have again an universal stored procedure.

When I will have a little more time, I will update the sample with this approach.

But as mentioned above, if you know what you exactly need, you simply create a highly customized and optimized procedure for your exact purpose and it will give you better results and for sure also better performance.

Hello There! First, thanks for this post. It was very helpfull … but now I’m having a problem …

I follow this procedure by the book, and I have seen the full users only at the first time 😉 After that i´m receiving the following error:

——
Msg 6532, Level 16, State 49, Procedure usp_QueryAD, Line 0
.NET Framework execution was aborted by escalation policy because of out of memory.
System.Threading.ThreadAbortException: Thread was being aborted.”
——

Even when I add the a user to the filter, sometimes it works, sometimes it not …

Hi, the change of value from 255 to 512 in ‘@propertiesToLoad nvarchar(255) is related to the length of passed properties to be retrieved. Let’s say, if you want to receive name of 26 properties and an average length of the property will be longer than 8 characters, then with this declaration you will not be able to pass the list (as it is limited to 255 chars). In this case you can change the declaration even to nvarchar(4000) if necessary.

The concept of “passing length of fields” is as follow:
Say you have sn;cn;ADsPath then if you do not specify lengths for that properties, then each property will be returned as nvarchar(4000) (result set returned by the stored proc). This can be problematic for large amount of properties as you can start having problems with out of memory.

In case of receiving Out Of Memory errors when receiving large amount of properties, you can also try to lower the pageSize which specifies number of items returned by single round trip to AD.

For that purpose the “length of fields” was introduced, where you specify the length of field after comma. So if you pass 'sn,30;cn,50;ADsPath,1000', it means that sn will be returned as nvarchar(30), cn returned as nvarchar(50) and ADsPath as nvarchar(1000).

I have tested and working this solution for about 50 properties and retrieving about 25000 AD entries without any issues.

Without setting a lenght on the fields it works great for the code/properties below. When, for example, I set the same values that i giving to the table fields it doesn’t work … and this message appears:

I dont know if I did something wrong, as I´m new in this. From the Visual Studio i opened and runned the project to create the .dll file. The ‘NetFramework Register’, and so on, it was copy paste from the site …

I´ve been testing and making some changes in order to understand how it works, and … is it supposed to work when the length field is lower than the data retrieve? Example:

@propertiesToLoad = ‘sAMAccountName;displayName;mail;homeMDB’ — it works

@propertiesToLoad = ‘sAMAccountName,1;displayName,1;mail;homeMDB’ — the sAMAccountName retrieves data higher than 1char; the displayName
retrieves only 1char (the data is truncate)

There was a typo and instead correct TryParse(propDetails[1], out len) there was TryParse(propDetails[i], out len). So there was “i” instead of 1 used as index to array what caused the index out of range exception.

The code is now fixed both in the blog post and also in the project available for download.

Using the same code that you describe above, do you know how can I add a column with the ‘account is locked out’ info? I´ve been searching over the internet how to do it, and discover that are some attributes that we can use, like ‘msDS-User-Account-Control-Computed’, ‘useraccountcontrol’, but the samples that i found are in VB, etc …

I already have the a column saying if the account is enable or disable. Have to use bitwise (never heard about it) and write some code like: case when useraccountcontrol & @ADS_UF_ACCOUNTDISABLE 0 then … it works …

Hi Pavel,
this is a great post thanks for the great work! Was testing a number of solution provided on the net but really favoring your approach.
Although I do have an issue with the routine and maybe you can give am hint please?
If I query the AD and want to have the “MemberOf” a user groups , the length is limited to 4000.
It cuts of the string by 4000 char. Also tried to pass the length of 8000 in the SP call but again it cuts is off.
Have seen in the C# code that the length is checked for 4000.
Do you have a solution for that? I am not very familiar with C#.
Apprechiate any hints!
Regards Beat

effect of the change will be, that if you do not specify length of a property to load, or you specify a value out of the interval it will set the internal variable len = -1. As a result it will declare the returning data type as nvarchar(max), so everything should easily fit into it.

You can test it and will see, if it helps. Currently I’m not able to test the change by myself, but when I will have a time I will update the procedure to be able to handle the nvarchar(max) generally.

Hi Pavel,
this is a great post and was testing a number of solution provided on the net but really favoring your approach.
Although I do have an issue with the routine and maybe you can give am hint please?
If I query the AD and want to have the “MemberOf” a user groups , the length is limited to 4000.
It cuts of the string by 4000 char. Also tried to pass the length of 8000 in the SP call but again it cuts is off.
Have seen in the C# code that the length is checked for 4000.
Do you have a solution to that? I am not very familiar with C#.
Appreciate any hints!
Regards Beat

Hi Pavle,
Hi Pavel,
this is a great post! Was testing a number of solution provided on the net but really favoring your approach.
Although I do have an issue with the routine and maybe you can give am hint please?
If I query the AD and want to have the “MemberOf” a user groups , the length is limited to 4000.
It cuts of the string by 4000 char. Also tried to pass the length of 8000 in the SP call but again it cuts is off.
Have seen in the C# code that the length is checked for 4000.
Do you have a solution to that? I am not very familiar with C#.
Appreciate any hints!
Regards Beat

It seems, that you receive the SID and GUID as array of bytes representing the SIG and GUID strings. The current sample here handles everything as string and simply calls .ToString() method to covert returned object values as string. As System.Byte[] array is not convertible directly to string, it returns a class name.

For general handling, it would need a deeper refactoring of the code to be able to handle different data types.

However for quick solution to return a proper string values, when your string data are being returned as System.Byte[], a below alignment to the code should help.

But note, it will work only if you return values of string AD properties. Otherwise if you return real binary values, then it will try to convert those binary values to Unicode string.
For returning binary values the code would require bigger refactoring.

I post posting the method to convert string received as array of bytes in the result back to strings. Of course, if there are some other data types encoded, then those has to be processed separately.

Only related to the Guid. If you are receiving it as byte[] array, maybe it would be enough to use the Orverloaded constructor of the Guid structure.

new Guid((bytep[])props[0]).ToString().

But this covers only single case. To cover other data types, it would be better to extend the solution to accept also data types, when specifying properties to be retrieved and then convert the returned data accordingly.

And then switch special properties like objectGUID, objectSid, ….
In default case i will always do what the script already does.

To my Opinoin this is the easiest way to get the result.
The only thing to do afterwards is a special routine for each special column in ad, like i have done with the octetstring routine, and the code can grow with its funcionality.

I will send it over to you when i am ready. Solution before was just the POC if i can get the value correctly.

Hi, then use the System.DirectoryServices.dll from .Net framework 4.0 and also update the project target framework to .Net 4.0. Once you compile the assembly for the .Net 4.0, you will be able to use it in SQL Server 2012.