Recap

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

   1. EXTERNAL_ACCESS or UNSAFE:

      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=0.0.0.0, 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!

Demo

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(
            DateToFormat.Value.ToString(FormatString.Value));
    }
}

Conclusion

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:

• sys.trusted_assemblies
• sys.sp_add_trusted_assembly
• sys.sp_drop_trusted_assembly
• CREATE CERTIFICATE
• CERT_ID
• sys.certificates
• ADD SIGNATURE