My problem is as follows: I need a clr udf (in C#) to query with a given ad-usr the ad-group membership
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.DirectoryServices.AccountManagement;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 check_user_is_part_of_ad_grp(SqlString ad_usr, SqlString ad_grp)
{
bool bMemberOf = false;
// set up domain context
PrincipalContext ctx = new PrincipalContext(ContextType.Domain);
// find the group in question
GroupPrincipal group = GroupPrincipal.FindByIdentity(ctx, ad_grp.ToString());
UserPrincipal usr = UserPrincipal.FindByIdentity(ctx, ad_usr.ToString());
if (group != null && usr != null)
{
bMemberOf = usr.IsMemberOf(group);
}
// Put your code here
return new SqlInt32 (bMemberOf ? 1 : 0);
}
}
If I publish the CLR to my SQL Server 2008 (.net 3.5), then I run the udf as follows:
select dbo.check_user_is_part_of_ad_grp('user', 'group')
And I get an error:
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "check_user_is_part_of_ad_grp":
System.Security.SecurityException: Request for the permission of type 'System.DirectoryServices.DirectoryServicesPermission, System.DirectoryServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' failed.
System.Security.SecurityException:
at UserDefinedFunctions.check_user_is_part_of_ad_grp(SqlString ad_usr, SqlString ad_grp)
I set the target framework of my project to 3.5 and the permission level to EXTERNAL_ACCESS
. Also the project references (System.DirectoryServices
, System.DirectoryServices.AccountManamgement
, System.DirectoryServices.Protocols
) to EXTERNAL
I appreciate any help