SQLCLR vs. SQL Server 2012 & 2014 & 2016, Part 7: “CLR strict security” – The Problem Continues … in the Past (Wait, What?!?)

(last updated: 2018-03-04 @ 14:56 EST / 2018-03-04 @ 19:56 UTC )

If the new “CLR strict security” Server-level configuration option in SQL Server 2017 hasn’t caused enough confusion and pain, then, as Eagle Man says, “I’ve got something for you”. You can now enable this super-fun setting in SQL Server 2012, 2014, and 2016. Why would anyone (intentionally) do this? Misery loves company? Perhaps. Practical jokes? Well, that just became a possibility 😉 . Who is Eagle Man? Watch the following commercial from 1993 and learn (though you will likely come away with more questions than answers):

No, that was not a joke. It was a real commercial for a real company. Likewise, the ability to enable “CLR strict security” (i.e. requiring that all Assemblies, even those marked as SAFE, be signed and have an associated Login that has been granted the UNSAFE ASSEMBLY permission) in several versions prior to SQL Server 2017 is also not a joke.

Why would Microsoft allow for enabling this setting in SQL Server 2012, 2014, and 2016? Most likely because these three versions are all using CLR 4.0, and hence potentially have the same security “issue” that caused Microsoft to come up with the new “CLR strict security” option in the first place. Allowing these three versions to have this option has two benefits (I suppose):

1. It strengthens the security (assuming that the risk is / was real)
2. It allows for preparing better for eventually moving up to SQL Server 2017 (and versions that follow). It allows for testing rollout scripts, etc. It also helps identify any SAFE Assemblies that are already loaded that are not signed, thereby forcing those to be fixed now and hence not become a migration issue.

Why not allow for enabling this setting in versions prior to SQL Server 2012? That is most likely due to this: the problem that “CLR strict security” was meant to help with is an issue starting in CLR 4.0, which is what SQL Server 2012 and newer use. SQL Server versions 2005, 2008, and 2008 R2 all used CLR 2.0 which fully supports CAS (Code Access Security).

What Has Been Stated

As far as I can tell, this option was only mentioned in a brief and not-well-maintained support article (linked at the end of this post). I say “not-well-maintained” because the article was clearly written when the option was only available to SQL Server 2016, and then only partially updated when the option was added to SQL Server 2014 and 2012.

Here is what has been “officially” documented:

1. “The feature is turned off for backward compatibility” (makes sense)
2. “Because of database level compatibility, the feature cannot be enabled by using the sp_configure ‘clr strict security’, 1 command.”
3. This cannot be enabled using DBCC TRACEON !
4. This can be enabled only by specifying a command-line Trace Flag, 6545, to be used at startup.
5. For more info on startup options, please see Database Engine Service Startup Options
6. This option became available in the following updates / patches:
   • Cumulative Update 7 for SQL Server 2016 RTM
   • Cumulative Update 4 for SQL Server 2016 Service Pack 1
   • Cumulative Update 6 for SQL Server 2014 SP2
   • Cumulative update 10 for SQL Server 2012 SP3

What Has Not Been Stated

I said that the support article was “brief” because there is quite a bit of information missing. What it fails to mention is the following:

1. It appears that this option became available on 2017-08-08 as that is the date that all four Cumulative Updates (CUs) noted above were released. Hence the update should be included in all updates released on or after that date. For example, it is available (I confirmed it) in the SP2 GDR update for SQL Server 2014 (build # 12.0.5214), which does not have the cumulative updates applied, but was released on 2018-01-06.
2. To enable this option automatically when the SQL Server service starts up, without using SQL Server Configuration Manager, you can edit the Registry (at your own risk, of course!!):
    
   KEY = HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL{version = 11, 12, or 13}.{instance_name}\MSSQLServer\Parameters
   VALUE (String) = SQLArg3 (for me it was “3”. Either pick the next highest “SQLArg” number, or use anything you like as it does not seem to really matter: I used “P.I.T.A.” 😉 )
   DATA = -T6545       (note: upper-case “T” and no spaces)

   Please note:

   • A restart of the SQL Server service is required for the change to take effect.
   • If you are using SQL Server Express LocalDB then:
     1. KEY = HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL{version = 11, 12, or 13}E.LOCALDB\MSSQLServer\Parameters
     2. you will need to create the “Parameters” key within the “MSSQLServer” key.
     3. to restart you either execute the SHUTDOWN command or go to a command prompt and run SQLLOCALDB stop {instance_name}. Then just reconnect and the instance should automatically start.
3. Whether enabled or disabled, the “CLR strict security” configuration option will not be visible…
   • via sp_configure:

     EXEC sp_configure N'show advanced options', 1; RECONFIGURE;
     EXEC sp_configure N'clr strict security';
     

     returns the following error:

     Msg 15123, Level 16, State 1, Procedure sp_configure, Line XXXXX [Batch Start Line YYYYY] The configuration option ‘clr strict security’ does not exist, or it may be an advanced option.

   • via sys.configurations:

     SELECT *
     FROM   sys.configurations sc
     WHERE  sc.[configuration_id] = 1587; -- clr strict security
     

     returns an empty result set.

4. The state of the “CLR strict security” configuration option is only visible via DBCC TRACESTATUS:

   SET NOCOUNT ON;
   DBCC TRACESTATUS(6545, -1) WITH NO_INFOMSGS; -- returns a table
   

   returns the following result set when the option has not been enabled:

   TraceFlag    Status    Global    Session
   6545         0         0         0
   

   and returns the following result set when the option has been enabled:

   TraceFlag    Status    Global    Session
   6545         1         1         0
   

5. When this option is enabled, attempting to load a SQLCLR Assembly that has a) not been signed, or b) does not have an associated Login, or c) that Login has not been granted the UNSAFE ASSEMBLY permission, will result in the following error:

   Msg 10314, Level 16, State 2, Line XXXXX
   An error occurred in the Microsoft .NET Framework while trying to load assembly id 0. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: {assembly_name}

   That error message appears to not have been intended for this purpose. The “assembly id” will always be “0” because it hasn’t been created (and this error is supposed to be telling us why it wasn’t created). Also, the error message mentions “PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE”, yet the Assembly that I was creating was marked as SAFE.

   Please note that this is not the same error message that you would get starting in SQL Server 2017. Under the same conditions in SQL Server 2017 you would receive the following error message:

   Msg 10343, Level 14, State 1, Line XXXXX
   CREATE or ALTER ASSEMBLY for assembly ‘{assembly_name}’ with the SAFE or EXTERNAL_ACCESS option failed because the ‘clr strict security’ option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly.

6. When this option is enabled, attempting to use functionality in an existing SQLCLR Assembly that has a) not been signed (typically SAFE Assemblies), or b) does not have an associated Login, or c) that Login has not been granted the UNSAFE ASSEMBLY permission, will result in the following error:

   Msg 10314, Level 16, State 11, Line XXXXX
   An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
   System.IO.FileLoadException: Could not load file or assembly ‘{assembly_name}, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

   That error message also mentions “PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE”. However, outside of the part in bold, it is the exact same error that you would receive starting in SQL Server 2017.

7. The “Trusted Assemblies” feature (really “Trusted Assembly Hashes”) that was added to SQL Server 2017 to provide an entirely unnecessary, clunky, and deficient “solution” for this error is not made available by enabling this option. Only the security setting of “CLR strict security” is enabled by this option, so do not expect that the sys.trusted_assemblies system catalog view, nor the system stored procedures that add and remove “trusted” Assemblies, will now exist. And this is a good thing, really, because the “Trusted Assemblies” feature is a hack that ideally wouldn’t even exist in SQL Server 2017.

Final Thoughts

The reason I mentioned in the very beginning that this can provide an opportunity for mischief / pranks is that the error message returned when this option is enabled:

1. is not the same error message returned for the same scenario in SQL Server 2017,
2. does not mention anything about “CLR strict security” or that SAFE Assemblies now need to follow the same rules (for being created!) as UNSAFE Assemblies,
3. and, (best of all) mentions the PERMISSION_SET of EXTERNAL_ACCESS, implying that it is a possible setting that might resolve the error, even though with “CLR strict security” enabled, only granting the Login associated with the Assembly (by being signed with the same Certificate or Asymmetric Key) the UNSAFE ASSEMBLY permission prevents the error.

In the spirit of that Eagle Man commercial: “Wow. Look at that not-entirely-helpful error message!” 😼

Also see:

• “Update adds the "CLR strict security" feature to SQL Server 2016” (Microsoft Support)
• SQL Server Configuration Manager
• Eagle Insurance (Wikipedia)