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.
So here is the CLR class for querying AD.
public class ActiveDirectory
{
/// <summary>
/// Queries Active directory according provided parameters
/// Current user credentials are used for authentication
/// </summary>
/// <param name="adRoot">AD Root for querying AD</param>
/// <param name="filter">Filter to be used for querying</param>
/// <param name="searchScope">Scope to be used for queryingg</param>
/// <param name="propertiesToLoad">List of properties to return</param>
/// <param name="pageSize">Represents a PageSise for the paged search of AD</param>
/// <param name="rowsLimit">Rrepresent limit for numbers of rows returned. NULL or value less than 1 represents unlimited</param>
[Microsoft.SqlServer.Server.SqlProcedure]
public static void QueryAD(string adRoot, string filter, string propertiesToLoad, string searchScope, int pageSize, SqlInt32 rowsLimit)
{
SearchAD(null, null, null, adRoot, filter, searchScope, propertiesToLoad, pageSize, rowsLimit);
}
/// <summary>
/// Queries Active directory according provided parameters
/// </summary>
/// <param name="userName">UserName to be used to authenticate AD</param>
/// <param name="password">Password to be used to authenticate to AD</param>
/// <param name="adRoot">AD Root for querying AD</param>
/// <param name="filter">Filter to be used for querying</param>
/// <param name="searchScope">Scope to be used for queryingg</param>
/// <param name="propertiesToLoad">List of properties to return</param>
/// <param name="pageSize">Represents a PageSise for the paged search of AD</param>
/// <param name="rowsLimit">Rrepresent limit for numbers of rows returned. NULL or value less than 1 represents unlimited</param>
[Microsoft.SqlServer.Server.SqlProcedure]
public static void QueryADUName(string userName, string password, string adRoot, string filter, string propertiesToLoad, string searchScope, int pageSize, SqlInt32 rowsLimit)
{
SearchAD(userName, password, null, adRoot, filter, searchScope, propertiesToLoad, pageSize, rowsLimit);
}
/// <summary>
/// Queries Active directory according provided parameters
/// </summary>
/// <param name="userName">UserName to be used to authenticate AD</param>
/// <param name="password">Password to be used to authenticate to AD</param>
/// <param name="authType">Authentication type to be used to authenticate to AD</param>
/// <param name="adRoot">AD Root for querying AD</param>
/// <param name="filter">Filter to be used for querying</param>
/// <param name="searchScope">Scope to be used for queryingg</param>
/// <param name="propertiesToLoad">List of properties to return</param>
/// <param name="pageSize">Represents a PageSise for the paged search of AD</param>
/// <param name="rowsLimit">Rrepresent limit for numbers of rows returned. NULL or value less than 1 represents unlimited</param>
[Microsoft.SqlServer.Server.SqlProcedure]
public static void QueryADAuth(string userName, string password, string authType, string adRoot, string filter, string propertiesToLoad, string searchScope, int pageSize, SqlInt32 rowsLimit)
{
SearchAD(userName, password, authType, adRoot, filter, searchScope, propertiesToLoad, pageSize, rowsLimit);
}
private static DirectoryEntry GetRootEntry(string adRoot, string userName, string password, string authType)
{
if (userName == null)
return new DirectoryEntry(adRoot);
else if (authType == null)
return new DirectoryEntry(adRoot, userName, password);
else
{
AuthenticationTypes at;
if (TryParseEnum<AuthenticationTypes>(authType, true, out at))
return new DirectoryEntry(adRoot, userName, password, at);
else
throw new System.InvalidCastException(string.Format("authType must be one of '{0}'", GetEnumNames<AuthenticationTypes>()));
}
}
private static string GetEnumNames<T>()
{
string[] names = Enum.GetNames(typeof(T));
StringBuilder sb = new StringBuilder();
for (int i = 0; i < names.Length; i++)
{
if (i > 0)
sb.Append(',');
sb.Append(names[i]);
}
return sb.ToString();
}
private static bool TryParseEnum<T>(string value, bool ignoreCase, out T outEnum)
{
try
{
outEnum = (T)Enum.Parse(typeof(T), value, ignoreCase);
return true;
}
catch
{
outEnum = (T)Enum.GetValues(typeof(T)).GetValue(0);
return false;
}
}
/// <summary>
/// Searches Active Directory according provided parameters
/// </summary>
/// <param name="userName">UserName to be used to authenticate AD</param>
/// <param name="password">Password to be used to authenticate to AD</param>
/// <param name="authType">Authentication type to be used to authenticate to AD</param>
/// <param name="adRoot">AD Root for querying AD</param>
/// <param name="filter">Filter to be used for querying</param>
/// <param name="searchScope">Scope to be used for queryingg</param>
/// <param name="propertiesToLoad">List of properties to return</param>
/// <param name="pageSize">Represents a PageSise for the paged search of AD</param>
/// <param name="rowsLimit">Rrepresent limit for numbers of rows returned. NULL or value less than 1 represents unlimited</param>
private static void SearchAD(string userName, string password, string authType, string adRoot, string filter, string searchScope, string propertiesToLoad, int pageSize, SqlInt32 rowsLimit)
{
string[] properties = propertiesToLoad.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
SqlMetaData[] recordMetaData = new SqlMetaData[properties.Length];
SearchScope scope;
int limit = rowsLimit.IsNull ? 0 : rowsLimit.Value;
int rowsCount = 0;
if (rowsLimit > 0 && pageSize > limit)
pageSize = limit;
if (!TryParseEnum<SearchScope>(searchScope, true, out scope))
throw new System.InvalidCastException(string.Format("searchScope must be one of '{0}'", GetEnumNames<SearchScope>()));
//Trim properties and prepare result set metadata, also process specified lengths
for (int i = 0; i < properties.Length; i++)
{
string[] propDetails = properties[i].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
string propName = propDetails[0].Trim();
int len;
if (propDetails.Length < 2 || !int.TryParse(propDetails[1], out len) || len < 1 || len > 4000)
len = 4000;
recordMetaData[i] = new SqlMetaData(properties[i], System.Data.SqlDbType.NVarChar, len);
properties[i] = propName;
}
//Get Root Directory Entry
using (DirectoryEntry rootEntry = GetRootEntry(adRoot, userName, password, authType))
{
//Create a directory searcher with aproperiate filter, properties and search scope
using (DirectorySearcher ds = new DirectorySearcher(rootEntry, filter, properties, scope))
{
ds.PageSize = pageSize; //Set Page Size - without this we will not do a paged search and we will be limiited to 1000 results
//find all object from the rood, according the filter and search scope
using (SearchResultCollection results = ds.FindAll())
{
SqlDataRecord record = new SqlDataRecord(recordMetaData);
//Start pushing of records to client
SqlContext.Pipe.SendResultsStart(record);
foreach (SearchResult result in results)
{
record = new SqlDataRecord(recordMetaData);
for (int i = 0; i < properties.Length; i++)
{
ResultPropertyValueCollection props = result.Properties[properties[i]];
if (props.Count == 1) //if property collection contains single vallue, set the record field to that value
record.SetSqlString(i, props[0].ToString());
else if (props.Count == 0) //if property collection doesn't contain any value, set record field to NULL
record.SetSqlString(i, SqlString.Null);
else //In case of multiple value, separate the values by commas
{
StringBuilder sb = new StringBuilder();
bool firstItem = true;
foreach (object prop in props)
{
if (!firstItem)
sb.Append(',');
else
firstItem = false;
sb.Append(prop.ToString());
}
record.SetSqlString(i, sb.ToString());
}
}
//send record to client
SqlContext.Pipe.SendResultsRow(record);
//if rowsLimit was reached, break the loop
if (++rowsCount == rowsLimit)
break;
}
//stop sending records to client
SqlContext.Pipe.SendResultsEnd();
}
}
}
}
}
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 UNSAFE PERMISSION_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.
--Register the NetFramework System.DirectoryServices assembly CREATE ASSEMBLY [System.DirectoryServices] AUTHORIZATION [dbo] FROM 'C:WindowsMicrosoft.NETFrameworkv2.0.50727System.DirectoryServices.dll' WITH PERMISSION_SET = UNSAFE GO --Register our assembly with ActiveDirectory CLR Stored Procedures CREATE ASSEMBLY [ADServices] AUTHORIZATION [dbo] FROM 'C:CLRADServices.dll' WITH PERMISSION_SET = UNSAFE GO
Once we have successfully registered those assemblies, we can create the CLR Stored procedures in our DB.
--==========================================================================
-- @adRoot - root of searching eg. 'LDAP://OU=Sales,DC=Fabricam,DC=com'
-- @filter - filter to be used for searching eg. '(&(objectCategory=group)'
-- @propertiesToLoad - list of properties to be retrieved eg. 'cn,50;ADsPath'
-- - properties are separated by a semicolon and
-- - and comma can be used to specify return length of the property
-- @searchScope - scope to be used for searching: {Base,OneLevel,Subtree}
-- @pageSize - specifies the PageSize for paged search - default is 1000
-- it is possible to lower the value if there is a problem
-- retrieving such amount of records at once.
-- @rowsLimit - represents maximum number of rows returned.
-- NULL or value less than 1 represents unlimited
--==========================================================================
CREATE PROCEDURE usp_QueryAD
@adRoot nvarchar(255),
@filter nvarchar(255),
@propertiesToLoad nvarchar(255),
@searchScope nvarchar(8),
@pageSize int = 1000,
@rowsLimit int = 0
AS
EXTERNAL NAME [ADServices].[ActiveDirectory].[QueryAD]
GO
--==========================================================================
-- @userName - username to be used for authentificaiton to AD
-- @password - password to be used for authentification to AD
-- @adRoot - root of searching eg. 'LDAP://OU=Sales,DC=Fabricam,DC=com'
-- @filter - filter to be used for searching eg. '(&(objectCategory=group)'
-- @propertiesToLoad - list of properties to be retrieved eg. 'cn,50;ADsPath'
-- - properties are separated by a semicolon and
-- - and comma can be used to specify return length of the property
-- @searchScope - scope to be used for searching: {Base,OneLevel,Subtree}
-- @pageSize - specifies the PageSize for paged search - default is 1000
-- it is possible to lower the value if there is a problem
-- retrieving such amount of records at once.
-- @rowsLimit - represents maximum number of rows returned.
-- NULL or value less than 1 represents unlimited
--==========================================================================
CREATE PROCEDURE usp_QueryADUname
@userName nvarchar(255),
@password nvarchar(255),
@adRoot nvarchar(255),
@filter nvarchar(255),
@propertiesToLoad nvarchar(255),
@searchScope nvarchar(8),
@pageSize int = 1000,
@rowsLimit int = 0
AS
EXTERNAL NAME [ADServices].[ActiveDirectory].[QueryADUName]
GO
--==========================================================================
-- @userName - username to be used for authentificaiton to AD
-- @password - password to be used for authentification to AD
-- @authType - Authentification Type to be used for AD Authentification
-- {None,Secure,Encryption,SecureSocketsLayer,ReadonlyServer,Anonymous,
-- FastBind,Signing,Sealing,Delegation,ServerBind}
-- @adRoot - root of searching eg. 'LDAP://OU=Sales,DC=Fabricam,DC=com'
-- @filter - filter to be used for searching eg. '(&(objectCategory=group)'
-- @propertiesToLoad - list of properties to be retrieved eg. 'cn,50;ADsPath'
-- - properties are separated by a semicolon and
-- - and comma can be used to specify return length of the property
-- @searchScope - scope to be used for searching: {Base,OneLevel,Subtree}
-- @pageSize - specifies the PageSize for paged search - default is 1000
-- it is possible to lower the value if there is a problem
-- retrieving such amount of records at once.
-- @rowsLimit - represents maximum number of rows returned.
-- NULL or value less than 1 represents unlimited
--==========================================================================
CREATE PROCEDURE usp_QueryADAuth
@userName nvarchar(255),
@password nvarchar(255),
@authType nvarchar(20),
@adRoot nvarchar(255),
@filter nvarchar(255),
@propertiesToLoad nvarchar(255),
@searchScope nvarchar(8),
@pageSize int = 1000,
@rowsLimit int = 0
AS
EXTERNAL NAME [ADServices].[ActiveDirectory].[QueryADAuth]
GO
After successful creation of the stored procedures we can start querying the AD: The filter parameter is a LDAP filter according the Search Filter Syntax.
--Query AD Groups --Result set will contain cn - common name, ADsPath EXECUTE [usp_QueryAD] @adRoot = 'LDAP://DC=Fabricam,DC=com' ,@filter = '(&(objectCategory=group))' ,@propertiesToLoad = 'cn,50;ADsPath' ,@searchScope = 'subtree' GO --Query users of Sales organization unit --Result set will containt sn - surname, cn - common name and ADsPath EXECUTE [usp_QueryAD] @adRoot = 'LDAP://OU=Sales,DC=Fabricam,DC=com' ,@filter = '(&(objectCategory=user)(objectClass=user))' ,@propertiesToLoad = 'sn,cn,ADsPath' ,@searchScope = 'subtree' GO --Query al users belonging to Group "Sales Representatives" and providing user name and password --for AD Authetication including authetication type --Result set will containt sn - surname, cn - common name and ADsPath EXECUTE [TestDB3].[dbo].[usp_QueryADAuth] @userName = 'FabricamADUserName' ,@password = 'ADuserNamePassword' ,@authType = 'secure' ,@adRoot = 'LDAP://DC=Fabricam,DC=com' ,@filter = '(&(objectCategory=user)(objectClass=user)(memberOf=CN=Sales Representatives,OU=Security Groups,OU=Groups,DC=Fabricam,DC=com))' ,@propertiesToLoad = 'sn,100;cn,200;ADsPath,1000' ,@searchScope = 'subtree' GO
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).
--Query All users from AD, store them in a table variable and then
--select all users with their cn (common name) starting by "C"
DECLARE @result TABLE (
sn nvarchar(100),
cn nvarchar(200),
ADsPath nvarchar(1000)
)
INSERT INTO @result
EXECUTE [TestDB3].[dbo].[usp_QueryADAuth]
@userName = 'FabricamADUserName'
,@password = 'ADuserNamePassword'
,@authType = 'secure'
,@adRoot = 'LDAP://DC=Fabricam,DC=com'
,@filter = '(&(objectCategory=user)(objectClass=user))'
,@propertiesToLoad = 'sn,100;cn,200;ADsPath,1000'
,@searchScope = 'subtree'
SELECT
*
FROM @result
WHERE cn LIKE 'C%'
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.
If you are interested, you can download sample ADServices Visual C# 2010 Express project. It contains all the latest updates I have mentioned.
Here you can download an updated sample PPSqlDirectoryServices Visual C# 2010 project with all the lasted updates implemented.
Pavel's SQL Pages 
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!
Chris
Hi,
you can try to lower the page size in the private static void SearchAD procedure.
If it returns 381 rows, try to set it to 350. It could be also possible to pass the Page Size as an parameter to the stored proc.
Maybe I will also update the sample with the Page Size as a parameter.
Pavel.
That’s better, But I still cant pull all the fields I would like without running out of memory. Even with the page size set to 1. I did some research in MSDN and came across the following article:
http://msdn.microsoft.com/en-us/library/ms675090(VS.85).aspx
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.
Also, certain fields that I want like lastLogon are in an interval format. I used the code below to convert them to a useful datetime value before returning to SQL Server.
// try
// {
// Int64 largeInteger = Convert.ToInt64(props[0]);
// record.SetSqlString(i, DateTime.FromFileTime(largeInteger).ToString());
// }
// catch
// {
// record.SetSqlString(i, SqlString.Null);
// }
I’d be happy to share the rest of my changes with you if you like.
Thanks,
Chris
Hi chris,
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.
Thanks for the Ideas.
First I’d like to thank Pavel Pawlowski for this work, it’s a great job and I’ve learned a lot reading it.
Chris, if you’re still trying to get around the properties limit, try changing the schema on the USP definition.
It’s limited to 255 chars and that will only give you like 20 columns.
I’ve bumped mine up to 512 and it’s enough to get all I need.
Charles and Chris,
Finally I had a time to post an update to this article with the modification I was describing in my comments here.
So now the code is updated so it supports providing the lengths for the AD properties like
'sn,50;cn,50;ADsPath'.Great post Pavel. With our developer’s help, we we had this up and running in under 30 minutes and it runs like a champ.
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 …
@filter = ‘(&(objectCategory=person)(objectClass=user)(sAMAccountName=XXXX))
I already change the value from 255 to 512 (like Charles Torry said), but and i’m getting the same error. Is this ‘@propertiesToLoad nvarchar(255)’ where i have to change, right?
I’m also having some trouble to understand the ‘passing lengths of the fileds’ “concept” … When I place a value like ‘displayName,100’ I receive the following error:
————-
Msg 6522, Level 16, State 1, Procedure usp_QueryAD, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate “usp_QueryAD”:
System.IndexOutOfRangeException: Index was outside the bounds of the array.
————-
Can you help me with this?
Thanks!
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;ADsPaththen if you do not specify lengths for that properties, then each property will be returned asnvarchar(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
pageSizewhich 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 thatsnwill be returned asnvarchar(30),cnreturned asnvarchar(50)andADsPathasnvarchar(1000).I have tested and working this solution for about 50 properties and retrieving about 25000 AD entries without any issues.
Pavel, thanks for your reply!
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:
——-
Msg 6522, Level 16, State 1, Procedure usp_QueryAD, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate “usp_QueryAD”:
System.IndexOutOfRangeException: Index was outside the bounds of the array.
——
This is my code:
—————————————–
–ALTER PROCEDURE usp_QueryAD
— @adRoot nvarchar(255),
— @filter nvarchar(255),
— @propertiesToLoad nvarchar(4000),
— @searchScope nvarchar(8),
— @pageSize int = 500,
— @rowsLimit int = 0
— AS EXTERNAL NAME [ADServices].[ActiveDirectory].[QueryAD]
DBCC FREESYSTEMCACHE (‘ALL’)
DECLARE @result TABLE
(
Username nvarchar(50),
DisplayName nvarchar(100),
Mail nvarchar(100),
homeMDB nvarchar(300)
)
INSERT INTO @result
EXECUTE [CSD].[dbo].[usp_QueryAD]
@adRoot = ‘LDAP://OU=Users, OU=XXX, DC=XXX,DC=internal,DC=XXX, DC=com’,
@filter = ‘(&(objectCategory=person)(objectClass=user))’,
@propertiesToLoad = ‘sAMAccountName;displayName;mail;homeMDB’,
@searchScope = ‘subtree’
SELECT *
FROM @result
—————————————-
What changes would you make for this to work?
Have patience 🙂
Hmms.. Maybe a bug in the code posted.. Did you use the CLR code from this site, or did you downloaded the complete project?
I will check and review the code, if there is no typo.
I´ve downloaded the ‘PPSqlDirectoryServices’ project.
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)
@propertiesToLoad
= ‘sAMAccountName,1;displayName,1;mail,1;homeMDB,1’ — A .NET Framework error occurred during execution of user-defined routine or aggregate “usp_QueryAD” error!
@propertiesToLoad
= ‘sAMAccountName;displayName;mail,1;homeMDB,1’ — A .NET Framework error occurred during execution of user-defined routine or aggregate “usp_QueryAD” error!
Your help would be greatfull!
Hi,
I can confirm, that there was a small bug in the BlogPost Code and also in the Project available for download.
The issue was in below line:
There was a typo and instead correct
TryParse(propDetails[1], out len)there wasTryParse(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.
Now the code should work correctly
Anyway thanks for letting me know about the issue.
😉 The problems are gone! Thanks for your help Pavel!
Good to hear. 🙂
Pavel,
i´m trying to create a temp table with the user account info …
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 …
Thanks again.
I did it 🙂
declare @UF_LOCKOUT int
set @UF_LOCKOUT = ’16’
case when convert(int,AC_Locked) & @UF_LOCKOUT = @UF_LOCKOUT Then ‘The account is locked’ else ‘The account is not locked’ end
The ‘AC_Locked’ column is the “msDS-User-Account-Control-Computed” attribute.
[…] http://www.pawlowski.cz/2011/04/querying-active-directory-sql-server-clr/ […]
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
Sample call to SP:
execute DirectoryServices.usp_QueryAD
@adRoot = ‘LDAP://DC=so,DC=local’,
@filter = ‘(&(objectCategory=user)(objectClass=user)(memberOf=CN=SampelGroup,OU=Main,OU=Office,OU=GROUPS,DC=xx,DC=xxxxx))’,
@propertiesToLoad = ‘ MemberOf,8000;
sn,255;
LastName,255;
GivenName,255;
FirstName,255;
sAMAccountName,255;
DomainAccount,255;
cn,255;
ADsPath,1000’,
@searchScope = ‘subtree’,
@pageSize = 1000,
@rowsLimit = 0
Hi,
probably the easies change would be, if you locate a part of C# code:
if (propDetails.Length < 2 || !int.TryParse(propDetails[1], out len) || len 4000) len = 4000;and update it to:
if (propDetails.Length < 2 || !int.TryParse(propDetails[1], out len) || len 4000) len = -1;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
Sample call to SP:
execute DirectoryServices.usp_QueryAD
@adRoot = ‘LDAP://DC=so,DC=local’,
@filter = ‘(&(objectCategory=user)(objectClass=user)(memberOf=CN=SampelGroup,OU=Main,OU=Office,OU=GROUPS,DC=xx,DC=xxxxx))’,
@propertiesToLoad = ‘ MemberOf,8000;
sn;
LastName,255;
GivenName,255;
FirstName,255;
sAMAccountName,255;
DomainAccount,255;
cn,255;
ADsPath,1000’,
@searchScope = ‘subtree’,
@pageSize = 1000,
@rowsLimit = 0
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
Hi,
as I have mentioned above. If you implement the change I’ve described, you should be able to query the whole string using:
execute DirectoryServices.usp_QueryAD
@adRoot = ‘LDAP://DC=so,DC=local’,
@filter = ‘(&(objectCategory=user)(objectClass=user)(memberOf=CN=SampelGroup,OU=Main,OU=Office,OU=GROUPS,DC=xx,DC=xxxxx))’,
@propertiesToLoad = ‘ MemberOf,-1;
sn,255;
LastName,255;
GivenName,255;
FirstName,255;
sAMAccountName,255;
DomainAccount,255;
cn,255;
ADsPath,1000′,
@searchScope = ‘subtree’,
@pageSize = 1000,
@rowsLimit = 0
Hi Pavel,
thanks for your quick reply!
Excellent, it does the job!
Beat
Good to know it is tested and it works. I will then update the sources also to support this directly. 🙂
Hi Pavel,
how can i retrieve the objectSid and objectGUID from AD over your CLR?
When i try to retrieve them i just get System.Byte[] as output.
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.
if (props.Count == 1) //if property collection contains single vallue, set the record field to that value if (props[0] is byte[]) record.SetSqlString(i, Encoding.Unicode.GetString((byte[])props[0])); else record.SetSqlString(i, props[0].ToString()); else if (props.Count == 0) //if property collection doesn't contain any value, set record field to NULL record.SetSqlString(i, SqlString.Null); else //In case of multiple value, separate the values by commas { StringBuilder sb = new StringBuilder(); bool firstItem = true; foreach (object prop in props) { if (!firstItem) sb.Append(','); else firstItem = false; if (prop is byte[]) sb.Append(Encoding.Unicode.GetString((byte[])prop)); else sb.Append(prop.ToString()); } record.SetSqlString(i, sb.ToString()); }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.
Hi Pavel,
thank you for your reply, but it doesnt work like this because you will get some unicode characters but no GUID.
I now found a way to handle it:
Added a function which builds the GUID from an Octetstring:
public static Guid ConvertOctetStringToGuid(String gUID)
{
String pattern = @"^(?i)[0-9A-F]{32}";
if (Regex.IsMatch(gUID, pattern))
{
UInt32 a = Convert.ToUInt32((gUID.Substring(6, 2) +
gUID.Substring(4, 2) + gUID.Substring(2, 2) + gUID.Substring(0, 2)), 16);
UInt16 b = Convert.ToUInt16((gUID.Substring(10, 2) + gUID.Substring(8, 2)), 16);
UInt16 c = Convert.ToUInt16((gUID.Substring(14, 2) + gUID.Substring(12, 2)), 16);
Byte d = (Byte)Convert.ToUInt16(gUID.Substring(16, 2), 16);
Byte e = (Byte)Convert.ToUInt16(gUID.Substring(18, 2), 16);
Byte f = (Byte)Convert.ToUInt16(gUID.Substring(20, 2), 16);
Byte g = (Byte)Convert.ToUInt16(gUID.Substring(22, 2), 16);
Byte h = (Byte)Convert.ToUInt16(gUID.Substring(24, 2), 16);
Byte i = (Byte)Convert.ToUInt16(gUID.Substring(26, 2), 16);
Byte j = (Byte)Convert.ToUInt16(gUID.Substring(28, 2), 16);
Byte k = (Byte)Convert.ToUInt16(gUID.Substring(30, 2), 16);
return new Guid(a, b, c, d, e, f, g, h, i, j, k);
}
else
{
throw new InvalidOperationException("Input value violates octet string validation pattern (32-character hexidecimal string expected).");
}
}
Thanks to http://blog.msresource.net/2013/09/05/guids-and-octet-strings-converting-between-them/
and then have done it like this:
for (int i = 0; i < properties.Length; i++)
{
ResultPropertyValueCollection props = result.Properties[properties[i]];
if (props.Count == 1) //if property collection contains single vallue, set the record field to that value
if (props[0] is byte[])
record.SetSqlString(i, ConvertOctetStringToGuid(BitConverter.ToString((byte[])props[0]).Replace("-", string.Empty)).ToString());
//Encoding.Unicode.GetString((byte[])props[0]));
else
record.SetSqlString(i, props[0].ToString());
else if (props.Count == 0) //if property collection doesn't contain any value, set record field to NULL
record.SetSqlString(i, SqlString.Null);
else //In case of multiple value, separate the values by commas
{
StringBuilder sb = new StringBuilder();
bool firstItem = true;
foreach (object prop in props)
{
if (!firstItem)
sb.Append(',');
else
firstItem = false;
if (prop is byte[])
sb.Append(ConvertOctetStringToGuid(BitConverter.ToString((byte[])props[0]).Replace("-", string.Empty)).ToString());
else
sb.Append(prop.ToString());
}
record.SetSqlString(i, sb.ToString());
}
}
Hi Jürgen,
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.
As there are many cases when you can receive a byte array.
Hi Pavel,
what will do the next days is following:
I wil interate the property names:
foreach (string propertyKey in
searchResult.Properties.PropertyNames)
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.
new Guid((bytep[])props[0]).ToString()
Also tried it but doesn’t help, you will get the error that it’s not possible to convert an object to string.
Reason is:
objectGuid is 128bit in AD so 2 times 64bit and you do not have a data type for it.
The only way i saw and also found on internet is to do it with bit converter.
Just with bit converter.tostring you get a format like FF-FF-FF-FF…
new Guid expects 32 chars, means FFFFFFFF…..
Took me the half day to understand this really nice objectGUID behavior.
Hi,
if you are receiving this error, then it will be some other issue or wrong parentheses or something.
Guid is in general 128 bit. and if the Array is 16 bytes, then it should be the byte array by which the Guid should be able to initialize.
As in this snippet. It creates new Guid, converts it to a byte array and then creates a new instance of the same Guid from the byte array. Finally converts it to string.
Hi Pavel,
I’ve now finished it.
Changed the code to:
foreach (SearchResult result in results)
{
record = new SqlDataRecord(recordMetaData);
for (int i = 0; i < properties.Length; i++)
{
ResultPropertyValueCollection props = result.Properties[properties[i]];
record.SetSqlString(i, properties[i].ToString());
// Take Poperty name and put it to lower case for comparison
switch (properties[i].ToLower())
{
// Case out special System.Byte[] Value for correct appearance in SQL - if it occours
case "objectguid":
record.SetSqlString(i, ConvertOctetStringToGuid(BitConverter.ToString((byte[])props[0]).Replace("-", string.Empty)).ToString());
break;
// Case out special System.Byte[] Value for correct appearance in SQL - if it occours
case "objectsid":
record.SetSqlString(i,ConvertSidToString((byte[])props[0]));
break;
//case "":
// Use special function to do it
//break;
// If no special value - just convert it to string
default:
//if property collection contains single vallue, set the record field to that value
if (props.Count == 1)
record.SetSqlString(i, props[0].ToString());
//if property collection doesn't contain any value, set record field to NULL
else if (props.Count == 0)
record.SetSqlString(i, SqlString.Null);
//In case of multiple value, separate the values by commas
else
{
StringBuilder sb = new StringBuilder();
bool firstItem = true;
foreach (object prop in props)
{
if (!firstItem)
sb.Append(',');
else
firstItem = false;
if (prop is byte[])
sb.Append(ConvertOctetStringToGuid(BitConverter.ToString((byte[])props[0]).Replace("-", string.Empty)).ToString());
else
sb.Append(prop.ToString());
}
record.SetSqlString(i, sb.ToString());
}
break;
}
}
//send record to client
SqlContext.Pipe.SendResultsRow(record);
//if rowsLimit was reached, break the loop
if (++rowsCount == rowsLimit)
break;
}
To my opinion its now generic enough as before and special enough to fulfill other requierements.
Also added these function for AD SIDs and AD GUIDs:
private static string ConvertSidToString(byte[] objectSid)
{
SecurityIdentifier si = new SecurityIdentifier(objectSid, 0);
return si.ToString();
}
public static Guid ConvertOctetStringToGuid(String gUID)
{
String pattern = @"^(?i)[0-9A-F]{32}";
if (Regex.IsMatch(gUID, pattern))
{
UInt32 a = Convert.ToUInt32((gUID.Substring(6, 2) +
gUID.Substring(4, 2) + gUID.Substring(2, 2) + gUID.Substring(0, 2)), 16);
UInt16 b = Convert.ToUInt16((gUID.Substring(10, 2) + gUID.Substring(8, 2)), 16);
UInt16 c = Convert.ToUInt16((gUID.Substring(14, 2) + gUID.Substring(12, 2)), 16);
Byte d = (Byte)Convert.ToUInt16(gUID.Substring(16, 2), 16);
Byte e = (Byte)Convert.ToUInt16(gUID.Substring(18, 2), 16);
Byte f = (Byte)Convert.ToUInt16(gUID.Substring(20, 2), 16);
Byte g = (Byte)Convert.ToUInt16(gUID.Substring(22, 2), 16);
Byte h = (Byte)Convert.ToUInt16(gUID.Substring(24, 2), 16);
Byte i = (Byte)Convert.ToUInt16(gUID.Substring(26, 2), 16);
Byte j = (Byte)Convert.ToUInt16(gUID.Substring(28, 2), 16);
Byte k = (Byte)Convert.ToUInt16(gUID.Substring(30, 2), 16);
return new Guid(a, b, c, d, e, f, g, h, i, j, k);
}
else
{
throw new InvalidOperationException("Input value violates octet string validation pattern (32-character hexidecimal string expected).");
}
}
Thank you very much for your help, and this really fine library!
Hi Jürgen,
Could you share your ActiveDirectory.cs?
I tried to copy the code posted above and I got the following errors:
“Error 1 The type or namespace name ‘SecurityIdentifier’ could not be found (are you missing a using directive or an assembly reference?)”
and
“Error 3 The name ‘Regex’ does not exist in the current context ”
Many Thanks.
Hi,
thanks for post!
But I have one problem:
I’m using SQL 2012 and I can’t create assembly for System.DirectoryServices.dll in Framework version 2.0.50727 (for v4.0.30319 I can). What can I do about it, please?
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.
[…] http://www.pawlowski.cz/2011/04/querying-active-directory-sql-server-clr/ […]
Hi Pawel
I have got a problem If I am quering boolean AD attributes. For example Computer disables/enalbed information. Are you going to change your code to fiz that issue?
Regards
Hi,
what problem exactly you have. What you are receiving?
You can easily achive this with:
CASE WHEN [userAccountControl] & 2 = 2 THEN ‘TRUE’ ELSE ‘FALSE’ END AS UAC_account_disabled,
CASE WHEN [userAccountControl] & 32 = 32 THEN ‘TRUE’ ELSE ‘FALSE’ END AS UAC_pwd_not_req,
CASE WHEN [userAccountControl] & 64 = 64 THEN ‘TRUE’ ELSE ‘FALSE’ END AS UAC_pwd_cant_chgd,
CASE WHEN [userAccountControl] & 65536 = 65536 THEN ‘TRUE’ ELSE ‘FALSE’ END AS UAC_pwd_dont_exp,
CASE WHEN [userAccountControl] & 524288 = 524288 THEN ‘TRUE’ ELSE ‘FALSE’ END AS UAC_trust_for_delig,