Directory Programming .NET

Hi

Is it possible to query AZMAN roles in sql 2005? I am using AD as the store type.

Thank you

I have managed to figure out that you need to use Link server and openquery to query.

Unfortunately I am not getting any data. I want to return all roles assigned to a windows user or all existing roles.

I have written the following query:

SELECT cn
FROM OPENQUERY( ADSI,
'SELECT cn
FROM ''LDAP://server:50000/CN=AzManADAMStore,OU=SecNetPartition,O=SecNet,C=US''
where cn = ''Roles''')

Thank You

No clue. I'd be surprised if you can get this to work easily. I think the way you are intended to do this is to call the AzMan APIs to initialize an AzMan context (via COM). I've never seen anyone do this with LDAP queries.

Hi Joe

If I can access the AZMAN roles for a user in SQL Stored procedures then I should be able to use AZMAN with ADAM for authorisation. I wonder if SQL projects coudl help with this?

Thanks again!

I'm not a SQL guy at all, but I think you would be wanting to do this by calling the AzMan COM interfaces and not by trying to do this with an LDAP query of some sort.

Thank you for all the advice. I can access the AZMAN store using SQL openquery but can't retrieve any data. The AZMAN is stored inside AD so I should be able to access the data.

Hi Joe

I managed to access AZMAN from SQL using SQL CLR. Dont know if this is the best way but it is the only way I could use AZMAN as my role provider.

Here is the code I used.


1) I first created a strong named class library. This allows me to to reference the AZROLESLib inside my SQL project.
2) I create an ASYMMETRIC KEY and a login for the key
3) CREATE ASSEMBLY a unsafe assembly for the dll
4) Set a reference to the dll in my SQL project.

The second piece of code is my SQL Project that contains the user defined function. It returns a table of the roles that are assigned to a user account in Azman

Thanks
Fred


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AZROLESLib;
using System.Security.Principal;
using Microsoft.SqlServer.Server;
using System.Collections;

namespace P_Azman
{
public class Azman
{
///
/// Get roles per application name and user name
///
/// AzManStore name
/// Application name
///Client name
///Domain name
/// object
public static object GetRoles(string azManStoreName,string applicationName,string clientName,string domainName)
{
WindowsImpersonationContext originalContext = null;
object result = null;
try
{
//Impersonate the current SQL Security context
WindowsIdentity callerIdentity = SqlContext.WindowsIdentity;

if (callerIdentity != null)
{
originalContext = callerIdentity.Impersonate();
AzAuthorizationStoreClass AzManStore = new AzAuthorizationStoreClass();
AzManStore.Initialize(0, azManStoreName, null);
IAzApplication azApp = AzManStore.OpenApplication(applicationName, null);
IAzClientContext clientContext = azApp.InitializeClientContextFromName(clientName, domainName, null);
result = clientContext.GetRoles(null);
}

}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (originalContext != null)
originalContext.Undo();
}
return result;
}


}
}








using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using P_Azman;
using System.Collections;


public partial class UserDefinedFunctions
{
///
/// Get user roles
///
/// AzMan store Name
/// Application name
/// Client name
/// Domain name
/// IEnumerable
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillRows", TableDefinition = "name nvarchar(4000)")]
public static IEnumerable GetUserRoles(string azManStoreName, string applicationName, string clientName, string domainName)
{
try
{

object[] roles = (object[])Azman.GetRoles(azManStoreName,applicationName,clientName,domainName);
ArrayList rows = ArrayList.Adapter(roles);
return rows;

}

catch (System.Exception ex)
{
throw new Exception(ex.Message);
}
}

public static void FillRows(Object obj,out string name)
{
object row = (object)obj;
name = (string)obj;




}
};

SQL CLR seems like a good way to go here. Trying to get the Azman stuff built up correctly via LDAP queries seemed like it would be a disaster. Much better to use the Azman APIs and SQL CLR is probably the cleanest way to get there.

How about this way?

-------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[SelectAzmanRoleMembers] (@ApplicationName varchar(50), @Role varchar(50))

AS
BEGIN
 DECLARE @Application TABLE (cn varchar(50));
 DECLARE @ApplicationCN varchar(50);
 DECLARE @SQLString nvarchar(MAX);

 SET @SQLString='SELECT cn FROM OPENQUERY(ADSI,''SELECT cn FROM ''''LDAP://cn=MyCompny Apps,cn=Program

Data,dc=corp,dc=MyCompny,dc=com''''
       WHERE objectClass=''''msDS-AzApplication'''' AND

msDS-AzApplicationName='''''+@ApplicationName+''''''')';
 INSERT @Application EXEC(@SQLString);
 SET @ApplicationCN=(SELECT TOP 1 cn FROM @Application);

 SET @SQLString='SELECT cn FROM OPENQUERY(ADSI,''SELECT cn FROM ''''LDAP://ou=staff,dc=corp,dc=MyCompny,dc=com''''
  WHERE msDS-MembersForAzRoleBL=''''CN='+@Role+',CN=AzRoleObjectContainer-'+@ApplicationCN+',CN='+@ApplicationCN+
   ',CN=MyCompny Apps,CN=Program Data,DC=corp,DC=MyCompny,DC=com'''''')'

 EXEC(@SQLString);
  
 --Note:Recursive group members retrieving is not handled yet.
 RETURN
END
--------------------------------------------------------------------------------------
ALTER PROCEDURE [SelectAzmanRoles] (@ApplicationName varchar(50), @UserID varchar(50))

AS
BEGIN
 DECLARE @tmpTable TABLE (cn varchar(1024));
 DECLARE @tmpTable2 TABLE (distinguishedName varchar(1024));
 DECLARE @ApplicationCN varchar(1024);
 DECLARE @RoleObjectContainrCN varchar(1024);
 DECLARE @UserADsPath varchar(MAX);
 DECLARE @SQLString nvarchar(MAX);

 SET @SQLString='SELECT cn FROM OPENQUERY(ADSI,''SELECT cn FROM ''''LDAP://cn=MyCompny Apps,cn=Program

Data,dc=corp,dc=MyCompny,dc=com''''
       WHERE objectClass=''''msDS-AzApplication'''' AND

msDS-AzApplicationName='''''+@ApplicationName+''''''')';
 INSERT @tmpTable EXEC(@SQLString);
 SET @ApplicationCN=(SELECT TOP 1 cn FROM @tmpTable);
 SET @RoleObjectContainrCN='AzRoleObjectContainer-'+@ApplicationCN;

 SET @SQLString='SELECT distinguishedName FROM OPENQUERY(ADSI,''SELECT distinguishedName FROM ''''LDAP://dc=corp,dc=MyCompny,dc=com''''
       WHERE sAMAccountName='''''+@UserID+''''''')';

 INSERT @tmpTable2 EXEC(@SQLString);
 SET @UserADsPath=(SELECT TOP 1 distinguishedName FROM @tmpTable2);

 SET @SQLString='SELECT cn FROM OPENQUERY(ADSI,''SELECT cn FROM ''''LDAP://cn='+@RoleObjectContainrCN+
   ',cn='+@ApplicationCN+',cn=MyCompny Apps,cn=Program Data,dc=corp,dc=MyCompny,dc=com''''
   WHERE msDS-MembersForAzRole='''''+@UserADsPath+''''''')'
 EXEC(@SQLString);

 --Recursive group is not handled yet.
END

I managed to come up with procedures which can get Azman role members belong to a certain role drilling into all Active Directory groups too. The second procedure is for the other way around. It gets all roles in an application defines the user in Azman.

 

----------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[SelectAzmanRoles] (@ApplicationName varchar(50), @UserID varchar(50), @bRecursive bit=0)
--returns all roles for @UserID. when @bRecursive=1, it drills into groups recursively.
 
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @Roles TABLE (cn varchar(1024));
 DECLARE @tmpTable TABLE (cn varchar(1024));
 DECLARE @tmpTable2 TABLE (distinguishedName varchar(1024));
 DECLARE @ApplicationCN varchar(1024);
 DECLARE @RoleObjectContainrCN varchar(1024);
 DECLARE @UserDN varchar(MAX);
 DECLARE @SQLString nvarchar(MAX);

 SET @SQLString='SELECT cn FROM OPENQUERY(ADSI,''SELECT cn FROM ''''LDAP://cn=myCompany Apps,cn=Program Data,dc=corp,dc=myCompany,dc=com''''
       WHERE objectClass=''''msDS-AzApplication'''' AND

msDS-AzApplicationName='''''+@ApplicationName+''''''')';
 INSERT @tmpTable EXEC(@SQLString);
 SET @ApplicationCN=(SELECT TOP 1 cn FROM @tmpTable);
 SET @RoleObjectContainrCN='AzRoleObjectContainer-'+@ApplicationCN;

 SET @SQLString='SELECT distinguishedName FROM OPENQUERY(ADSI,''SELECT distinguishedName FROM ''''LDAP://dc=corp,dc=myCompany,dc=com''''
       WHERE sAMAccountName='''''+@UserID+''''''')';
 INSERT @tmpTable2 EXEC(@SQLString);
 SET @UserDN=(SELECT TOP 1 distinguishedName FROM @tmpTable2);

 SET @SQLString='SELECT cn FROM OPENQUERY(ADSI,''SELECT cn FROM ''''LDAP://cn='+@RoleObjectContainrCN+',cn='
   +@ApplicationCN+',cn=myCompany Apps,cn=Program Data,dc=corp,dc=myCompany,dc=com''''
   WHERE msDS-MembersForAzRole='''''+@UserDN+''''''')'

 IF @bRecursive=0
 BEGIN
  EXEC(@SQLString);
  RETURN;
 END;
 ELSE
 BEGIN
  INSERT @Roles EXEC(@SQLString);
  DECLARE @Groups TABLE(cn varchar(1024),dn varchar(1024));

  --Get all groups to which this user belongs
  SET @SQLString='SELECT cn,distinguishedName FROM OPENQUERY (ADSI, ''<LDAP://dc=corp,dc=myCompany,dc=com>;
    (&(objectCategory=group)(member:1.2.840.113556.1.4.1941:=' +@UserDN +'));cn,distinguishedName;subtree''  )' ;
  PRINT @SQLstring;
  INSERT @Groups EXEC(@SQLString);

  --Get all person members who belong to those groups using a cursor
  DECLARE @cn varchar(1024);
  DECLARE @dn varchar(1024);
  DECLARE cGroups CURSOR
   FOR SELECT cn,dn FROM @Groups;
  OPEN cGroups;
  FETCH NEXT FROM cGroups INTO @cn, @dn
  WHILE @@FETCH_STATUS = 0
  BEGIN
   SET @SQLString='SELECT cn FROM OPENQUERY (ADSI, ''<LDAP://cn='+@RoleObjectContainrCN+',cn='
   +@ApplicationCN+',cn=myCompany Apps,cn=Program Data,dc=corp,dc=myCompany,dc=com>;
    

(&(objectCategory=CN=ms-DS-Az-Role,CN=Schema,CN=Configuration,DC=corp,DC=myCompany,DC=com)(msDS-MembersForAzRole=' +@dn +'));cn;subtree''  )' ;
   PRINT @SQLstring;
   INSERT @Roles EXEC(@SQLString);

   FETCH NEXT FROM cGroups INTO @cn, @dn;
  END;
  CLOSE cGroups;
  DEALLOCATE cGroups;
  
 END;
  
 SELECT DISTINCT cn FROM @Roles;
 RETURN
END

/*
--Get all members of a group
select cn,AdsPath
from openquery (
          ADSI,

'<;(&(objectCategory=person)(memberOf:1.2.840.113556.1.4.1941:=CN=Administrators,CN=Builtin,DC=corp,DC=myCompany">LDAP://dc=corp,dc=myCompany,dc=com>;(&(objectCategory=person)(memberOf:1.2.840.113556.1.4.1941:=CN=Administrators,CN=Builtin,DC=corp,DC=myCompany,

DC=com));cn, adspath;subtree'
     )
order BY cn;

--get all groups a user is a member of
select cn,AdsPath
from openquery (
          ADSI, '<;(&(objectClass=group)(member:1.2.840.113556.1.4.1941:=CN=Nam">LDAP://dc=corp,dc=myCompany,dc=com>;(&(objectClass=group)(member:1.2.840.113556.1.4.1941:=CN=Nam

Park,OU=Developers,OU=Staff,DC=corp,DC=myCompany,DC=com));cn, adspath;subtree'
     )
order BY cn;
*/
-----------------------------------------------------------------------

ALTER PROCEDURE [dbo].[SelectAzmanRoleMembers] (@ApplicationName varchar(50), @Role varchar(50), @bRecursive bit=0)
--returns all members in the @Role. When @brecursive=1, it drills into groups recursively.

AS
BEGIN
 DECLARE @PersonMembers TABLE(cn varchar(1024),distinguishedName varchar(1024));
 DECLARE @Application TABLE (cn varchar(1024));
 DECLARE @ApplicationCN varchar(1024);
 DECLARE @SQLString nvarchar(MAX);

 SET @SQLString='SELECT cn FROM OPENQUERY(ADSI,''SELECT cn FROM ''''LDAP://cn=myCompany Apps,cn=Program Data,dc=corp,dc=myCompany,dc=com''''
       WHERE msDS-AzApplicationName='''''+@ApplicationName+''''''')';
 INSERT @Application EXEC(@SQLString);
 SET @ApplicationCN=(SELECT TOP 1 cn FROM @Application);

 IF @bRecursive=0
 BEGIN
  SET @SQLString='SELECT cn,distinguishedName FROM OPENQUERY(ADSI,''SELECT cn,distinguishedName FROM

''''LDAP://dc=corp,dc=myCompany,dc=com''''
   WHERE objectCategory=''''person'''' AND

msDS-MembersForAzRoleBL=''''CN='+@Role+',CN=AzRoleObjectContainer-'+@ApplicationCN+',CN='+@ApplicationCN+
    ',CN=myCompany Apps,CN=Program Data,DC=corp,DC=myCompany,DC=com'''''')'
  EXEC(@SQLString);
  RETURN;
 END;
 ELSE --Drill into groups recursively
 BEGIN
  DECLARE @Groups TABLE(cn varchar(1024),distinguishedName varchar(1024));

  --Get direct level persons memebers
  SET @SQLString='SELECT cn,distinguishedName FROM OPENQUERY(ADSI,''SELECT cn,distinguishedName FROM

''''LDAP://dc=corp,dc=myCompany,dc=com''''
   WHERE objectCategory=''''person'''' AND

msDS-MembersForAzRoleBL=''''CN='+@Role+',CN=AzRoleObjectContainer-'+@ApplicationCN+',CN='+@ApplicationCN+
    ',CN=myCompany Apps,CN=Program Data,DC=corp,DC=myCompany,DC=com'''''')'
  INSERT @PersonMembers EXEC(@SQLString);

  --Get all group members
  SET @SQLString='SELECT cn,distinguishedName FROM OPENQUERY(ADSI,''SELECT cn,distinguishedName FROM

''''LDAP://dc=corp,dc=myCompany,dc=com''''
   WHERE objectCategory=''''group'''' AND

msDS-MembersForAzRoleBL=''''CN='+@Role+',CN=AzRoleObjectContainer-'+@ApplicationCN+',CN='+@ApplicationCN+
    ',CN=myCompany Apps,CN=Program Data,DC=corp,DC=myCompany,DC=com'''''')';
  INSERT @Groups EXEC(@SQLString);

  --Get all person members who belong to those groups using a cursor
  DECLARE @cn varchar(1024);
  DECLARE @dn varchar(1024);
  DECLARE cGroups CURSOR
   FOR SELECT cn,distinguishedName FROM @Groups;
  OPEN cGroups;
  FETCH NEXT FROM cGroups INTO @cn, @dn
  WHILE @@FETCH_STATUS = 0
  BEGIN
   SET @SQLString='SELECT cn,distinguishedName FROM OPENQUERY (ADSI, ''<LDAP://dc=corp,dc=myCompany,dc=com>;
    (&(objectCategory=person)(memberOf:1.2.840.113556.1.4.1941:=' +@dn +'));cn,distinguishedName;subtree''  )' ;
   --See http://msdn.microsoft.com/en-us/library/aa746475(VS.85).aspx for recursive matching rule
   INSERT @PersonMembers EXEC(@SQLString);

   FETCH NEXT FROM cGroups INTO @cn, @dn;
  END;
  CLOSE cGroups;
  DEALLOCATE cGroups;
  
 END;
  
 SELECT DISTINCT cn,distinguishedName FROM @PersonMembers;
 RETURN
END