SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


HTTP Requests Using SQLCLR


HTTP Requests Using SQLCLR

Author
Message
Eilert Hjelmeseth
Eilert Hjelmeseth
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 229
Comments posted to this topic are about the item HTTP Requests Using SQLCLR
Sergio Lugo
Sergio Lugo
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 14
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!
Eilert Hjelmeseth
Eilert Hjelmeseth
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 229
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.
David Primus-231262
David Primus-231262
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 110
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.
yolousa
yolousa
SSC Veteran
SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)

Group: General Forum Members
Points: 273 Visits: 92
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.
jeffrey yao
jeffrey yao
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3476 Visits: 974
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 !



francesco.mantovani
francesco.mantovani
SSC-Addicted
SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)

Group: General Forum Members
Points: 419 Visits: 140
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.

Eilert Hjelmeseth
Eilert Hjelmeseth
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 229
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!
Eilert Hjelmeseth
Eilert Hjelmeseth
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 229
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!

francesco.mantovani
francesco.mantovani
SSC-Addicted
SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)

Group: General Forum Members
Points: 419 Visits: 140
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

francesco.mantovani
francesco.mantovani
SSC-Addicted
SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)

Group: General Forum Members
Points: 419 Visits: 140
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]Wink;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.

francesco.mantovani
francesco.mantovani
SSC-Addicted
SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)

Group: General Forum Members
Points: 419 Visits: 140
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

Eilert Hjelmeseth
Eilert Hjelmeseth
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 229
Ha, careful with those chairs!

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

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'
)


francesco.mantovani
francesco.mantovani
SSC-Addicted
SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)

Group: General Forum Members
Points: 419 Visits: 140
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?


Eilert Hjelmeseth
Eilert Hjelmeseth
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 229
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)

francesco.mantovani
francesco.mantovani
SSC-Addicted
SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)

Group: General Forum Members
Points: 419 Visits: 140
FIXED, man.... fixed....
I didn't put the DLL there and I didn't ran the query to enable it. Whistling

By the way the error

Incorrect syntax near the keyword 'with'. 

is definitely misleading.
It should say something like "hey dude, no DLL found" or "no idea what you are talking about". Instead it looked more like a typo in the query.

Anyway, this was an interesting chat indeed. I think I will use your DLL for my private work at home and I will keep using "MSXML2.ServerXMLHTTP.6.0" on machines where I'm not allowed to install DLLs.
So we have 2 solutions for 2 different scenarios .
This is great .

Thank you


Eilert Hjelmeseth
Eilert Hjelmeseth
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 229
I was going to suggest you could wrap things up in a table-valued function so you could query something like this:

SELECT * FROM [dbo].[ufn_example_by_area_amenity]('Auckland', 'cinema')


Function would look something like this:

CREATE FUNCTION [dbo].[ufn_example_by_area_amenity] 
(
@area NVARCHAR(MAX),
@amenity NVARCHAR(MAX)
)
RETURNS TABLE AS RETURN
(
SELECT
[type],
[id],
[lat],
[lon],
[amenity],
[name]
FROM OPENJSON
(
[dbo].[clr_http_request]
(
'GET',
'http://overpass-api.de/api/interpreter?',
CONCAT('data=[out:json];area[name=%22', @area, '%22]-%3E.a;(node(area.a)[amenity=', @amenity, '];way(area.a)[amenity=', @amenity, '];rel(area.a)[amenity=', @amenity, ']; );out;'),
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'
)
);

Solomon Rutzky
Solomon Rutzky
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12722 Visits: 3354
yolousa - Thursday, October 11, 2018 9:53 AM
...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 potential for harmful implementations. Again, interesting piece of code, nice writing, good job! Just understand the implications from an architectural standpoint, and be careful.

Hi yolousa. This warning is highly generic and not very helpful given that it could just as easily be said about: cursors, triggers, MERGE, LEFT JOINs, WITH (NOLOCK), index hints, etc, etc. If there is a specific danger you are aware of, then please provide that so this can be a meaningful warning. Otherwise it is unfair and misleading because the real issue comes down to appropriate usage. And the author was not suggesting anything that would be specifically concerning. Ever since SQLCLR was introduced in SQL Server 2005, many people have been unfairly negative about this feature due to either lack of information and/or misinformation. Just saying that it is "unsafe" doesn't really help. Your passive statement about HTTP libraries needing to be marked as UNSAFE is an example of this pervasive misinformation. All that is necessary is to mark the custom library as EXTERNAL_ACCESS, not UNSAFE. Yet posts such as this keep people from using this feature in situations where it would be appropriate. For example, francesco (in other posts in this thread) has stated that his company does not allow SQLCLR in Production, yet his current process -- still a web request -- is using OLE Automation stored procedures (sp_OA*) which are less stable, less secure, less efficient, and less functional (they don't work with any datatypes introduced in SQL Server 2005 or newer) than SQLCLR.

For more information on SQLCLR, including appropriate usage and security, please see the Stairway to SQLCLR series I am writing here on SQL Server Central: Stairway to SQLCLR.

Take care, Solomon..


SQL# - https://SQLsharp.com/ ( SQLCLR library of over 340 Functions and Procedures)
Sql Quantum Lift - https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap - https://SqlQuantumLeap.com/ ( blog )
Eilert Hjelmeseth
Eilert Hjelmeseth
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 229
I was thinking similarly about the OLE Automation. That is also a configuration which is turned off by default just like CLR and once enabled allows you to do so many different things while CLR being enabled by itself doesn't do much. One still need to register assemblies and create objects to use them. Especially if the TRUSTWORTHY database configuration is avoided I'm having a hard time thinking of anything inherently insecure.

That said, I'm very interested to hear about security concerns related to this [clr_http_request] function, provided they're specific and not a blanket "CLR opens up security concerns"
Hopefully nobody uses this function to mount DDOS attacks Blink (although, I imagine even that'd be rather ineffective)
Solomon Rutzky
Solomon Rutzky
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12722 Visits: 3354
francesco.mantovani - Thursday, October 11, 2018 9:08 PM
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
and from another post of yours (francesco):
Anyway, this was an interesting chat indeed. I think I will use your DLL for my private work at home and I will keep using "MSXML2.ServerXMLHTTP.6.0" on machines where I'm not allowed to install DLLs.
So we have 2 solutions for 2 different scenarios .So we have 2 solutions for 2 different scenarios .


Hi Eilert. Nice article. Good explanation and integration with JSON and XML. Just to clear up two things:
  1. No HttpWebRequest is not UNSAFE. It only requires EXTERNAL_ACCESS. I don't think I have ever found a need to set code using HttpWebRequest / HttpWebResponse to UNSAFE.
  2. The wording found in that SQL Server 2017 documentation stating that all SQLCLR code is considered "unsafe" (starting with SQL Server 2017, if "CLR strict security" is enabled, and it is by default), is misleading. It only means that all verification steps are processed, none are skipped, when loading SQLCLR assemblies, both at creation time (via CREATE ASSEMBLY / ALTER ASSEMBLY) and at runtime (when SQLCLR objects are accessed). Previously you could CREATE ASSEMBLY that contained "unsafe" code yet was marked as either SAFE or EXTERNAL_ACCESS, but it would fail at runtime if you either didn't have TRUSTWORTHY enabled (bad choice) or had not signed the assembly and had the matching signature-based Login that had been granted the UNSAFE ASSEMBLY permission. But if you mark your assembly as SAFE in SQL Server 2017 or 2019, then you will not be able to access the file system, network, etc. For information on the SQLCLR security changes starting in SQL Server 2017, including 2 approaches on applying proper security to your projects, even if using Visual Studio, please see my series of posts on this topic, starting with: SQLCLR vs. SQL Server 2017, Part 1: “CLR strict security” – The Problem.

Francesco: regarding your use of OLE Automation stored procedures (sp_OA*), if that is the better choice than using xp_cmdshell, then it is only barely better. Either way, both xp_cmdshell and sp_OA* procs are absolutely horrible choices compared to SQLCLR:
  1. There is no concept of security with either of those options: they are (for the most part) "all-or-none" in that once enabled, anyone with access can use of them any way that they like. You cannot restrict what .EXE is executed, or what objects are created via sp_OACreate, or what methods are called via sp_OAMethod. With SQLCLR you can be more selective about what code is loaded into the system, and what the overall reach of that code is (well, this is less secure now with the new "CLR strict security" since all assemblies need to be "allowed" to be UNSAFE, even if marked as SAFE, so it is harder to prevent someone from setting them to be UNSAFE, but you don't have to let folks change that setting either: you can mark it as SAFE and not allow anyone to ALTER ASSEMBLY)
  2. The security context of OLE automation process is always the service account for the SQLSERVER service. The security context of xp_cmdshell is either the service account for SQLSERVER, or the single proxy account, if set up for folks not in the "sysadmin" role. SQLCLR, by default, uses the security context of the SQLSERVER service account, but it has the ability to impersonate the login that is accessing the SQLCLR object (assuming it is a Windows Login and not a SQL Server Login). So you can differentiate between logins, allowing for more specific NTFS permissions.
  3. SQLCLR can use the MAX, XML, DATETIME2, etc dataypes added starting in SQL Server 2005. OLE Automatic procs cannot (at least not for parameters from everything I have read and seen). xp_cmdshell isn't even part of this conversion for this topic.
Honestly, I can't imagine a single scenario where sp_OA* procs would be a better choice than SQLCLR, especially in Production where security and stability are even more important. If your company is ok with sp_OA* and not ok with SQLCLR, then one or more folks are clearly misinformed, and not doing the company any good by such a backwards policy.

Take care,
Solomon..

P.S. If Eilert's code works for you then great (and it is more thorough than any other example I have ever seen posted). But just to mention, while not taking anything away from the usefulness of what the author has provided, there is a SQLCLR TVF called INET_GetWebPages in the SQL# library (that I wrote) that does this and handles a few more edge cases, as well as taking into account performance when the code is expected to be executed for concurrent sessions and hitting the same URI (the same host, that is). INET_GetWebPages is only available in the Full (i.e. paid-for) version, but that might be worth it depending on the need / usage.


SQL# - https://SQLsharp.com/ ( SQLCLR library of over 340 Functions and Procedures)
Sql Quantum Lift - https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap - https://SqlQuantumLeap.com/ ( blog )
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search