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:
- 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.
- 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:
- 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)
- 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.
- 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.