C＃clr udf用于Active Directory组成员身份 [英] C# clr udf for Active Directory group membership

问题描述

我的问题如下：我需要一个clr udf（在C＃中）以给定的ad-usr查询广告组成员身份

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);
    }
}

如果我将CLR发布到SQL Server 2008（ .net 3.5），然后按以下方式运行udf：

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')

我收到一个错误：

消息6522，级别16，状态1，第1行

在执行用户定义的例程或聚合 check_user_is_part_of_ad_grp期间发生.NET Framework错误：

System.Security.SecurityException：请求类型为 System.DirectoryServices.DirectoryServicesPermission，System.DirectoryServices，Version = 2.0.0.0，Culture = neutral，PublicKeyToken = b03f5f7f11d50a3a的权限失败。

System.Security .SecurityException：

在UserDefinedFunctions.check_user_is_part_of_ad_grp（SqlString ad_usr，SqlString ad_grp）

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)

我设置了目标框架我的项目为3.5，权限级别为 EXTERNAL_ACCESS 。项目引用（ System.DirectoryServices ， System.DirectoryServices.AccountManamgement ， System.DirectoryServices .Protocols ）到 EXTERNAL

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

感谢任何帮助

推荐答案

所有这些程序集很可能都需要设置为 UNSAFE ，尤其是三个<您导入的strong> System.DirectoryServices * .NET Framework库。另外，由于您要导入不受支持的.NET Framework库，因此您将为了使其正常工作，需要将数据库设置为 TRUSTWORTHY ON 。通常需要避免将数据库设置为 TRUSTWORTHY ON ，因为这存在安全风险，但是在这种情况下，我认为无法避免。

Most likely all of those Assemblies will need to be set to UNSAFE, especially the three System.DirectoryServices* .NET Framework libraries that you imported. Also, since you are importing unsupported .NET Framework libraries, you will need to set the database to TRUSTWORTHY ON in order to get them to work. Setting a Database to TRUSTWORTHY ON is typically something you want to avoid as it is a security risk, but in this case I do not believe that it can be avoided.

也就是说，我不确定您是否甚至需要在SQLCLR中自己创建此函数。如果您只是想知道某个登录名（显然只有Windows登录名）是否属于特定的Active Directory组，则可以使用内置函数应该为您执行此操作。 IS_MEMBER 函数将指示 current 登录名是指定Windows组（指定为 Domain\Group ）的成员。与您要创建的函数不同，该函数的工作方式是仅对当前登录有效。您不能将任意登录信息传递给它。但是，它也不需要此 SQLCLR解决方案的任何额外工作量和安全风险。因此，有一些需要考虑的问题：-）。

That said, I am not sure that you even need to create this function yourself in SQLCLR. If you are just wanting to know if a Login (Windows Logins only, obviously) belongs to a particular Active Directory group, there is a built-in function that should do that for you. The IS_MEMBER function will indicate if the current Login is a member of the specified Windows group (specified as Domain\Group). The difference in how this function works as opposed to the one that you are creating is that it only works for the current Login; you cannot pass any arbitrary Login into it. BUT, it also doesn't require any of the extra effort and security risks that are a part of this SQLCLR solution. So, something to consider :-).

OP对这个答案的评论：

Comment from O.P. on this answer:

实际上，我需要检查任意登录名（如果它是特定组的成员）。我什至尝试将存储的proc和 OPENQUERY与链接到ADSI的服务器一起使用，但这只能用作动态SQL，因为我需要注入组和用户。

Actually, I need to check an arbitrary Login if it's member of a particular group. I even tried to use a stored proc and `OPENQUERY' with a linked server to ADSI, but this only works as Dynamic SQL since I need to inject group and user.

在这种情况下，只需将Dynamic SQL设置为两层而不是通常的一层即可。类似于以下内容：

In that case, just make the Dynamic SQL two layers deep instead of the usual one layer. Something along the lines of:

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'
  SELECT *
  FROM   OPENQUERY([LinkedServer], N''
             SELECT *
             FROM   someResource
             WHERE  GroupName=N''''' + @Group + N'''''
             AND    ObjectName=N''''' + @Login + N''''';
                   '');
';

PRINT @SQL; -- DEBUG
EXEC (@SQL);

在这种方法中，执行 OPENQUERY 的查询是动态SQL，但是赋予 OPENQUERY 执行的查询是字符串文字。

In this approach, the query executing OPENQUERY is Dynamic SQL, but the query given to OPENQUERY to execute is a string literal.

这篇关于C＃clr udf用于Active Directory组成员身份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！