I have a pre-SQL Server 2017 database that I restored into / upgraded to SQL Server 2017. In this database I have a SQLCLR Assembly. The Assembly is marked as SAFE
since it doesn't do anything requiring a higher level of permissions, and the database has TRUSTWORTHY
disabled / OFF
. The SQLCLR functions and stored procedures worked as expected before moving to SQL Server 2017, but now when I try to execute any of them I get the following error:
Msg 10314, Level 16, State 11, Server XXXXXXXXXXX, Line YYYYYY
An error occurred in the Microsoft .NET Framework while trying to load assembly id ZZZZZ. The server may be running out of resources, or the assembly may not be trusted. 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)
I have confirmed that CLR Integration / SQLCLR has been enabled on the server / instance.
Best Answer
The error is a result of the new
clr strict security
server-level configuration option in SQL Server 2017. This new security setting disallows any Assembly, even those marked asSAFE
, from being either created or loaded into memory for execution, unless:TRUSTWORTHY
isON
(don't do this as it is an unnecessary security risk) and the Login that is the database owner (i.e. same SID used by the[dbo]
User) needs to have theUNSAFE ASSEMBLY
permission (which it might already have if the owner issa
or a member of thesysadmin
fixed server role or maybe has theCONTROL SERVER
permission).clr strict security
is disabled /0
(don't do this as it is an unnecessary security risk)UNSAFE ASSEMBLY
permission (please do this)All you need to do in order to fix this situation are the following steps (which are not difficult and provide the highest level of security):
SAFE
Assemblies:[master]
(Public Key only!)UNSAFE ASSEMBLY
permissionA simple example of this (minus the optional backup and Private Key removal) would be:
A fully working example of this (minus the backup and Private Key removal) can be found on PasteBin:
Avoiding "Trusted Assemblies" - Demo
A detailed explanation of why you should use Certificates, and why you should not use "Trusted Assemblies", to fix this problem is provided in my blog post:
SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment
Also, please consider supporting my request to have Microsoft remove the new "Trusted Assemblies" feature given the numerous problems surrounding it (including security concerns), and the overall lack of benefits for having it:
Trusted Assemblies are more problematic yet less functional than Certificates - Please Remove