Sql-Clr 安全异常

发布于 2024-12-29 17:57:11 字数 1479 浏览 3 评论 0原文

最近我创建了一个可以获取用户密码的应用程序，其用户名作为参数提供，我检查了代码，它工作正常，现在我创建了一个类库，以便我可以在下面的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)

任何解决方案。

原文

Recently i created an application which could get the password of the user, whose username is provided as the parameter, i checked the code, its working fine, now i created a class library so that i can add this assemnbly in sql server 2008, below is the code of my class library.

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

i created the assembly in sql server 2008 like below

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

i got the command completed successfully message, then i created a function like below:

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

here too i got the command completed successfully message, now when i tried to call the function using

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

it throws me below error:

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)

any solutions.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

橙幽之幻 2025-01-05 17:57:11

不确定可能是什么问题。首先尝试将 PERMISSION_SET 更改为 Unrestricted。
如果这没有帮助，请尝试从 Visual Studio 调试 DLL。

回复收藏 0 原文

救星 2025-01-05 17:57:11

您需要设置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

You need to set PERMISSION_SET = UNSAFE to get access to remote resources.
Also you might want to set TRUSTWORTHY flag to 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

回复收藏 0 原文

~没有更多了~