HTTP Requests Using SQLCLR

  • Comments posted to this topic are about the item HTTP Requests Using SQLCLR

  • Hello, interesting approach, Eilert!

    There are a couple errors in the script though:
    1. The parameter @convertResponseToBas64 is missing (it's not optional in the C# code).
    2. There is a typo in the first query (missing s in the variable name):

    FROM OPENJSON(@reponse_json)

    I would also suggest implementing some exception handling in the C# library. If an exception occurs (for instance, invalid URL, authentication problem, invalid request, etc.) the function will raise an exception and it won't return any response as it should according to the HTTP protocol specification.

    Other than that, really nice work!

  • Thanks for the great Feedback, Sergio!

    Late last night I noticed that I missed the @convertResponseToBase64 parameter and submitted a fix for the article. I also just now submitted a fix for the other issue you pointed out.

    Since I first submitted this article I've made some changes to the function I would like to post soon. Notably, I've added some error handling and I also changed the parameters to include a generic "options" parameter to pass in the things like timeout, base64 encoding, etc. Thought being if more features are added in over time and it requires adding new parameters that will break existing code since SQL Server requires all parameters to be specified when calling a function. A single "options" parameter that allows you to pass in various features you'd like to utilize solves that issue.

  • It has long been best practice to firewall databases from the internet. Both SSIS and SqlCLR are proscribed by most corporate governance practices from internet connections altogether, proxies block outbound traffic altogether. Ideally WSUS servers handle service packs and update installations in order to protect the database. Sacrificial app servers are much better suited handling httpwebrequests.

  • This is fun and interesting as far as examples go, but the overall idea of integrating Web API calls in database implementation may do more harm than good. There are levels of "safety" for CLR code and I am almost sure HTTP libraries will require CLR integration as unsafe. That in itself is not bad but has pottential for harmful implementations. Again, interesting piece of code, nice writting, good job! Just understand the implications from an architectural standpoint, and be careful.

  • This is a very interesting topic. CLR integration may not necessarily allowed in production most of the cases, but CLR integration can be of great help in non-production environments, such as importing data from websites, or utilizing the external resources (computing some complex stuff and getting back data etc).
    There can be chances that we integrate the internal websites with our internal T-SQL solutions for company internal applications. So in short, I am always a big fan of CLR integration which provides more opportunities for us to choose.

    Good article Eilert !

  • Good stuff! 
    By the way if I copy/paste your code to SSMS I receive: 
    Msg 319, Level 15, State 1, Line 22
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
    Msg 319, Level 15, State 1, Line 23
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

  • Indeed, some policies (especially in larger corporate environments) may to some extent block this method. For those that allow it this can be a very convenient tool, though!

    This does fall into the "UNSAFE" category, but then all CLR in starting in SQL Server 2017 is considered UNSAFE. See CLR strict security: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/clr-strict-security

    Additionally, I was careful to do some homework on this one and find out how to "properly" sign the assembly so I wouldn't need to do things like set TRUSTWORTHY on the database.
    CLR, just like other excellent features of SQL Server like dynamic sql, xp_cmdshell, OPENQUERY, etc. need to be handled with care!

  • francesco.mantovani - Thursday, October 11, 2018 1:59 PM

    Good stuff! 
    By the way if I copy/paste your code to SSMS I receive: 
    Msg 319, Level 15, State 1, Line 22
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
    Msg 319, Level 15, State 1, Line 23
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

    Which version of SQL Server are you using? If it's prior to SQL Server 2016, then the examples using "OPENJSON(...) WITH (...) will not work since that JSON support was introduced in SQL Server 2016.

    Also, thanks everyone for all the kind words!

  • SQL Server 2016:

    Microsoft SQL Server Management Studio                        13.0.16106.4
    Microsoft Analysis Services Client Tools                        13.0.1700.441
    Microsoft Data Access Components (MDAC)                        10.0.17134.1
    Microsoft MSXML                        3.0 4.0 6.0
    Microsoft Internet Explorer                        9.11.17134.0
    Microsoft .NET Framework                        4.0.30319.42000
    Operating System                        6.3.17134

  • Just to be out of confusion: OPENJSON is working because my archaic query still works.
    Look how horrible it is, with that unhealthy "MSXML2.ServerXMLHTTP.6.0", it looks like an old petrol engine from the early 1900:

    Declare @Object as Int;
    DECLARE @hr int
    Declare @json as table(Json_Table nvarchar(max))

    Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
    Exec @hr=sp_OAMethod @Object, 'open', NULL, 'get',
          'http://overpass-api.de/api/interpreter?data=[out:json];area[name=%22Auckland%22]-%3E.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema];);out;', --Your Web Service Url (invoked)
          'false'
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
    Exec @hr=sp_OAMethod @Object, 'send'
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
    Exec @hr=sp_OAMethod @Object, 'responseText', @json OUTPUT
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

    INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'
    -- select the JSON string
    select * from @json
    -- Parse the JSON string
    SELECT * FROM OPENJSON((select * from @json), N'$.elements')
    WITH ( 
      [type] nvarchar(max) N'$.type' ,
      [id] nvarchar(max) N'$.id',
      [lat] nvarchar(max) N'$.lat',
      [lon] nvarchar(max) N'$.lon',
      [amenity] nvarchar(max) N'$.tags.amenity',
      [name] nvarchar(max) N'$.tags.name' 
    )
    EXEC sp_OADestroy @Object

    I like you code so much that if I arrive to make it work I'm going to use it on daily basis.
    My colleagues and I will jump on the chairs like the monkeys from Space Odyssey, when they see a spaceship arriving on their planet from a remote galaxy. 

    I hope you understand how trilled I am.

  • Eilert Hjelmeseth - Thursday, October 11, 2018 8:50 PM

    Indeed, some policies (especially in larger corporate environments) may to some extent block this method. For those that allow it this can be a very convenient tool, though!

    This does fall into the "UNSAFE" category, but then all CLR in starting in SQL Server 2017 is considered UNSAFE. See CLR strict security: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/clr-strict-security

    Additionally, I was careful to do some homework on this one and find out how to "properly" sign the assembly so I wouldn't need to do things like set TRUSTWORTHY on the database.
    CLR, just like other excellent features of SQL Server like dynamic sql, xp_cmdshell, OPENQUERY, etc. need to be handled with care!

    yeah, that's why my horrible script you can see I posted on this page doesn't use xp_cmdshell but sp_OACreate

  • Ha, careful with those chairs!

    I was able to pretty quickly take your code and use this function to query it 🙂

    SELECT
      *
    FROM OPENJSON
       (
         [dbo].[clr_http_request]('GET', 'http://overpass-api.de/api/interpreter?data=[out:json];area[name=%22Auckland%22]-%3E.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema]; );out;', NULL, NULL, 10000, 0, 0).value('/Response[1]/Body[1]', 'NVARCHAR(MAX)'),
          '$.elements'
       )
      WITH
       (
          [type] nvarchar(max) N'$.type' ,
          [id] nvarchar(max) N'$.id',
          [lat] nvarchar(max) N'$.lat',
          [lon] nvarchar(max) N'$.lon',
          [amenity] nvarchar(max) N'$.tags.amenity',
          [name] nvarchar(max) N'$.tags.name'
       )


  • I tried this at home on my SQL Server 2017:

    Microsoft SQL Server Management Studio                        14.0.17285.0
    Microsoft Analysis Services Client Tools                        14.0.1016.283
    Microsoft Data Access Components (MDAC)                        10.0.17763.1
    Microsoft MSXML                        3.0 6.0
    Microsoft Internet Explorer                        9.11.17763.0
    Microsoft .NET Framework                        4.0.30319.42000
    Operating System                        6.3.17763

    And I receive the same error.

    For instance, what is:
    [dbo].[clr_http_request]
    Do I need to activate SQLCLR  first?
    I run :
    EXEC sp_configure 'clr enabled';
    EXEC sp_configure 'clr enabled' , '1';
    RECONFIGURE; 

    But I still see the 2 errors on line 21 ans 22.

    Very kind from you to write the code for me, I copy/pasted it into my SSMS and I receive 1 error on line 9:
    Msg 319, Level 15, State 1, Line 9
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

    I'm experiencing the same problem on 2 different environment?
    Maybe there is something I need to turn ON or OFF?

  • For instance, what is:
    [dbo].[clr_http_request]


    [dbo].[clr_http_request] is the function this article describes. The steps to create the function are noted at the bottom of the article. I'll copy here for convenience. You can also download ClrHttpRequest.zip at the bottom of the article.

    To create the function, follow these steps using files found in the attached ClrHttpRequest.zip:
    Copy ClrHttpRequest.dll to C:\ on the machine running the SQL Server instance

    Run code in clr_http_request.sql to create the function (tweaking as necessary to adjust which database should be used).
    Note: This script includes code to change configuration of the SQL Server instance by enabling the CLR integration.

    Very kind from you to write the code for me, I copy/pasted it into my SSMS and I receive 1 error on line 9:
    Msg 319, Level 15, State 1, Line 9
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

    This error is very strange to me. The article was intiially published with a typo in the examples where it was missing an "s" in @response_json, and for me that was giving this error:

    Msg 137, Level 15, State 2, Line 28
    Must declare the scalar variable "@reponse_json".
    Msg 319, Level 15, State 1, Line 28
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
    Msg 319, Level 15, State 1, Line 29
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

    However, I'm not able to get any similar error with the code I just provided.
    If you have created the [dbo].[clr_http_request] function, what happens when you just run this? 

    SELECT [dbo].[clr_http_request]('GET', 'http://overpass-api.de/api/interpreter?data=[out:json];area[name=%22Auckland%22]-%3E.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema]; );out;', NULL, NULL, 10000, 0, 0)

Viewing 15 posts - 1 through 15 (of 52 total)

You must be logged in to reply to this topic. Login to reply