在 Visual Studio 中调用外部 Web 服务时出现安全错误 [英] Security error when calling external web service in visual studio

问题描述

我正在尝试在 Visual Studio 中调用外部网络服务，但出现错误.

I am trying to call an external webservice in visual studio but I am getting the error.

System.Security.SecurityException:请求类型为System.Security.Permissions.SecurityPermission、mscorlib、Version=4.0.0.0、Culture=neutral、PublicKeyToken=b77a5c561934e089"的权限失败.System.Security.SecurityException:在 System.Security.CodeAccessSecurityEngine.Check(对象需求，StackCrawlMark& stackMark，Boolean isPermSet)在 System.Security.CodeAccessPermission.Demand()在 System.Net.ServicePointManager.set_ServerCertificateValidationCallback(RemoteCertificateValidationCallback 值)

下面是调用webservice的程序.

The following is the program to invoke the webservice.

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    [return: SqlFacet(MaxSize = -1)]
    public static SqlString NYP_RestGet(SqlString uri)
    {
        String document;
        System.Net.ServicePointManager.ServerCertificateValidationCallback +=
        delegate(object sender, System.Security.Cryptography.X509Certificates.X509Certificate certificate,
                                System.Security.Cryptography.X509Certificates.X509Chain chain,
                                System.Net.Security.SslPolicyErrors sslPolicyErrors)
        {
            return true; // **** Always accept
        };

        // Set up the request, including authentication
        WebRequest req = WebRequest.Create(Convert.ToString(uri));
        ((HttpWebRequest)req).UserAgent = "CLR web client on SQL Server";
        req.ContentType = "application/xml";
        ((HttpWebRequest)req).Accept = "application/xml";

        WebResponse resp = req.GetResponse();
        Stream dataStream = resp.GetResponseStream();
        StreamReader rdr = new StreamReader(dataStream);
        document = (String)rdr.ReadToEnd();


        rdr.Close();
        dataStream.Close();
        resp.Close();
         return (document);
    }
};

推荐答案

对于与网络相关的请求本身，您需要将程序集设置为 PERMISSION_SET = EXTERNAL_ACCESS.然而，不幸的是，使用 System.Net.ServicePointManager.ServerCertificateValidationCallback 需要 PERMISSION_SET = UNSAFE.如果您没有明确需要覆盖 SSL 证书的处理，那么您应该摆脱该委托，因为将程序集设置为 EXTERNAL_ACCESS 会更好.

For network-related requests by themselves you would need to set your Assembly to PERMISSION_SET = EXTERNAL_ACCESS. However, using System.Net.ServicePointManager.ServerCertificateValidationCallback unfortunately requires PERMISSION_SET = UNSAFE. If you do not expressly need to override the handling of SSL Certificates, then you should get rid of that delegate since it would be better for the Assembly to be set to EXTERNAL_ACCESS.

不幸的是，Visual Studio/SSDT(SQL Server 数据工具)并没有让您很容易地采取适当的步骤来允许您的程序集设置为 EXTERNAL_ACCESS 或 UNSAFE.但是，它们确实可以轻松地将 TRUSTWORTHY 选项设置为 ON，这通常是一个坏主意.

Unfortunately, Visual Studio / SSDT (SQL Server Data Tools) does not make it very easy to take the appropriate steps needed to allow your Assembly to be set to either EXTERNAL_ACCESS or UNSAFE. They do, however, make it easy enough to set the TRUSTWORTHY option to ON, which is mostly a bad idea.

除非绝对必要，否则请不要设置TRUSTWORTHY ON！并且仅在加载您未构建且无法重新签名的程序集时才应该是必要的".这主要发生在加载不受支持"并因此不在 SQL Server 的 CLR 主机中的 .NET Framework 库时.在这些情况之外，您不应将数据库设置为 TRUSTWORTHY ON，因为它会打开一个安全漏洞.

Please do not set TRUSTWORTHY ON unless absolutely necessary! And it should only be "necessary" when loading an Assembly that you did not build and cannot re-sign. And that mostly happens when loading .NET Framework libraries that aren't "supported" and hence not already in SQL Server's CLR host. Outside of those circumstances, you should not be setting the database to TRUSTWORTHY ON as it opens up a security hole.

相反，最好执行以下操作:

Instead, it is much better to do the following:

USE [master];

CREATE ASYMMETRIC KEY [SomeKey]
  AUTHORIZATION [dbo]
  FROM EXECUTABLE FILE = 'C:\path\to\Some.dll';

CREATE LOGIN [SomeLogin]
  FROM ASYMMETRIC KEY [SomeKey];

GRANT EXTERNAL ACCESS ASSEMBLY TO [SomeLogin]; -- or "UNSAFE" instead of "EXTERNAL ACCESS"

以上每个实例、每个键只需要执行一次.因此，如果您对所有程序集使用相同的 snk/pfx 文件，则每个 SQL Server 实例只需执行一次上述步骤；程序集和包含这些程序集的数据库的数量无关紧要.

The above only needs to be done once per Instance, per key. So if you use the same snk / pfx file for all of your assemblies, then the steps shown above only need to be done once per SQL Server Instance; the number of Assemblies and databases containing those Assemblies does not matter.

这种方法使您可以更好地保护数据库(通过将 TRUSTWORTHY 设置为 OFF)并允许更精细地控制甚至允许哪些程序集设置为 EXTERNAL_ACCESS 和/或 UNSAFE(因为您可以根据不同的密钥使用不同的密钥进行签名和登录).

This approach allows you to keep better security on the database (by keeping TRUSTWORTHY set to OFF) and allows for more granular control of which assemblies are even allowed to be set to EXTERNAL_ACCESS and/or UNSAFE (since you can separate by using different keys for signing and Logins based on those different keys).

有关安全选项的更详细演练，请参阅我在 SQL Server Central 上撰写的以下文章:通往 SQLCLR 4 级的阶梯:安全性(外部和不安全程序集)(需要免费注册).

For a more detailed walk-through of the security options, please see the following article that I wrote on SQL Server Central: Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies) (free registration is required).

其他注意事项:

1. 有关通过 SQLCLR 一般调用 Web 服务和网页的更多说明，请在此处查看我的回答:从 SQL CLR 调用 Web 服务?
2. 无需使用Convert.ToString(uri).所有 Sql* 类型都有一个 .Value 属性，该属性返回适当的本机类型.所以用 uri.Value 替换它.
3. 您不需要 SqlFunction 属性中的 DataAccess = DataAccessKind.Read，因为您没有进行任何数据访问.设置 DataAccess = DataAccessKind.Read 会对性能造成轻微影响，因此您没有使用它，只需将其删除即可.
4. dataStream 和 rdr(分别是 Stream 和 StreamReader)是一次性"对象，所以你确实需要对它们调用 .Dispose()，您可能可以这样做而不是 .Close().
5. 您没有任何错误处理，这意味着如果在这两个对象上调用 Dispose 方法之前发生错误，您的进程可以保持打开外部网络句柄，并且在 App Domain 回收之前可能不会发布，这可能不会持续很长时间.您需要使用 using 构造或正确构建 try/catch/finally.这是在 SQL Server 中运行而不执行这两件事之一的相当危险的代码.
6. 有关使用 SQL Server 的 CLR 主机的各种细微差别的更多详细信息，请参阅我的文章:通往 SQLCLR 5 级的阶梯:开发(在 SQL Server 中使用 .NET)(需要免费注册).

1. For more notes regarding calling Web Services and web pages in general via SQLCLR, please see my answer here: Call web service from SQL CLR?
2. There is no need to use Convert.ToString(uri). All of the Sql* types have a .Value property that returns the appropriate native type. So replace that with uri.Value.
3. You do not need DataAccess = DataAccessKind.Read in the SqlFunction attribute since you are not doing any data access. Setting DataAccess = DataAccessKind.Read is a slight performance hit, so since you are not making use of it, just remove it.
4. dataStream and rdr (being a Stream and StreamReader respectively) are "disposable" objects, so you really need to be calling .Dispose() on them, which you can probably do instead of the .Close().
5. You do not have any error handling, which means that if there is an error prior to the Dispose method being called on both of those objects, your process could hold open the external network handle, and that might not get released until the App Domain recycles which might not be for a long time. You need to either use the using construct or properly structure a try / catch / finally. This is rather dangerous code to be running within SQL Server without doing one of these two things.
6. For more details on the various nuances of working with SQL Server's CLR host, please see my article: Stairway to SQLCLR Level 5: Development (Using .NET within SQL Server) (free registration is required).

这篇关于在 Visual Studio 中调用外部 Web 服务时出现安全错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！