SQLCLR vs. SQL Server 2012 & 2014 & 2016, Part 7: “CLR strict security” – The Problem Conti…

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
  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 (and is included in all updates since these):

    • 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. 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_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL{version = 11, 12, or 13}.{instance_name}MSSQLServerParameters

    VALUE =
    (for me it was “3”. If you already set one or more Trace Flags, use the next available “Arg#” — i.e. MAX(SQLArg#) + 1
    DATA =

  2. 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.

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

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

    returns the following result set:

    TraceFlag    Status    Global    Session
    6545         0         0         0

    This is a global Trace Flag, so if the option is enabled, there will be a 1
    in the “Global” column.

  4. 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}

    Please note that this error message is not
    the same error message that you would get starting in SQL Server 2017. The error message you would receive in SQL Server 2017 would specifically mention “trusted assemblies”.

  5. 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
  3. and, (best of all) mentions the PERMISSION_SET
    , 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:

SQLServerCentral稿源:SQLServerCentral (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » SQLCLR vs. SQL Server 2012 & 2014 & 2016, Part 7: “CLR strict security” – The Problem Conti…

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录