With the additional (and annoying) configuration step required to get SQLCLR Assemblies to load starting in SQL Server 2017, some people have been wondering what is going on with SQLCLR. Considering that this new restriction is the only real change to SQLCLR since SQL Server 2012 (three versions ago), then with (limited) support for languages such as R (starting in SQL Server 2016) and Python (starting in SQL Server 2017) being added, it might even look like SQLCLR is being deprecated (i.e. phased-out) in favor of these new languages.
Could this be true? There is no official indication, but could it be unofficially / “effectively” deprecated? Well, let’s take a look.
As someone who likes CLR, I want both CLR and Machine Learning Services to co-exist. This would be true even if ML Services supported F# and the lesser .NET languages.
The fact that no data has been collected for this type throughout a good cross-section of their customers really confirmed for me that this isn’t something that is commonly a problem, so I was intrigued by the fact that this specific workload was now exhibiting problems with this wait. I wasn’t sure where to go to further investigate the issue so I replied to the email saying I was sorry that I couldn’t help further because I didn’t have any idea what would be causing literally dozens of threads performing spatial queries to all of sudden start having to wait for 2-4 seconds at a time on this wait type.
A day later, I received a kind follow-up email from the person that asked the question that informed me that they had resolved the problem. Indeed, nothing in the actual application workload had changed, but there was a change to the environment that occurred. A third-party software package was installed on all of the servers in their infrastructure by their security team, and this software was collecting data at five-minute intervals and causing the .NET garbage collection processing to run incredibly aggressively and “go nuts” as they said. Armed with this information and some of my past knowledge of .NET development I decided I wanted to play around with this some and see if I could reproduce the behavior and how we could go about troubleshooting the causes further.
Read the whole thing if you use CLR.
This code will add one function and one procedure in SQL Server that implements the following CURL functions:
CURL.XGET – function that calls API on some http endpoint using get method and fetches the response. It has two parameters:
- @H representing the header information that should be sent to remote endpoint (null for none).
- @url representing the endpoint where the Http request should be sent.
CURL.XPOST – procedure that sends text to some http endpoint using post method and prints response. It has three parameters:
@H representing the header information that should be sent to the remote endpoint (null for none).
@d representing the data that should be sent to remote endpoint in the request body.
@url representing the endpoint where the Http request should be sent.
Click through for the process. I’ve never been afraid of CLR and it is a rather useful tool, but pushing for CLR is definitely like swimming upstream.
- Greatly reduced size (by approx. 310 kb) of main SQL# Assembly by moving LookUp category into its own Assembly: SQL#.LookUps. This will improve initial load times and won’t waste much memory when not using the LookUp functions.
Added explicit support for TLS 1.1 and TLS 1.2 protocols
Increased default “Connection Limit” for URIs to 20 from the .NET default of 2. This will reduce performance bottlenecks from concurrent access to the same URI.
SQL# is also the first place where I found a decent median function for SQL Server.
Have you ever had the need to use Regular Expressions directly in SQL Server? I sometimes hear or see others refer to using RegEx in TSQL. But I always assume they’re talking about the TSQL LIKE operator, because RegEx isn’t natively supported. In TSQL’s defence, you can get a lot of mileage out of LIKE and some clever pattern matching strings, even though it’s not authentic RegEx. You can leverage RegEx libraries in the .NET Framework via a CLR stored procedure. You should also be able to do something similar with an old-school extended stored procedure.
I discussed all of this during a recent interview. It was a day or two afterwards (of course) when it dawned on me that there’s another way to leverage RegEx from TSQL: the R language. Prior to this mini-revelation, I had always thought of R (and Python) as strictly a means to an end for Data Science and related disciplines. Now I am thinking I’ve been looking at R and Python through too narrow of a lens and I should take a larger view.
I think I’d prefer CLR for this because there’s additional overhead to making R Services calls, but it’s a clever use of R Services.
Ownership chaining is quite handy as it makes it easier to not grant explicit permissions on base objects (i.e. Tables, etc) to everyone. Instead, you just grant EXECUTE / SELECTpermissions to Stored Procedures, Views, etc.
However, one situation where ownership chaining does not work is when using Dynamic SQL. And, any SQL submitted by a SQLCLR object is, by its very nature, Dynamic SQL. Hence, any SQLCLR objects that a) do any data access, even just SELECT statements, and b) will be executed by a User that is neither the owner of the objects being accessed nor one that has been granted permissions to the sub-objects, needs to consider module signing in order to maintain good and proper security practices. BUT, the catch here is that in order to sign any Assembly’s T-SQL wrapper objects, that Assembly needs to have been signed with a Strong Name Key or Certificate prior to being loaded into SQL Server. Neither “Trusted Assemblies” nor even signing the Assembly with a Certificate within SQL Server suffices for this purpose, as we will see below.
Read on for more details.
Considering that “Trusted Assemblies” is entirely worthless within the context of the regular (i.e. non-Azure) SQL Server, does the same argument of “just use the existing Certificates and Module Signing functionality” also apply here?
Well, this just happens to be the one area where there might possibly be, in the worst-case scenario, some argument made for keeping this feature. The problem with applying the same Certificate / ADD SIGNATURE logic to Azure SQL Database is that you can’t create a signature-based Login (from either a Certificate or an Asymmetric Key) in that environment. Ouch! That is definitely a nail-in-the-coffin for the Certificate idea. Ok, so assuming that “Trusted Assemblies” would work in this scenario, is it an acceptable solution to the problem?
This has been a thought-provoking series so far. If you agree with his conclusions, Solomon has a Connect item he’d like you to upvote.
Hopefully, Microsoft removes all traces of “Trusted Assemblies” (as I have suggested here). In either case, please just use Certificates (and possibly Asymmetric Keys, depending on your preference and situation) as I have demonstrated in these past three posts (i.e. Parts 2, 3, and 4). Even better, especially for those using SSDT, would be if Microsoft implemented my suggestion to allow Asymmetric Keys to be created from a binary hex bytes string. But, even without that convenience, there is still no reason to ever, ever, use the “Trusted Assemblies” feature.
He’s given three alternatives so far, so if you’re interested in CLR security, there’s plenty of food for thought.
This new requirement prevents the technique described towards the end of Part 1 from working. That technique uses a SAFE Assembly as an indirect means of creating the Asymmetric Key to create the Login from. That worked perfectly prior to SQL Server 2017, but now even SAFE Assemblies require that the signature-based Login be created first, which now puts us in a whole chicken-egg paradox.
Before proceeding to the solution, it should be noted that yes, Microsoft has, as of RC2 (released on 2017-08-02), provided a kinda/sorta “fix” for this that allows for creating an Assembly without having the signature-based Login. HOWEVER, that “fix” is absolutely horrible, convoluted, and unnecessary. It should not be used by anyone. Ever! In fact, it should be completely removed and forgotten about. In no uncertain terms: it is not an option! To help clarify, I am being intentionally vague about that new feature here (and in Part 1) so as not to distract from these two solutions (this post and Part 3) that do not promote bad practices; it will be covered starting in Part 4.
Solomon outlines one approach to dealing with CLR security changes, though it’s a bit lengthy.
What Microsoft introduces in SQL Server 2017 RC1, is something I refer to as whitelisting. It is somewhat similar to the
TRUSTWORTHYsetting, where you indicate that a database is to be trusted. But instead of doing it on the database level, you do it per assembly.
To whitelist in SQL Server 2017 RC1, you use the system stored procedure
sys.sp_add_trusted_assembly. As the name implies the procedure adds an assembly to a list of “trusted” assemblies. By marking an assembly as trusted, SQL Server will allow it to be loaded when
clr strict securityis on (on by default), even if:
the assembly is not signed, and
the database where you want to deploy it to is not
With the elimination of the CAS model finally hitting CLR, this is probably going to be one of the easier ways for DBAs to move forward with CLR in the future.