从 SQL CLR 调用 Web 服务? [英] Call web service from SQL CLR?

问题描述

我有一个返回表的 SQL Server 2012 存储过程.我必须修改该 SP 以向返回的表中添加一个附加值.不幸的是，附加值来自对网络服务的调用.根据我的研究，我收集到的主要方法是使用 SQL 中的 OLE 自动化过程 (sp_OA...) 或 SQLCLR 存储过程.鉴于 sp_OA... 过程在其中运行的安全上下文，单个返回值是一个 VARCHAR(10) 注册密钥，并且对该服务的调用很少(每小时十到二十次)，我猜 SQLCLR 方法是要走的路.此外，Web 服务托管在我们的 Intranet 上，外部无法访问.

I have a SQL Server 2012 stored procedure that returns a table. I have to modify that SP to add an additional value to the returned table. Unfortunately, that added value comes from a call to a web-service. From my research, I gather the main ways to do this are using the OLE Automation procedures (sp_OA...) in SQL, or a SQLCLR stored procedure. Given the security context in which the sp_OA... procedures run, the single return value is a VARCHAR(10) registration key, and calls to the service are few (ten to twenty per hour), I'm guessing the SQLCLR method is the way to go. Also, the web-service is hosted on our intranet, and not accessible to the outside world.

有没有更好的方法来完成我的需要?Better 意味着更高的性能、更好的安全性、更易于编码和维护

Is there a better way to accomplish what I need? Better meaning more performant, better security, easier to code and maintain

推荐答案

请不要使用 sp_OA* OLE 自动化程序.它们似乎并未被正式弃用，但 SQLCLR 取代了 OLE 自动化过程和扩展存储过程.

Please do not use the sp_OA* OLE Automation procedures. They do not appear to be officially deprecated, but SQLCLR replaces both the OLE Automation procedures as well as Extended Stored Procedures.

是的，这可以在 SQLCLR 中轻松完成.您可以找到有关使用 WCF 的示例(如@CodeCaster 的答案所示)或使用 HttpWebRequest/HttpWebResponse(我在此答案中有更多信息:如何从 SQL Server 存储过程调用 webservice ).另外，请注意，有时您还需要添加序列化程序集:在 CLR 集成中使用 Web 服务和 Xml 序列化

Yes, this can be done easily enough in SQLCLR. You can find examples on using WCF (as shown in @CodeCaster's answer) or using HttpWebRequest / HttpWebResponse (I have more info in this answer: How to invoke webservice from SQL Server stored procedure ). Also, please be aware that sometimes you will need to also add the Serialization Assembly: Using Webservices and Xml Serialization in CLR Integration

编码和维护
Web 服务提供了一个很好的 API，但是如果您更改结构，您将不得不重新编译和重新部署至少其中的一部分.假设交换的信息足够简单，我倾向于认为将其视为标准 Web 请求会增加很多灵活性.您可以创建一个通用的 Web 请求函数(标量或 TVF)，它接受参数和 URI 并构造格式正确的 XML 请求并将其发送到 URI.然后它获取响应并仅返回 XML.因此您转移了一点责任，因为您现在需要解析 XML 响应而不是获取一个好的对象.但是，XML 在 SQL Server 中很容易解析，您可以在任意数量的地方重复使用此功能.而且，如果远程服务被更新，更新存储过程以更改传递给 Web 服务的查询字符串和/或更改 XML 响应的解析是一个简单的 ALTER PROCEDURE 并且应该易于测试.无需重新编译/重新部署 SQLCLR 程序集.

Coding and Maintenance
Web Services provide a nice API, but if you change the structure you will have to recompile and redeploy at least some part of this. Assuming the information being exchanged is simple enough, I tend to think that treating this as a standard web request adds a lot of flexibility. You can create a generic web request function (scalar or TVF) that takes in the parameters and URI and constructs the properly formatted XML request and sends it to the URI. It then gets the response and merely returns the XML. So you shift a little bit of the responsibility since you now need to parse the XML response rather than getting a nice object. But, XML is easy to parse in SQL Server, and you can re-use this function in any number of places. And, if the remote service is ever updated, updating a Stored Procedure to change the query string that is passed to the Web Service and/or change the parsing of the XML response is a simple ALTER PROCEDURE and should be easy to test. No need to recompile / redeploy the SQLCLR Assembly.

安全
无论您想要多么纯粹"的 Web 服务调用，安全方面的主要事情是不要偷懒并开启TRUSTWORTHY(也如@CodeCaster 答案中的链接页面，不幸的是，互联网上的大多数其他示例).确保此安全的正确方法是执行以下操作:

Security
Regardless of how "pure" of a web service call you want, the main thing, security wise, is to NOT be lazy and turn TRUSTWORTHY ON (as also shown in the linked page from @CodeCaster's answer, and unfortunately most other examples here on the interwebs). The proper way to make this secure is to do the following:

• 签署您的大会
• 在 [master] 数据库中，从程序集的 DLL 创建一个非对称密钥.
• 此外，在 [master] 中，从该非对称密钥创建登录
• 授予您的新登录 EXTERNAL ACCESS ASSEMBLY 权限
• 使用 EXTERNAL_ACCESS 的 PERMISSION_SET 创建你的程序集，不是 UNSAFE

• Sign your Assembly
• In the [master] database, create an Asymmetric Key from the DLL of your Assembly.
• Also, in [master], create a Login from that Asymmetric Key
• Grant your new Login the EXTERNAL ACCESS ASSEMBLY permission
• Create your Assembly with a PERMISSION_SET of EXTERNAL_ACCESS, not UNSAFE

欲知更多详情:

• 一般使用 SQLCLR，请访问:SQLCLR 信息
• 使用模块签名，请访问:模块签名信息
• 不使用 TRUSTWORTHY ON，请阅读:请停止使用模拟、可信和跨数据库所有权链接

• using SQLCLR in general, please visit: SQLCLR Info
• using Module Signing, please visit: Module Signing Info
• not using TRUSTWORTHY ON, please read: PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining

这篇关于从 SQL CLR 调用 Web 服务?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！