SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment (Msg 10314)

(last updated: 2018-03-20 @ 13:45 ET / 2018-03-20 @ 17:45 UTC)


Before we look at “Trusted Assemblies”, let’s take a moment to review the context of the situation. First, the “Good”: SQL Server 2017 introduces many awesome features including Linux as a platform, adaptive query processing, interleaved execution for Multi-statement TVFs, and a lot more. Next, the “Bad”: SQL Server 2017 also comes with the first update to SQLCLR in a while, but the “clr strict security” sever-level configuration option is no cause for celebration. Fortunately, I provided guidance for working within this new constraint, using Visual Studio / SSDT or not, in Parts 2 and 3 of this series. Now, finally, …

The Ugle̅e̅

“Trusted Assemblies”, introduced in Release Candidate 1 (RC1) of SQL Server 2017, released on 2017-07-17, is a misguided attempt at getting SQLCLR to work while keeping both clr strict security enabled and TRUSTWORTHY disabled. In order to understand why this new feature is misguided (and should be removed), we need to ask:

  1. What are “Trusted Assemblies”?
  2. What problem(s) is it trying to solve?
  3. Are there any problems with this approach?
  4. Is there a better approach?

What is the “Trusted Assemblies” feature?

When using Assemblies marked as either EXTERNAL_ACCESS or UNSAFE, you need to have the Database property of TRUSTWORTHY enabled (bad), or sign the Assembly, create a signature-based Login, and grant that Login EXTERNAL ACCESS ASSEMBLY xor UNSAFE ASSEMBLY (good). In SQL Server 2017, SAFE Assemblies now have those same requirements when clr strict security is enabled (recommended and default).

“Trusted Assemblies” is a way of getting SQLCLR to work without meeting either of those requirements. It lets you whitelist Assemblies so that you can keep the recommended security settings without needing to sign the Assemblies or create the associated signature-based Login. All you need to do is register the SHA-512 hash of the Assembly, and then that Assembly will be “trusted” and hence allowed to operate.

What is “Trusted Assemblies” trying to solve?

Here are the various scenarios that we will encounter, and if “Trusted Assemblies” helps:

  1. New development

    Yes, loading any Assembly is now more complicated. Yes, having the hash of the Assembly registered as “Trusted” allows the Assembly to be loaded without being signed.
    HOWEVER, “Trusted Assemblies” provides no benefit here over Certificates. For people using SSDT there is no difference since SSDT doesn’t handle either Certificates or registering the “trusted” hash. And if you were going to manipulate SSDT into registering the “Trusted” hash, then that’s no different than what you would do to get Certificates working. Deploying Assemblies manually is essentially the same in both cases as well.
    No time or effort is saved, and nothing is gained, by using “Trusted Assemblies”. AND IN FACT, while both creating the Certificate and registering the “trusted” hash can be done via VARBINARY literal, both require external functionality to get that hex bytes string: either something like BinaryFormatter — a free, command-line utility that I wrote — or a similar utility to calculate the SHA-512 hash. BUT, Certificates have the advantage of being able to be created from a file, which doesn’t require any functionality that is not already natively provided.
    So no, this is not why “Trusted Assemblies” exists.

  2. Existing Assemblies in Databases upgraded to, or restored into, SQL Server 2017

      Whether using TRUSTWORTHY ON or a signature-based Login, the security issue is being handled in a manner that will continue working.
    2. SAFE that is signed and has a corresponding signature-based Login:
      Here the security issue is being handled in a manner that will continue working.
    3. SAFE that is not signed:
      HERE is the problem: Assemblies that used to work — there was no need for them to be signed — will no longer work. There is even a warning about this on the documentation page for CLR strict security:
      After enabling strict security, any assemblies that are not signed will fail to load. You must either alter or drop and recreate each assembly so that it is signed with a certificate or asymmetric key that has a corresponding login with the UNSAFE ASSEMBLY permission on the server.
      Attempting to use SQLCLR functionality in this scenario returns 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 65546. 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 ‘sql2017_trustedassemblies, Version=, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

      It isn’t always possible to “alter or drop and recreate each assembly”, especially if the Assembly was provided by a 3rd party, or someone who no longer works for the company and didn’t tell anyone where the source code is (but that never happens, right?). But with “Trusted Assemblies” you don’t need to do either of those things. Situation resolved, right?

Are There Any Problems With “Trusted Assemblies”?

There are two types of problems with “Trusted Assemblies”. This first set of problems are ones that are consequences of having, and using, this new feature.

  1. It promotes bad security practices:
    1. Microsoft has already, unintentionally, been pushing people to enable TRUSTWORTHY by not providing an easy means of accomplishing the ideal signature-based approach, and not doing nearly enough to educate everyone on how signatures, and module signing in general, work (both of which I am trying to rectify through these blog posts, Connect suggestions such as this and this, giving presentations on the topic, and creating a site focused on this topic: ModuleSigning.Info).
      “Trusted Assemblies” doesn’t truly alleviate deployment issues arising from “clr strict security” since it requires non-natively provided functionality: generating the SHA-512 hash (SHA2_512 as far as HASHBYTES is concerned) of the Assembly. SSDT could do this, but it doesn’t (which makes sense enough as this feature wasn’t intended to solve the deployment issue). So anyone using SSDT is still in the same situation today: if something won’t deploy then most people will likely just enable TRUSTWORTHY since that is something SSDT can handle.
    2. And if Microsoft does provide an easy way to register an Assembly’s hash as “trusted”, would that help? Not really. While it would reduce the number of Databases that have TRUSTWORTHY enabled (a good thing), it would also provide a means of avoiding signing Assemblies, thereby pushing most people (probably) to never sign their SQLCLR Assemblies. That is a bad thing, and the exact opposite direction that Microsoft should be moving in.
  2. Speaking of security: Hashes, by themselves, are not security.
    1. They are just a simple mechanism for quickly determining if one value is different than another. But they cannot guarantee that two initial (pre-hashed) values are identical, due to collisions. Yes, they can provide a reasonable level of trust that two initial values are the same, but here we need more than that.
    2. Hashes do not contain any identifying information (i.e. who provided that value).
    3. Even though it is infeasible to cause a collision on a SHA-512 hash by today’s computing standards, between increases in processing power and people constantly looking for, and potentially finding, short-cuts to doing such things, it is only a matter of time before this becomes feasible. And, given that people are still running SQL Server 2000, and maybe even 7.0, this “feature” will be in place well past when it becomes deprecated.
  3. Unforeseen consequence over time: potentially ever-increasing junk meta data.
    1. This value will change every time an Assembly is compiled. Continuous Integration will likely be responsible for a lot of obsolete “trusted” hashes.
    2. Are people going to be good and clean up their old, unused hashes? That will require knowledge of the old Assembly’s hash, and once an Assembly is compiled, there is no “old” version to calculate the hash of. The only “old” version, prior to running the deployment script, is the value currently in sys.assembly_files.
    3. A deployment script can be written to calculate the hash of the current Assembly, based on its name, and then unregister that hash before registering the new one. All fixed? Not quite.
      sys.trusted_assemblies is server-level meta data. Meaning, the “trusted” hashes added to that list cover all databases in that Instance.
      What happens when the same Assembly is used in more than one Database? Initially everything seems fine. But then update the Assembly in just one of those Databases for some good ‘ol A/B testing. The deployment of the updated Assembly can’t remove the “old” hash because it’s still “current” for the other Databases containing the non-updated version of that Assembly and removing it would break the other Databases. So how can you guarantee that a particular “trusted” hash isn’t still in use in another Database? You can only guarantee that if Assemblies are only ever deployed to a single Database.
      And then there is still the risk, even if very minimal, that there is a hash collision and two different Assemblies share the same hash and you unregister it as you drop one of those Assemblies. You can say that this probably won’t happen, but you can’t say that it can’t happen.
  4. Trusted Assemblies don’t allow for module signing (of the exposed functions / stored procedures / triggers / types). Explanation and example code for this topic can be found in Part 6 of this series.

One might argue that these problems are all a result of using “Trusted Assemblies” to create / import Assemblies, which is not its intended purpose. To that I would say: intended or not, it will be used in that manner. And in fact, people are already talking about using it this way (i.e. not just for getting already imported, unsigned, SAFE Assemblies to work).

Is there a better approach?

The other type of problem is when things change and get more complicated for no good reason. Changes, even those that add some amount of complexity, are acceptable if all other options are more expensive (based on cost of time, money, resources, side-effects, etc). So, is “Trusted Assemblies” the most cost-effective solution to this problem? Well…

If you recall, the main reason that “Trusted Assemblies” exists is contained in the warning noted above, in part stating: “You must either alter or drop and recreate each assembly so that it is signed…” (emphasis mine). While it is true that each Assembly needs to be signed, it is not true that they need to be altered or dropped and recreated. Instead, we can sign each Assembly with a built-in function that has been around since SQLCLR was introduced, the appropriately named ADD SIGNATURE! To be fair, this is a non-obvious use of ADD SIGNATURE as it probably has rarely, if ever, been used in this way (I’ve never needed to do this). But yes, this actually works, and the demo script (linked below) let’s you see for yourself.

Solution Details

All you need to do is:

  1. Create a Certificate in the Database containing the Assembly
  2. Sign one or more Assemblies
  3. Copy the Certificate to [master] (but not the Private Key!)
  4. Create a Login from the Certificate
  5. Grant the signature-based Login UNSAFE ASSEMBLY

That. Is. It. And compared to “Trusted Assemblies”, this approach:

  • uses existing functionality
  • uses actual security
  • only requires one Certificate no matter how many Assemblies exist in any number of Databases.

    If you have multiple Databases containing unsigned, SAFE Assemblies, just copy the Certificate into each one, but those will need the Private Key copied as well.
    Already have other Assemblies that are signed? As long as the Certificate or Asymmetric Key has the Private Key with it, then you can use the existing Certificate or Asymmetric Key and there is no need create anything. In this case, you just need to do step 2 above — sign the Assembly.

While the demo only shows a Certificate being created in SQL Server, this technique works just as well if the Certificate is created outside of SQL Server and then imported into SQL Server (imported from the .cer and .pvk files and not from a DLL).

But Wait, There’s More!

Yes, it gets even better: for new deployments, the Certificate doesn’t change! Unlike the “trusted” hash value that changes with each compile, the Certificate, once imported into SQL Server, doesn’t require any additional administration or thought.

And, even betterer (not a typo ;-): Since Certificates and ADD SIGNATURE already exist in SQL Server, anyone planning on upgrading can create the Certificate(s) and sign the Assemblies RIGHT NOW, thereby PREVENTING the situation where SQLCLR breaks in upgraded and restored Databases with unsigned, SAFE Assemblies. Doing this step ahead of time is one less thing to worry about during the actual upgrade. It could even be incorporated into the Upgrade Advisor as a warning of something that will need fixin. In either case, this technique should replace the current “Important” notice on the CLR strict security documentation page.

With “Trusted Assemblies”, you can pre-calculate the hashes (assuming you are on at least SQL Server 2012 as that is the first version to support SHA2_512 ), but you can’t apply the fix until the Database is upgraded or restored. Hence it will be an additional post-upgrade. Don’t forget to run it!


A fully working example of this technique has been posted to PasteBin. The C# / .NET code used in that demo script is just the following simple scalar UDF:

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class SQL2017vsSQLCLR
    [SqlFunction(IsDeterministic = true)]
    public static SqlString Date_Format(SqlDateTime DateToFormat,
        SqlString FormatString)
        return new SqlString(


In an attempt to not have everyone either disable the new “clr strict security” server configuration option, or enable TRUSTWORTHY, Microsoft provided a feature that:

  • requires a yet-to-be-implemented change in SSDT (or custom programming for manual deployments),
  • potentially generates a pile of junk meta-data (over time),
  • requires sysadmin rights to administer,
  • adds an additional point-of-failure / potential attack vector,
  • is less secure than Certificates (which have been around since SQL Server 2005, or at the very least since SQL Server 2012 when it became possible to create a Certificate from a VARBINARY literal), and
  • can’t prevent SQLCLR objects (in unsigned, SAFE Assemblies) from breaking upon upgrade.

“Trusted Assemblies” was technical debt from the moment it was first mentioned in a planning meeting, and any time spent on planning, designing, developing, testing, and documenting this “feature” was time that should have been spent either on something useful, or at least letting folks go home early (i.e. not staying late).

And now our feeling buddies are:

😤   😖   😠   🤮

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.

Next time we will see how to automate the Certificate-based fix that I described above.

Also see:

15 thoughts on “SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment (Msg 10314)”

  1. […] 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. […]

  2. Hi there, I’ve got to defend hashes which you misrepresent in one particular paragraph above:

    “Speaking of security: Hashes are not security.”

    Hashes are an integral part of modern cryptographic security. As much as any other piece of technology, they ARE security. The signatures you’re interested in are themselves built on top of hashes; they hash the initial value and sign that using the certificate private key.

    “They are just a simple mechanism for quickly determining if one value is different than another. But they cannot guarantee that two initial (pre-hashed) values are identical, due to collisions. Yes, they can provide a reasonable level of trust that two initial values are the same, but here we need more than that.”

    Modern hashing algorithms actually provide an enormous level of trust. Given two identical SHA-512 hashes, you can pretty much guarantee the initial values were the same. Again, this is why signatures use hashes internally; it’s impractically slow to sign the whole artefact using public key cryptography, and a hash of it is much smaller and effectively the same. For reference, last year Google found an actual collision in the known-weak algorithm SHA-1, and it still took them approximately 6500 years of compute time to do it. SHA-512 doesn’t have the same weakness SHA-1 did; with present mathematics the sun will burn out before we find a collision in it.

    1. Hi Trent, and thanks for the feedback. Regarding your first point, I may have been unclear. I was not saying that hashes have no place here, just that they aren’t the total package. As you stated, they are an integral part of various security mechanisms: certificates, verifying downloads, etc. I updated that part to be clear that I am objecting to them being used by themselves. You might still disagree, but at least my point should be clearer now.

      Regarding your second point, I did say that hashes could be used to reasonably determine sameness, because yes, they are used in that way. But they are a convenience due to what you pointed out: impractical to always use the full key. But that part of signatures can be upgraded to use stronger hashes over time because at some point causing collisions will become within reach, at least for some. It will probably be a while before personal computers are fast enough for script-kiddies to do this, but folks who are determined will be able to distribute the work, and the type of collision I am talking about should be easier to force than forging a signature or creating a different PDF to hash the same (what Google did). So yes, SHA2 is much improved over SHA1, but again, the argument of “can’t break it today” is pretty flimsy given that these systems last for a long time. I am not concerned about today, or even a few months from now. I am concerned about years down the road when this stupid “feature” is still in place, even though SQL Server 2017 is out of support. It wouldn’t be so bad if they had tied the hash to some other data point, such as the size in bytes of the Assembly. Forcing a collision of a different item having the same number of bytes would be way more difficult.

      1. You’ve got valid arguments against the Trusted Assemblies option, but weak security still isn’t one of them. A SHA-512 hash is astronomically better than “reasonably” good for determining sameness. We don’t need more assurance of sameness. Any arguments about it being future proof can be leveled at signatures too. Just like a configured “safe” hash might become unsafe due to crypto research, so might a validly signed DLL, or signing certificate, because they also rely on hashes being reliable.

        The principle of “can’t break it today” is just fine for security because tomorrow we can migrate/invalidate security as needed. In the event of some sort of crypto breakthrough, a patch to SQL Server can invalidate registered SHA-512 hashes just like it would invalidate registered code signing certs that rely on SHA-512.

  3. Did anything else changed recently???
    I followed the workaround 2 weeks ago and it was working perfectly (Thanks for guidance!! ), but one of the IT guys ran the Microsoft window security patches over the last weekend and now its failing. Do you know if it has anything to do?

    These are the ones that I suspect:
    Security Update for Windows (KB4095515)
    Security Update for Windows (KB4096236)

    these also were installed but I dont think it has anything to do
    Security Update for Windows (KB4103715)
    Security Update for Windows (KB4103768)

    Thanks in advance,

    1. Hello, Ricardo. I finally had a chance to test this on Windows Server 2012 R2 with all 4 of those patches applied, and it works just fine. I ran the script that is posted to PasteBin (linked above in the post) and had no problems. I tested on SQL Server 2017 CU 6, and will update to CU 7 soon and try again. What CU level are you at? What OS are you using? If you are still having the problem, you could always try dropping the Login, the Certificate (from both DBs), and try creating the Certificate again, re-signing the Assembly, creating a new Login, etc. Please let me know if you are still having this issue, if you have tried recreating/resigning, and what CU level and OS you are using. Or, you might need to get the full list of patches applied just before this started failing, and I will see if I have all of those applied.

Leave a Reply to Solomon RutzkyCancel reply