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

问题描述

我有一个SQL Server 2012的存储过程返回的表。我必须修改SP将其他值添加到返回的表。不幸的是，增加值来自于web服务的调用。从我的研究，我收集的主要方式做到这一点正在使用的OLE自动化过程（一条sp_OA ...）在SQL或SQLCLR存储过程。鉴于其中一条sp_OA ...程序运行的安全上下文，单返回值是一个VARCHAR（10）注册码，并调用该服务的少数（十到二十每小时），我猜SQLCLR方法要走的路。另外，web服务托管在我们的内联网，以及向外界不可访问。

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.

有没有更好的方式来完成我需要什么？更好的意义更好的性能，更好的安全性，更容易code和维护

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

推荐答案

请不要使用一条sp_OA * OLE自动化过程。他们似乎没有正式去precated，但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实例（我在这个答案的详细信息：<一href=\"http://stackoverflow.com/questions/18486928/how-to-invoke-webservice-from-sql-server-stored-procedure/30248335#30248335\">How调用从SQL Server存储过程 web服务）。另外，请注意，有时你会还需要添加序列化大会：的在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请求增加了很大的灵活性。可以创建取入的参数和URI和构建正确格式的XML请求，并将其发送到URI的通用web请求功能（标量或TVF）。然后它的反应，只是返回的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 ON （如链接页面还显示，从@ codeCaster的答案，很不幸，大多数其他的例子在这里的interwebs）。使这个安全正确的方法是做到以下几点：

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:

• 登入您的大会


• 在 [大师] 数据库，创建从大会的DLL中的非对称密钥。


• 此外， [大师] ，从创建一个登录的非对称密钥


• 授予新登录的外部访问ASSEMBLY 许可


• 与 PERMISSION_SET EXTERNAL_ACCESS 的创建大会的不的不安全

• 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

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