Call web service from SQL CLR?

Question

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.

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

Solution 1

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.

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

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.

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:

• 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

For more details 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

Solution 2

You can definitely call a WCF service using SQL CLR.

If you don't want that, you could write a Windows Service in C# that watches or polls the table for changes. Depending on how you implement this service, the reaction to a new record would be near immediate. Read also How to notify a windows service(c#) of a DB Table Change(sql 2005)?.

Then you can perform the service call from C#, perform the required work and store the result in the column.

When you require more information, for example extra variables obtained during the exchange, you could introduce a new table for storing that, and the actual result you're interested in. Then join that table from the table in your question.