sql-server - 调用 CLR 函数时出现 SecurityException 错误

Question

我用 C# 编写了一个简单的用户定义函数，用于获取 IP 地址并返回主机名，代码如下:

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString getname(String ipAddress)
    {
        System.Data.SqlClient.SqlClientPermission pSql = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
        pSql.Assert();

        System.Security.PermissionSet psql1 = new PermissionSet(System.Security.Permissions.PermissionState.Unrestricted);
        psql1.Assert();

        System.Net.DnsPermission psql = new DnsPermission(System.Security.Permissions.PermissionState.Unrestricted);
        psql.Assert();

        System.Security.Permissions.SecurityPermission psql2 = new SecurityPermission(System.Security.Permissions.PermissionState.Unrestricted);
        psql2.Assert();

        IPHostEntry host = Dns.GetHostEntry(ipAddress);
        return new SqlString(host.HostName.ToString());
    }
}

现在我已经部署了它，但是当我尝试在 sql 中调用此函数时: select dbo.getname('10.10.10.10')

它返回此错误:

Msg 6522, Level 16, State 2, Line 7
A .NET Framework error occurred during execution of user-defined routine or aggregate "getname": 
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException: 
   at System.Security.CodeAccessSecurityEngine.CheckNReturnSO(PermissionToken permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32 unrestrictedOverride, Int32 create)
   at System.Security.CodeAccessSecurityEngine.Assert(CodeAccessPermission cap, StackCrawlMark& stackMark)
   at System.Security.CodeAccessPermission.Assert()
   at UserDefinedFunctions.getname(String ipAddress)

我已经在互联网上爬行了，但什么也没找到。请提出建议。

Answer 1

您必须创建具有外部访问权限集的程序集。程序集还必须经过签名，或者数据库设置为可信，并且数据库所有者授予外部访问程序集权限。上面的权限和断言代码不是必需的。

例如

using Microsoft.SqlServer.Server;
using System;
using System.Data.SqlTypes;
using System.Net;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.None)]
    public static SqlString getname(String ipAddress)
    {
        IPHostEntry host = Dns.GetHostEntry(ipAddress);
        return new SqlString(host.HostName);
    }
}

将代码保存到C:\resolve\resolve.cs并编译...

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\csc.exe /t:library /out:C:\resolve\resolve.dll C:\resolve\resolve.cs

如果不是本地实例，请将 .dll 复制到服务器。然后创建对象...

USE [master]
GO
CREATE DATABASE [clr]; 
GO
ALTER AUTHORIZATION ON DATABASE::[clr] TO [sa]; 
GO
ALTER DATABASE [clr] SET TRUSTWORTHY ON; 
GO
USE [clr]; 
GO
CREATE ASSEMBLY [assResolve] FROM 'C:\resolve\resolve.dll' 
WITH PERMISSION_SET=EXTERNAL_ACCESS; 
GO
CREATE FUNCTION [fnGetHostName](@ipAddress nvarchar(255)) RETURNS nvarchar(255) 
AS EXTERNAL NAME [assResolve].[UserDefinedFunctions].[getname]; 
GO
SELECT [dbo].[fnGetHostName](N'8.8.8.8') as [host]; 
GO
USE [master]