SQLCLR vs. SQL Server 2017, Part 5: “Trusted Assemblies” – Valid Use Cases?

Posted on by Solomon Rutzky

In the previous post in this series on SQLCLR in SQL Server 2017 — Part 4: “Trusted Assemblies” — The Disappointment — we looked at what the “Trusted Assemblies” feature is, what it meant to do, the problems with it, and what the better and more appropriate approach is. “Trusted Assemblies” is a simple mechanism for designating individual Assemblies, identified by the SHA-512 hash of the entire Assembly, as having permission to operate. The main idea behind this feature is that since all Assemblies need to meet the requirements of loading an UNSAFE Assembly, and that it will be difficult at best, if not impossible (on a practical level) for most users to export, sign, and re-import their SAFE Assemblies, that registering them as “trusted” is better than compromising the security of the system by enabling TRUSTWORTHY for the Database. There are several problems with “Trusted Assemblies”, such as:

  • hashes don’t contain any meta-data such as where they came from (i.e. identity / provider)
  • hashes can’t (and aren’t meant to) prove that one object is the exact same thing as another (i.e. that the Assembly that the hash was generated from is the exact same Assembly that SQL Server is attempting to load), due to potential collisions
  • more maintenance over time as old hashes build up in the sys.trusted_assemblies system catalog view due to people forgetting to clean up obsolete hashes. Remember, the bytes of the Assembly will change upon each compile, so a new hash will need to be registered as “trusted” per each deployment.
  • the “trusted” hashes are registered at the Instance level, so if you deploy an Assembly to multiple Databases and later find that you need to remove / alter fewer than all of the Databases the Assembly was deployed to, then you can’t unregister the “trusted” hash without breaking the remaining Databases.
  • the “trusted” hashes require someone either being in the sysadmin fixed server role, or having the CONTROL SERVER permission, in order to administer.

Most folks aren’t thinking about the long-term consequences yet, but it won’t take long for people to find themselves in a messy situation, especially if they are in an environment using Continuous Integration / Continuous Deployment. Of course, all of those objections aside, “Trusted Assemblies” is still an entirely unnecessary feature given that Certificates (which have existed since SQL Server 2005, and could be created from a VARBINARY string as of SQL Server 2012), don’t have any of those drawbacks. Beyond that they:

  • already exist, so no need to add code / complexity to SQL Server only to end up with less than what was already there
  • provide a much greater level of security
  • actually can be applied to existing Assemblies
  • can be applied prior to upgrading to SQL Server 2017
  • can, individually, apply to any number of Assemblies, even across multiple Databases (meaning, you only really need one Certificate)

At this point the analysis has shown that “Trusted Assemblies” does far more harm than good. And, the only “good” provided isn’t a benefit unique to this feature, it is only a slight convenience (and even that is debatable). So is there any possible benefit to this new feature?

Make Software Delivery Easier?

It has been suggested that “Trusted Assemblies” will make delivering software containing SQLCLR Assemblies easier, especially in situations where the Login used for installation does not have sysadmin rights.

The idea is that “Trusted Assemblies” will allow a software vendor to provide a separate script to register the required hashes. Someone with sysadmin rights would execute that script. Then, a non-sysadmin can install the Assemblies whenever they are able to. During installation, the script can verify the existence of the hashes and abort cleanly if they are not present. If the Login used for installation has the VIEW SERVER STATE permission, then verification can be done directly by selecting from the sys.trusted_assemblies system catalog view. Otherwise the CREATE ASSEMBLY statement can be wrapped in a TRY…CATCH construct so that the error caused by attempting to load an Assembly with no matching trusted hash can be trapped and handled.

This certainly seems like a valid use of “Trusted Assemblies”. The only problem is that it has been possible to do this, and in a cleaner, simpler, and more secure way, starting with SQL Server 2012.

Certificates are at least just as easy (if not easier) than registering a Trusted Assembly:

BOTH

  • External piece (not provided by Microsoft) required to convert binary files (i.e. .cer, .dll) into string hex bytes form.

CERTIFICATES

  1. Need to use SignTool.exe to sign the DLL(s). This is provided by Microsoft.
  2. Creating the Certificate and Login requires the CREATE CERTIFICATE, CREATE LOGIN, and UNSAFE ASSEMBLY permissions, none of which is too highly privileged. These can, and should, be given to the same Login / person who is doing the main installation, if that Login doesn’t already have them (i.e. simple)
  3. Verifying the existence of the Certificate is a simple use of the CERT_ID built-in function. AND, permissions can be granted per each Certificate, so Logins only see what they are allowed to see (i.e. safe). Even better, a Login can automatically see Certificates that they create, so no additional permissions needed for verification (i.e. simple).
  4. Creating the Certificate and the install script for it is a one-time operation. A vendor can use the same Certificate on all of their Assemblies, and it doesn’t change when the Assembly is updated. This means a) one Certificate and one Login is all that ever get creatd, and b) no additional sysadmin work for software updates, and no growing pile of obsolete meta-data (i.e. simple and no extra maintenance).
  5. Logins can only drop Certificates that either they created or were granted permission to (i.e. safe).

TRUSTED ASSEMBLIES

  1. Might need additional external piece to calculate hash, or make SQL Server do it via HASHBYTES
  2. Registering the hash requires the Login to be in the sysadmin fixed server role, or have the CONTROL SERVER permission, hence this requires a different,
    more-privileged Login / person (i.e. not simple in order to be safe).
  3. Verification requires the VIEW SERVER STATE permission to query sys.trusted_assemblies. And then, they can see all Trusted hashes, no fine-grained access (i.e. not safe).
  4. Calculating and registering the hashes to trust needs to be done for every compilation of the DLL(s). This means that software updates require a sysadmin every time, and might be adding to the trusted hash list every time if not being cleaned up (i.e. not simple and extra maintenance).
  5. Unregistering an obsolete hash needs to be done by someone with sysadmin rights, who can then unregister any number of (or all) trusted hashes. This is why it needs to be done by a different Login / person (i.e. not simple in order to be safe).

Hence, if anyone wanted to use a non-sysadmin account for installation, Certificates make that rather easy. You just grant the Login / User the following three permissions:

USE [master];
GRANT CREATE CERTIFICATE TO [installation_login] AS [dbo];
GRANT ALTER ANY LOGIN TO [installation_login] AS [sa];
GRANT UNSAFE ASSEMBLY TO [installation_login] WITH GRANT OPTION AS [sa];

Also, SHA-512 is just a 64-byte hash, which is useful in determining if there is a difference between objects / values, but is not security per-se. It is only 64 bytes and contains no meta-data to indicate origin, etc. It could, theoretically (even if highly unlikely) be “faked” due to hashes allowing for collisions – multiple values calculating the same hash value. If someone had knowledge of at least one “trusted” hash, then it is at least possible to manipulate the DLL by adding bytes to the end of it until it computes to the desired hash.

On the other hand, a certificate is (approximately) 700 – 900 bytes and does contain additional information to help determine authenticity. Also, the certificate value is calculated from both the item being signed and the private key. So no, this is not going to be “faked”.

Once again, “Trusted Assemblies” have no advantages over Certificates, which are as simple and secure as it gets. Certificates don’t require sysadmin privileges, which gives the flexibility of either having a single deployment script to do everything, or, if you wan’t you can have a separate script create the Certificate and Login. “Trusted Assemblies”, however, requires a higher level of privilege to configure a less secure and clunkier system (i.e. a lose-lose situation).

AND, a deployment script using “Trusted Assemblies” will only work with SQL Server 2017, whereas a deployment script using a Certificate will work with SQL Server 2012, 2014, 2016, and 2017 (assuming no other 2017-specific functionality is being used, of course).

Allow SQLCLR back in Azure SQL Database?

Back in late 2014 it was announced that Azure SQL Database V12 would finally include support for SQLCLR. Even though it was only SAFE Assemblies that were allowed, this was still a huge improvement. But then, rather abruptly in mid-April of 2016, SQLCLR support was removed from Azure SQL Database V12. While it was never stated why (outside of it being a “security concern”), it was likely due to the same reason that prompted the new “clr strict security” server-level configuration option. So, it is possible that “Trusted Assemblies” is seen as the easiest means of getting SQLCLR back into Azure SQL Database.

Considering that “Trusted Assemblies” is entirely worthless within the context of the regular (i.e. non-Azure) SQL Server, does the same argument of “just use the existing Certificates and Module Signing functionality” also apply here?

Well, this just happens to be the one area where there might possibly be, in the worst-case scenario, some argument made for keeping this feature. The problem with applying the same Certificate / ADD SIGNATURE logic to Azure SQL Database is that you can’t create a signature-based Login (from either a Certificate or an Asymmetric Key) in that environment. Ouch! That is definitely a nail-in-the-coffin for the Certificate idea. Ok, so assuming that “Trusted Assemblies” would work in this scenario, is it an acceptable solution to the problem?

The answer is still pretty much “absolutely not”. As detailed in the previous post (i.e. Part 4) and again at the top of this post, there are numerous problems with using a hash for this, even if it appears to work (remember, code riddled with GOTO statements can work for a long time, but that doesn’t mean it’s a good idea). Below are a few other options that should be considered before proceeding to use an inferior simple hash. And while each of these options requires some amount of work, that was also the case for implementing “Trusted Assemblies”. The difference with these other options is that the time spent implementing them wouldn’t be time wasted injecting technical debt into an otherwise great RDBMS.

  1. Allow Certificate-based Logins:
    This is the ideal solution as it is fully consistent with the behavior of non-Azure SQL Server. This requires no additional documentation or training, and simplifies deployment scripts (by not complicating them). If this happened, then Azure SQL Database would be the same (at least from a SQLCLR perspective) as SQL Server on Linux.

  2. Scan for matching Certificate in [master], without requiring the Login:
    The documentation states that Certificates can be created in Azure SQL Database from a VARBINARY literal. Great. Now, assuming that Certificates can be created in [master], then check to see if any of those Certificates matches the Certificate of any Assembly being loaded. SQL Server already does this, except that it scans the Logins instead of Certificates. But the Logins aren’t truly needed, at least not here. Having the Logins allows for distinguishing between granting the full UNSAFE ASSEMBLY permission, or granting the more limited EXTERNAL ACCESS ASSEMBLY. But now there is only one valid permission — UNSAFE ASSEMBLY — so simply confirming the existence of a matching Certificate is logically equivalent; the Login is mere formality at this point, at least in this particular case.

  3. Finally, if there is actual reasonable justification for not implementing one of the above suggestions, then, as a last resort only, confine “Trusted Assemblies” to be an Azure SQL Database-only feature.
    But there is no valid reason for hurting the main SQL Server product with this proven anti-pattern.

Conclusion

Looking at the various scenarios where “Trusted Assemblies” could potentially be a worthwhile solution, we can see that it provides no benefit beyond what can already be accomplished via Certificates. “Trusted Assemblies” merely provides a means of getting SQLCLR Assemblies loaded that is potentially slightly easier than Certificates, but that is also definitely less secure. Its existence in SQL Server is a huge disservice to the product and to the SQL Server community. So, please support (i.e. upvote) my Microsoft Bug Report stating that this feature needs to be removed as absolutely nothing is gained by having it, and its existence only detracts from SQL Server’s security and overall quality:

Trusted Assemblies are more problematic yet less functional than Certificates – Please Remove

See also:

1 thought on “SQLCLR vs. SQL Server 2017, Part 5: “Trusted Assemblies” – Valid Use Cases?”

Leave a Reply