LDAP - Query specific OU for Groups

Mar 23, 2009 at 7:42 PM
Edited Mar 23, 2009 at 8:02 PM

Our organization is very strict in how they utilize and organize their Active Directory. One problem we have had was that the Admins will not create a Security Group unless it is actually going to secure a physical resource (e.g. network share, etc).

We have been trying to use Microsoft AzMan to query specific Organizational Units (OU) in order to find our users, but have not been able to do so due to limitations in their LDAP Group implementation.

Since not everyone desires this functionality, the feature needs to be ignored by default and continue to allow NetSqlAzMan to function normally (as it does now) unless a specific RootDSE pathis passed to the query.

Proposed custom extension to LDAP Query Syntax:
[RootDSE:OU=xyz,DC=contoso,DC=com](&(rest of my query))

The parts of the query in brackets are parsed and the RootDSE is then used as the root path for the LDAP query. This is also OPTIONAL, and is not required to be passed at all.

The code impact is fairly minimal, with changes needed in the following files:

NetSqlAzMan
  - DirectoryServices
    - DirectoryServicesUtils.cs
  SqlAzManApplicationGroup.cs
  SqlAzManStoreGroup.cs

TODO:
The parsing code is rather simplistic, it could be enhanced using RegEx, etc. so that passing more options could be handled, but it suited our needs.

Cheers,

Jeffrey Huntsman
Hillsborough County Sheriff's Office

PS: I have created an Issue of same name and attached the 3 code files?

Mar 24, 2009 at 12:03 AM
Edited Mar 24, 2009 at 9:16 AM

Hi Jeff,

I have implemented your request in the latest changeset; I lack only change the ExecuteLDAPQuery stored procedure.

 

Please download latest source code version, compile and test it

http://netsqlazman.codeplex.com/SourceControl/ListDownloadableCommits.aspx

Which changes have you done in the StoreGroup.cs and ApplicationGroup.cs files ?

Let me know.

Regards,

Andrea.

Mar 24, 2009 at 3:00 PM
Edited Mar 24, 2009 at 3:15 PM
Great, thanks Andrea!

I'm working on the testing now, great refactoring job! I'll also get you a working copy for the stored proc today

Jeff
Mar 24, 2009 at 3:35 PM
Hi Jeff,
the ExecuteLDAPQuery sp is called from the CheckAccess sp, so I need to update it.
I have already update the DirectoryServicesWebUtils class with the new changes.
Regards,
Andrea.

Mar 24, 2009 at 6:39 PM
I went ahead and posted the copy of the proc I was working on.

Testing is going very well, so far I have worked within the Snap-in and also the web console. I am now moving onto testing in my application.
Mar 24, 2009 at 10:03 PM
Great.
This is the updated SP.
If all test will be ok, I'll package all for the next release.
Regards,
Andrea.

USE [NetSqlAzManStorage]

GO

/****** Object: StoredProcedure [dbo].[ExecuteLDAPQuery] Script Date: 03/24/2009 22:02:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[ExecuteLDAPQuery](@LDAPPATH NVARCHAR(4000), @LDAPQUERY NVARCHAR(4000), @members_cur CURSOR VARYING OUTPUT)

AS

-- REMEMBER !!!

-- BEFORE executing ExecuteLDAPQuery procedure ... a Linked Server named 'ADSI' must be added:

-- --sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

CREATE TABLE #temp (objectSid VARBINARY(85))

IF @LDAPQUERY IS NULL OR RTRIM(LTRIM(@LDAPQUERY))='' OR @LDAPPATH IS NULL OR RTRIM(LTRIM(@LDAPPATH))=''

BEGIN

SET @members_cur = CURSOR STATIC FORWARD_ONLY FOR SELECT * FROM #temp

OPEN @members_cur

DROP TABLE #temp

RETURN

END

SET @LDAPPATH = REPLACE(@LDAPPATH, N'''', N'''''')

SET @LDAPQUERY = REPLACE(@LDAPQUERY, N'''', N'''''')

DECLARE @QUERY nvarchar(4000)

DECLARE @LDAPROOTDSEPART nvarchar(4000)

DECLARE @LDAPQUERYPART nvarchar(4000)

SET @LDAPROOTDSEPART = LTRIM(@LDAPQUERY)

IF CHARINDEX('[RootDSE:', @LDAPROOTDSEPART)=1

BEGIN

SET @LDAPROOTDSEPART = SUBSTRING(@LDAPROOTDSEPART, 10, CHARINDEX(']', @LDAPROOTDSEPART)-10)

SET @LDAPQUERYPART = SUBSTRING(@LDAPQUERY, CHARINDEX( ']', @LDAPQUERY)+1, 4000)

END

ELSE

BEGIN

SET @LDAPROOTDSEPART = @LDAPPATH

SET @LDAPQUERYPART = @LDAPQUERY

END

SET @QUERY = CHAR(39) + '<' + 'LDAP://'+ @LDAPROOTDSEPART + '>;(&(!(objectClass=computer))(&(|(objectClass=user)(objectClass=group)))' + @LDAPQUERYPART + ');objectSid;subtree' + CHAR(39)

DECLARE @OPENQUERY nvarchar(4000)

SET @OPENQUERY = 'SELECT * FROM OPENQUERY(ADSI, ' + @QUERY + ')'

INSERT INTO #temp EXEC (@OPENQUERY)

SET @members_cur = CURSOR STATIC FORWARD_ONLY FOR SELECT * FROM #temp

OPEN @members_cur

DROP TABLE #temp

Mar 25, 2009 at 2:17 PM
Great! I have updated my two test databases and will be testing all day today.

Jeffrey
Mar 26, 2009 at 2:46 PM
Andrea,

Looks good. My applications are authorizing using the new LDAP syntax and I am getting no unexpected results or errors. I have tested also using the CacheService and all looks great!

Thanks for your help!

Jeffrey
Mar 26, 2009 at 2:53 PM
Edited Jul 21, 2009 at 12:51 PM

Great.

Thanks for your collaboration.

 

I’m publishing the 3.5.2.1 official release.

Regards,
Andrea.