Is it possible to find out the original path that was used to load the assembly in SQL SERVER CLR?

I am very new to working with Assemblies CLR in SQL Server

I have a database that has many of them

Is it possible to find out the original path that was used to load the assembly ?

Considering this is the way the assemblies are created I need that “from” path

CREATE ASSEMBLY ClassLibrary1 
from 'D:DotNetTeamSQLServerClassLibrary1.dll' 

I’m asking this because this assembly : a798b6eb4255719355458f3749073dc1b is not found on the server list and is being reported as unsecure (I know I have to sign it, but for that I need to first find it)

I found this other question but is not what I need. How to find the assembly registered in SQL Server?

UPDATE

Querying the sys.assembly_files table, I notice that the record exists there but not on the left side (Object Explorer: Assemblies), do I need to re-created the assembly ?

select * 
from sys.assembly_files
where name like 'a79%'

The solution for this question might also help you solve any of these
errors:

System.IO.FileLoadException: Could not load file or assembly ‘ASSEMBLY_NAME, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

System.IO.FileLoadException: Could not load file or assembly ‘ASSEMBLY_NAME, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. Exception from HRESULT: 0x80FC80F1

Answer

Yes, but only if the assembly was loaded from a DLL on the file system and not by supplying the VARBINARY literal / hex bytes. When loaded from an external DLL (a method that I strongly recommend against, btw), the original path is stored in the [name] column in sys.assembly_files.

Execute the following to show any potential paths:

-- Change this to the assembly name or path to apply filter:
DECLARE @AssemblyName NVARCHAR(260) = '';
    
-- The content can be used to export the DLL:
SELECT asm.[name] AS [Assembly],
       afl.[name] AS [PathOrAltName],
       asm.[permission_set_desc],
       afl.[file_id],
       afl.[content]
FROM   sys.assembly_files afl
INNER JOIN sys.assemblies asm
        ON asm.[assembly_id] = afl.[assembly_id]
WHERE asm.[name] LIKE N'%' + @AssemblyName + N'%'
OR    afl.[name] LIKE N'%' + @AssemblyName + N'%'
ORDER BY asm.[name], afl.[file_id];

If the assembly was loaded from a VARBINARY literal / hex bytes (i.e. 0x4D5A9000...), then the [name] column in sys.assembly_files should be the same as the [name] column in sys.assemblies (which is the name used in the CREATE ASSEMBLY statement).

---

Querying the sys.assembly_files table, I notice that the record exists there but not on the left side

To be clear, both screen shots in the question clearly show a long list of very poorly (and clearly programmatically) named assemblies. And it is not possible, in any way, for an entry to show up in sys.assembly_files (or any of the other assembly-related management views) without it existing in sys.assemblies.

ALSO: are you certain that you are in the same database in the query window that you are drilling-down into in Object Explorer?

they only provided me a database backup

Assemblies physically exist in each DB where CREATE ASSEMBLY was executed and show up in sys.assemblies, sys.asssembly_files, and a few other system catalog views. This is one of many benefits of SQLCLR: the assembly is not external to the DB as is the case with the now deprecated external stored procedure API / feature (i.e. XPs).

I know I have to sign it…

So then I assume you are restoring a pre-SQL Server 2017 DB into SQL Server 2017 or newer. In this case you do need to sign all unsigned assemblies, BUT you do not need to export them in order to do this (an extremely common misunderstanding due to Microsoft misunderstanding the issue themselves and hence providing incorrect documentation on this topic). This is rather simple to solve given that you can sign the assembly in-place in order to get past the new “CLR strict security” debacle:

SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment (Msg 10314)

Here is a summary of the steps:

1. Create Certificate (using a password) in the DB containing the assembly / assemblies using CREATE CERTIFICATE ...
2. Sign the Assembly using ADD SIGNATURE TO Assembly::[{assembly_name}] ...
3. Copy the Certificate to [master] (but not the Private Key!)
4. Create a Login from the Certificate
5. Grant the signature-based Login the UNSAFE ASSEMBLY permission
6. You will still need to make sure that each assembly has the correct PERMISSION_SET for what it needs to do

Here is the demo script from that article that you can copy and adjust to fit your needs:

Avoiding “Trusted Assemblies” – Demo (on PasteBin)

---

For more information on working with SQLCLR in general, please visit: SQLCLR Info

UPDATE (from O.P.):

The issue was that the name of the assembly was ComputeHashFuncAssmembly but the path was the one that contained a798b6eb4255719355458f3749073dc1b so an ALTER ASSEMBLY was needed. That was also the reason it was not visible on the left side because it was under ComputeHashFuncAssmembly and not as the hex code.

I exported the assembly because the original DLL was not provided to me and I wanted to have it.

These are the steps for signing the assembly

USE [yourDatabase];
GO

CREATE CERTIFICATE [AssemblyCertificate]
ENCRYPTION BY PASSWORD = 'aPasswordYouChoose'
WITH SUBJECT = 'Assembly Certificate';

ADD SIGNATURE TO Assembly::[ComputeHashFuncAssmembly]
    BY CERTIFICATE [AssemblyCertificate]
    WITH PASSWORD = 'aPasswordYouChoose';

ALTER ASSEMBLY ComputeHashFuncAssmembly
    WITH PERMISSION_SET = SAFE;

-- Copy the certificate to the master
BACKUP CERTIFICATE [AssemblyCertificate] TO FILE = 'C:tempcertificate.cer';

USE [master];
GO

-- DROP certificate [AssemblyCertificate];
CREATE CERTIFICATE [AssemblyCertificate]
FROM FILE ='C:tempcertificate.cer';

CREATE LOGIN [login_AssemblyCertificate]
FROM CERTIFICATE [AssemblyCertificate];

GRANT UNSAFE ASSEMBLY TO [login_AssemblyCertificate];