已开启TRUSTWORTHY，SQL Server CLR仍缺System.Net.WebPermission权限如何解决？

问题详情 #

创建了用于SQL Server CLR调用Web API的sqlproj项目，代码如下：

public partial class SqlServerClrClient
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString Query(string url, string jobName, string sql)
    {
        var jobNameBase64 = Convert.ToBase64String(Encoding.UTF8.GetBytes(jobName));
        var sqlBase64 = Convert.ToBase64String(Encoding.UTF8.GetBytes(sql));
        var param =
            $"{{\"Id\":\"{Guid.NewGuid()}\",\"JobNameBase64\":\"{jobNameBase64}\",\"SqlBase64\":\"{sqlBase64}\",\"CreatedUtcTime\":\"{DateTime.UtcNow:yyyy-MM-dd HH:mm:ss}\"}}";
        var postData = "cmdParam=" + param;
        var bytes = Encoding.UTF8.GetBytes(postData);

        var httpRequest = WebRequest.CreateHttp(url);
        httpRequest.Method = "POST";
        httpRequest.ContentType = "application/x-www-form-urlencoded";
        httpRequest.ContentLength = bytes.Length;
        using (var stream = httpRequest.GetRequestStream())
            stream.Write(bytes, 0, bytes.Length);

        using (var httpResponse = httpRequest.GetResponse())
        {
            var responseStream = httpResponse.GetResponseStream();
            if (responseStream is null)
                return SqlString.Null;
            using (var streamReader = new StreamReader(responseStream, Encoding.UTF8))
                return streamReader.ReadToEnd();
        }
    }
}

已执行以下SQL开启数据库TRUSTWORTHY属性：

ALTER DATABASE TESTDB SET TRUSTWORTHY ON

调用CLR函数时出现权限错误：

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "GatewayQuery": 
System.Security.SecurityException: Request for the permission of type 'System.Net.WebPermission, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException: 
   at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
   at System.Security.CodeAccessPermission.Demand()
   at System.Net.HttpWebRequest.CheckConnectPermission(Uri uri, Boolean needExecutionContext)
   at System.Net.HttpWebRequest..ctor(Uri uri, ServicePoint servicePoint)
   at System.Net.HttpRequestCreator.Create(Uri Uri)
   at System.Net.WebRequest.Create(Uri requestUri, Boolean useUriBase)
   at System.Net.WebRequest.CreateHttp(Uri requestUri)
   at CIPGatewaySqlServerClrClient.Query(String url, String jobName, String sql)

解决方案 #

方法一：将CLR程序集设置为UNSAFE权限级别 #

SQL Server CLR默认的SAFE权限级别禁止访问外部资源（如网络请求），UNSAFE权限级别允许程序集执行几乎所有操作，适合需要调用Web API的场景。

1. 若尚未部署程序集，部署时指定权限集：

CREATE ASSEMBLY [你的程序集名称]
FROM 'C:\路径\你的程序集.dll'
WITH PERMISSION_SET = UNSAFE;

2. 若已部署程序集，修改其权限集：

ALTER ASSEMBLY [你的程序集名称] WITH PERMISSION_SET = UNSAFE;

注意：UNSAFE权限会带来一定安全风险，仅在信任程序集代码的前提下使用。

方法二：使用证书签名实现EXTERNAL_ACCESS权限（更安全） #

该方法无需依赖TRUSTWORTHY属性，通过证书签名授予程序集外部访问权限，安全性更高：

1. 在目标数据库（TESTDB）中创建证书：

USE TESTDB;
CREATE CERTIFICATE CLRWebAPICert
ENCRYPTION BY PASSWORD = 'StrongPassword123!'
WITH SUBJECT = 'CLR Web API Access Certificate';

2. 导出证书到文件：

BACKUP CERTIFICATE CLRWebAPICert
TO FILE = 'C:\路径\CLRWebAPICert.cer';

3. 在master数据库中创建证书并关联登录名，授予权限：

USE master;
CREATE CERTIFICATE CLRWebAPICert
FROM FILE = 'C:\路径\CLRWebAPICert.cer';

CREATE LOGIN CLRWebAPILogin
FROM CERTIFICATE CLRWebAPICert;

GRANT EXTERNAL ACCESS ASSEMBLY TO CLRWebAPILogin;

4. 用证书签名程序集：

USE TESTDB;
ADD SIGNATURE TO ASSEMBLY::[你的程序集名称]
BY CERTIFICATE CLRWebAPICert
WITH PASSWORD = 'StrongPassword123!';

5. 修改程序集权限为EXTERNAL_ACCESS：

ALTER ASSEMBLY [你的程序集名称] WITH PERMISSION_SET = EXTERNAL_ACCESS;

额外检查 #

确保SQL Server的CLR集成功能已开启：

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'clr enabled', 1;
RECONFIGURE;

内容的提问来源于stack exchange，提问作者Mutudu