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

Question

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

Anonymous

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

3 answers

Your answer

Answer 1

LiHongMSFT-4306 31,566

Hi @Anonymous

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".

Anonymous

2025-01-10T17:15:07.88+00:00

Hello LiHong

This query returning nothing like below
Avila, John 0 Reputation points

2025-02-24T18:23:40.1033333+00:00

This should not be marked as the accepted answer.

The ISSERVER CLR resides in the SSISDB database and MS suggests adding the ASYMMETRIC key to the master database. Also, you cannot base the JOIN on the principal_id because those are different between the master and SSISDB.

Answer 2

Erland Sommarskog 121.8K MVP Volunteer Moderator

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.

Anonymous

2025-01-10T00:03:47.63+00:00

Hello Erland,

Thanks for response,

Main ask is that from customer

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

2025-01-10T00:05:21.2266667+00:00

Hello @Erland Sommarskog

Thank you for responding,

Main ask is from customer that do we any query or a way that can determine if a user-defined CLR assembly is signed by an asymmetric key?
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2025-01-10T21:46:42.9766667+00:00
I'm not sure what Cosmog had in mind. He is joining the assembly to the asymmetric keys on the owner. Does not make much sense. I don't think it was correct to accept his post as answer.

Try this:

SELECT a.name AS assembly, ak.name as keyname FROM sys.assemblies a JOIN master.sys.asymmetric_keys ak ON ak.thumbprint = convert(binary(8), assemblyproperty(a.name, 'publickey'))
Anonymous

2025-02-28T16:10:47.13+00:00

Hello @Erland Sommarskog

Customer is asking to accept his answer that he is using bleow command and asking to accept it. What can we answer?

SELECT a.name AS assembly, ak.name as keyname FROM SSISDB.sys.assemblies a JOIN master.sys.asymmetric_keys ak ON ak.thumbprint = convert(binary(8), assemblyproperty(a.name, 'publickey'))

Cx statement :

Actually, I am wrong, because the assembly being shown is the "Microsoft.SqlServer.Types" CLR which has user_defined=0. It is still not showing the ISSERVER CLR as the assembly.
Avila, John 0 Reputation points

2025-02-28T16:17:11.72+00:00

The ISSERVER CLR does not have a public key. Therefore when using the assemblyproperty(a.name, 'public') function, it returns NULL.
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2025-02-28T21:41:57.0933333+00:00

Hello @Erland Sommarskog Customer is asking to accept his answer that he is using bleow command and asking to accept it. What can we answer?

Is John Avila your customer? No, you don't have to answer, none of my business. But if your customer is happy with some answer, please accept it. The answer from Li Hong is incorrect, and should have not been accepted.
Avila, John 0 Reputation points

2025-02-28T22:00:58.4266667+00:00

@Erland Sommarskog the query your provided below is not returning the ISSERVER CLR because the assemblyproperty(a.name, 'public') function is returning NULL.

SELECT a.name AS assembly, ak.name as keyname

FROM sys.assemblies a

JOIN master.sys.asymmetric_keys ak

ON ak.thumbprint = convert(binary(8), assemblyproperty(a.name, 'publickey'))
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2025-02-28T22:05:00.2533333+00:00

Yeah, I notice that you have said this. I don't use SSIS myself, so I don't have an SSISDB to verify, but I don't disbelieve you.

Answer 3

Avila, John 0

I have found that this is the correct query:

SELECT a.name AS assembly, ak.name as keyname
FROM SSISDB.sys.assemblies a
JOIN master.sys.asymmetric_keys ak
ON ak.thumbprint = convert(binary(8), assemblyproperty(a.name, 'publickey'))

Avila, John 0 Reputation points

2025-02-24T18:52:45.1766667+00:00

Actually, I am wrong, because the assembly being shown is the "Microsoft.SqlServer.Types" CLR which has user_defined=0. It is still not showing the ISSERVER CLR as the assembly.

Share via

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

3 answers

Your answer