Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Call web service from SQL CLR?

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

like image 997
EoRaptor013 Avatar asked Sep 30 '15 21:09

EoRaptor013


People also ask

Can SQL consume an API?

Microsoft SQL Server supports many languages and various client libraries. But nowadays, enterprises are shifting towards more flexible and easy-to-create API-driven solutions that allow them to access the server or data using a single REST API interface.

What is CLR used for in SQL Server?

For SQL Server users and application developers, CLR integration means that you can now write stored procedures, triggers, user-defined types, user-defined functions (scalar and table valued), and user-defined aggregate functions using any . NET Framework language, including Microsoft Visual Basic .

What does CLR stand for in SQL?

NET Framework common language runtime (CLR) manages and secures access between different types of CLR and non-CLR objects running within SQL Server. These objects may be called by a Transact-SQL statement or another CLR object running in the server.

What is sp_OACreate?

The sp_OACreate procedure, of course, creates the object, and so it expects an integer variable to be passed in as an output parameter to receive the reference to the object it creates. This integer actually references an internal wrapper object created by ODSOLE to encapsulate the COM object.


1 Answers

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
like image 173
Solomon Rutzky Avatar answered Sep 28 '22 10:09

Solomon Rutzky