Sql-Clr安全异常

Question

最近我创建了一个可以获取用户密码的应用程序，它的用户名被作为参数提供，我检查了代码，它工作正常，现在我创建了一个类库，以便我可以在sql server 2008中汇编添加它，下面是我的类库的代码。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Security;

public class DnnUserCredentials
{
    public static string GetUserPassword(string username)
    {
        MembershipUser objUser = Membership.GetUser(username);
        return objUser.GetPassword();
    }
}

我在sql server 2008中创建了程序集，如下所示

CREATE ASSEMBLY DNN_USER_CREDENTIALS FROM 'E:\NBM SITES\SqlProjectDll (DO NOT DELETE)\UserCredentials.dll' WITH PERMISSION_SET = SAFE

我得到了命令成功完成的消息，然后我创建了一个如下所示的函数：

ALTER FUNCTION [dbo].[fn_UserCredentials](@UserName [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [DNN_USER_CREDENTIALS].[DnnUserCredentials].[GetUserPassword]

现在，当我尝试使用以下命令调用函数时，我也得到了命令已成功完成的消息

SELECT [dbo].[fn_UserCredentials]('host')

它抛出了下面的错误：

A .NET Framework error occurred during execution of user-defined routine or aggregate "fn_UserCredentials": 
System.Security.SecurityException: Request for the permission of type 'System.Web.AspNetHostingPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException: 
   at DnnUserCredentials.GetUserPassword(String username)

任何解决方案。

Answer 1

不确定什么是问题所在。首先尝试将PERMISSION_SET更改为无限制。当这无济于事时，请尝试从visual studio调试DLL。

Answer 2

您需要设置PERMISSION_SET = UNSAFE才能访问远程资源。此外，您可能希望将TRUSTWORTHY标志设置为ON。

ALTER [<DBName>] SET TRUSTWORTHY ON
GO
CREATE ASSEMBLY [<assemblyName>]
--user with sysadmin rights for this DB may be required for deploing
AUTHORIZATION [<someSysadmin>]
FROM '[<pathToDll>]'
--compiled with unsafe to access EventLog for example
WITH PERMISSION_SET = UNSAFE
GO