How to Verify the Signed Status of a CLR Assembly with Asymmetric Key in SQL Server

Question

CLR Strict Security is enabled - User databases have TRUSTWORTHY as OFF 

In this scenario all CLRs are treated as unsafe and will not function, unless they are added to the trusted_assemblies or have an asymmetric key. 

They have seen many issues with adding the SSISDB ISSERVER CLR to trusted_assemblies because there are issues when this CLR is updated by a CU. This is why we are using an asymmetric key to sign the ISSERVER CLR. 

If they were able to consistently use the trusted_assemblies for this CLR they would have no issues and not need a custom query from Microsoft. 

Due to this deficiency, They are being forced to use an asymmetric key. 

SQL Server itself knows whether to execute a CLR, because it is signed or trusted. Knowing this, there must be a way tell if a CLR is trusted or signed

Answer 1

Hi @Vanitha Dodla (MINDTREE LIMITED)

do we have any query or that can determine if a user-defined CLR assembly is signed by an asymmetric key?

Try this:

SELECT A.name AS Assembly_Name,AK.name AS Asymmetric_Key_Name 
FROM sys.assemblies A JOIN sys.asymmetric_keys AK ON A.principal_id = AK.principal_id
WHERE A.is_user_defined = 1

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

I am not sure that I understand the question - or if there is a question, or it just a rant.

But if you want to know if a certain assembly is trusted, you can try:

SELECT a.name 
FROM   sys.assemblies a
JOIN   sys.assembly_files af ON a.assembly_id = af.assembly_id
WHERE  EXISTS (SELECT * 
               FROM   sys.trusted_assemblies ta
               WHERE  ta.hash = hashbytes('SHA_512', af.content))

Although, I don't really have any test material here and now. And I'm a little uncertain what applies if there is more than one file assembly.