Trusted Assemblies are more problematic yet less functional than Certificates - Please Remove - by Solomon Rutzky

Status : 

 


9
2
Sign in
to vote
ID 3140202 Comments
Status Active Workarounds
Type Bug Repros 0
Opened 9/1/2017 12:17:00 AM
Access Restriction Public

Description

The new "Trusted Assemblies" feature, introduced in SQL Server 2017 RC1, is entirely unnecessary, and pushes people farther away from good security practices (i.e. using a signature-based Login).

The main problem it is attempting to solve -- allowing SQLCLR code in unsigned, SAFE Assemblies to work with "clr strict security" enabled and TRUSTWORTHY disabled -- was solvable back when SQLCLR was first introduced in SQL Server 2005, but in a more elegant, more functional, and more secure way.

All that needs to be done is to sign the Assembly using ADD SIGNATURE (well, you still need the signature-based Login having the UNSAFE ASSEMBLY permission).

The problems that I see with "Trusted Assemblies", in summary, are as follows:

1) Hashes, even SHA-512, aren't security; this is just a type of Symmetric Key that is exposed and unencrypted / unprotected
2) Hashes are not guaranteed to uniquely identify an Assembly
3) Obsolete hashes will accumulate in the system over time (when used for new development)
4) sys.trusted_assemblies is server level, which may conflict when an Assembly starts out in multiple DBs but is removed from only some (either causing error or making it harder to clean up)
5) Manipulating and viewing the trusted hashes requires a higher level of privileges than Certificates
6) These can't be used pre-upgrade to make "clr strict security" a non-issue for upgrades (at least not as easily as Certificates)
7) The hashes are per-each Assembly and change per each compilation. Certificates can be used for any number of Assemblies, even across DBs, and won't change when Assemblies change
8) Trusted Assemblies don't allow for module signing (of the T-SQL wrapper objects).

A detailed explanation of each of the problems noted above, plus the various benefits of using the existing module signing functionality, has been posted to my blog at:

https://SqlQuantumLeap.com/2017/08/28/sqlclr-vs-sql-server-2017-part-4-trusted-assemblies-the-disappointment/

and (#8 gets its own post):

https://SqlQuantumLeap.com/2017/09/29/sqlclr-vs-sql-server-2017-part-6-trusted-assemblies-cant-do-module-signing/


Additional reasoning posted to:

https://SqlQuantumLeap.com/2017/09/04/sqlclr-vs-sql-server-2017-part-5-trusted-assemblies-valid-use-cases/

I also have a working demo showing the technique of signing an existing SAFE Assembly (without altering it or dropping and recreating it) posted to PasteBin at:

https://pastebin.com/mwi5BidL

I tested on Linux (Ubuntu 16.04), though that shouldn't make a difference.

Please remove:
* sys.trusted_assemblies
* sys.sp_add_trusted_assembly
* sys.sp_drop_trusted_assembly
* related documentation

Please, PLEASE fully remove this ASAP as "Trusted Assemblies" harms both the SQL Server product as well as the community of those of us who work with SQL Server. And, I do apologize if all of this is coming across as harsh or insulting, but I truly am deeply concerned about the long-term, negative impact on security practices. If the goal of "Trusted Assemblies" is to allow for stricter security, then I think the net effect of this will be quite the opposite. When it comes to handling existing, unsigned, SAFE Assemblies, "Trusted Assemblies" is only slightly less work than setting up the signature-based Login (as my demo script shows), yet they are far less secure. And since they are server-wide, the overall risk is about the same as simply enabling TRUSTWORTHY in any DB with unsigned, SAFE Assemblies: manipulating an unrelated Assembly to compute the same hash as one that has been trusted is certainly a challenge compared to the no extra work needed if a DB has TRUSTWORTHY enabled, but if someone does manage to manipulate an Assembly in this way, then the entire server is exposed whereas in the TRUSTWORTHY ON scenario, only that one DB (or a few, perhaps) is exposed. And, the probability of being able to do such a manipulation increases as the number of unique Assemblies in the system increases, and increases even further when people make changes to the Assemblies and forget to remove the obsolete hashes (which is inevitable).

If "Trusted Assemblies" were the only way to make "clr strict security" manageable, then I would say: "Sure, fine. It's not ideal, but it'll work". But, Certificates handle all of the scenarios that one might need / want "Trusted Assemblies" for. And, Certificates really aren't difficult to use, regardless of common perceptions. Just look at my demo script linked above. It shows exactly how easy they are to implement. And Certificates are a proven, solid, standard security mechanism.

Please know that I love SQL Server and SQLCLR. And I have great respect for the team -- past and present members -- who works on it (thanks again for porting it to Linux :-). And I know from experience that it is neither fun, nor easy, reverting entire features. But, in this particular case, I firmly believe that it's the right thing to do.
Sign in to post a comment.