MS SQL 2012 DirectoryServicesPermission中的CLR fce失败

问题描述

您好，

我做了这一切并且结果并不好:-)

I did this all and result isnt good :-)

当我尝试调用函数dbo时。 fn_jk_ver_email（@param）发生错误：

When I try to call the function dbo.fn_jk_ver_email(@param) the error occurs:

 

// SQL settings
USE master
GO  

CREATE ASYMMETRIC KEY  DirectoryServices_Key
    FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\assembly\GAC_MSIL\System.DirectoryServices\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.DirectoryServices.dll';

CREATE LOGIN  DirectoryServices_Login
    FROM ASYMMETRIC KEY  DirectoryServices_Key;
    
GRANT EXTERNAL ACCESS ASSEMBLY TO  DirectoryServices_Login;

GRANT UNSAFE ASSEMBLY TO DirectoryServices_Login; 
GO 

USE CRMMAFRA_TEST_23032014
GO
CREATE ASSEMBLY DirectoryServices FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.DirectoryServices.dll'  --na serveru
WITH PERMISSION_SET = UNSAFE;
GO


-- Create My_CLR_function in SQL
USE CRMMAFRA_TEST_23032014
GO
CREATE FUNCTION [dbo].[fn_jk_ver_email]( @adresa nvarchar(100))
RETURNS int
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME LDAP_CLR.UserDefinedFunctions.jk_ver_email;
GO





//CLR fce deployed into SQL
using System;
using System.Data;
using System.DirectoryServices.AccountManagement;
using System.DirectoryServices;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlInt32 jk_ver_email(string emailadresamoje)
    {
        int vysledek = 0;

        int firstCharacter = emailadresamoje.IndexOf("@") + 1;

        string domena = emailadresamoje.Substring(firstCharacter);

        string ldapstr = "LDAP://" + domena;

        emailadresamoje = emailadresamoje.ToLower();




        using (DirectoryEntry entry = new DirectoryEntry(ldapstr))
        {
            DirectorySearcher dSearch = new DirectorySearcher(entry);
            dSearch.Filter = "(objectClass=user)";
            foreach (SearchResult sResultSet in dSearch.FindAll())
            {
                if (sResultSet.Properties["mail"].Count > 0)
                {
                    //if (String.ReferenceEquals(sResultSet.Properties["mail"][0].ToString(), emailadresamoje))
                    bool result = Equals(sResultSet.Properties["mail"][0].ToString().ToLower(), emailadresamoje);


                    if (result)
                        vysledek = 1;
                }
            }
        }
        return new SqlInt32(vysledek);
    }
};

Msg 6522，Level 16，State 2，Line 1

Msg 6522, Level 16, State 2, Line 1

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

A .NET Framework error occurred during execution of user-defined routine or aggregate "fn_jk_ver_email":

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

System.Security.SecurityException: Request for the permission of type 'System.DirectoryServices.DirectoryServicesPermission, System.DirectoryServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' failed.

System.Security.SecurityException：

System.Security.SecurityException:

  在UserDefinedFunctions.jk_ver_email（String emailadresamoje）

   at UserDefinedFunctions.jk_ver_email(String emailadresamoje)

。

推荐答案

我也遇到了类似的问题，同样的错误信息，尝试运行CLR UDF获得Active Directory的成员 - 如果你想出来请发布答案，与此同时我会继续寻找自己

I'm also having a similar problem with the same error message, trying to run a CLR UDF to get members of an Active Directory - if you ever figure it out please post the answer, in the meantime I'll keep on looking myself

这篇关于MS SQL 2012 DirectoryServicesPermission中的CLR fce失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！