Trusted Assemblies are more problematic yet less functional than Certificates - Please Remove
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:
and (#8 gets its own post):
Additional reasoning posted to:
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:
I tested on Linux (Ubuntu 16.04), though that shouldn't make a difference.
* 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.
this "feature" has crippled SQLCLR in AWS and even AZURE -forever. clr cannot be used any more because sa is not allowed on managed instances :(
I completely endorse mb's comment. I'm the only IT person in a 5-person company. The trusted assembly option fits my limited use case, the certificates approach most emphatically does not. And thanks to mb for the script.
I disagree strongly with this post and think that trusted assemblies is a very useful feature, especially for migrating existing databases containing CLR assemblies. Most users who’ve created their own CLR procedures, for example to access C# regex or cryptographic functions, will likely not have signed these and would not want to go through the effort of doing so, especially during a database migration which is quite a busy time in itself. Trusted assemblies make this very straightforward.
To answer the numbered points above:
1+2: I’m no expert in cryptography, but my understanding is that, as SHA2-512 is a cryptographic hash function, it is essentially impossible to design a binary that hashes to the same value using currently available computing power. Furthermore, assemblies are stored in SQL Server, not on disk, so it’s not as straightforward as tampering directly with a dll.
3: I don’t think this is a major issue but in any case it would be fairly straightforward to write a script to remove any unused hashes from the sys.trusted_assemblies catalogue. This would just entail enumerating the hashes of all user assemblies in each database, then removing any entries from sys.trused_assemblies that were not in this collection.
4: Only remove the trusted assembly once it’s no longer used by any database.
5: This is unlikely to be an issue for most people. There is nothing preventing anyone from using certificates if so!
6: Using the script below it’s very easy to trust the assemblies on an attached/restored database without disabling any security features.
7: Certificates may be more appropriate for future development, but it’s straightforward to obtain the hash of an assembly using T-SQL.
8: I don’t think this will concern many people.
If we are creating our own assemblies I can’t envisage any significant additional risk from this functionality. Unless certificates are signed by a recognised authority, which would be preferable for obtaining third party dlls, I can’t see any great advantage that they confer. But, as I say, I’m not an expert in cryptography. Trusted assemblies do perhaps add an additional attack vector but, as always, it’s necessary to balance security against pragmatism.
In case it helps anyone, I have created a stored procedure that marks all existing assemblies in a database as trustworthy. To use it, add the proc below to the master database, not forgetting to run sp_MS_marksystemobject, which allows the procedure to run in the context of the current database. Then change the context to your user database and run the procedure.
CREATE PROCEDURE dbo.sp_TrustExistingAssemblies
DECLARE @hash binary(64)
DECLARE @clr_name nvarchar(4000)
DECLARE c CURSOR FOR
SELECT hash = HASHBYTES('SHA2_512', f.content), a.clr_name
FROM sys.assemblies a
JOIN sys.assembly_files f ON a.assembly_id = f.assembly_id
WHERE a.is_user_defined = 1
FETCH NEXT FROM c INTO @hash, @clr_name
WHILE @@FETCH_STATUS = 0
IF NOT EXISTS(
FROM sys.trusted_assemblies a
WHERE a.hash = @hash
EXEC sp_add_trusted_assembly @hash, @clr_name
FETCH NEXT FROM c INTO @hash, @clr_name
EXEC sp_MS_marksystemobject sp_TrustExistingAssemblies
To get the hash of a dll file in you can use:
DECLARE @blob varbinary(MAX) =(SELECT BulkColumn FROM OPENROWSET(BULK N'path_of_dll.dll', SINGLE_BLOB) AS x)
And for the hash of an assembly from T-SQL script, just grab the binary from
CREATE ASSEMBLY [SomeAssembly] AUTHORIZATION [dbo] FROM 0x(SomeBinary)
and use it in the HASHBYTES function:
In either case you must run sp_add_trused_assembly before actually creating the assembly.
Solomon Rutzky commented