Hi Ronald. Great job sticking with not enabling
TRUSTWORTHY, and figuring out how to accomplish that!
Just one error I noticed in the article that should probably be updated to be accurate, if possible. You stated:
The assembly is physically located inside the C:\Windows\Assembly folder ...
That is not how SQLCLR works. All assemblies that you call methods from within SQL Server are entirely contained within SQL Server (hence they are backed up when you backup a database containing any assemblies, unlike the deprecated extended stored procedures that were external DLLs). The files within C:\Windows\Assembly and its subfolders should be run-time copies.
The assembly containing the CLR code that is executed within SQL Server can be found via:
WHERE [assembly_id] = 65536;
[content] field, being
VARBINARY(MAX), contains an exact copy of the DLL it was loaded from. That value is the assembly that gets loaded into memory.
The original filesystem location of the DLL can be seen in those backup/restore instructions that you linked to, in the step regarding the creation of the Asymmetric Key:
CREATE Asymmetric Key [MS_SQLEnableSystemAssemblyLoadingKey]
FROM Executable File =
'C:\Program Files\Microsoft SQL Server\110\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll'
Of course, if you ever find yourself in a situation with an Assembly and don't have the public key it was signed with, you can do the following:
- TEMPORARILY enable
TRUSTWORTHY for the
ALTER DATABASE [master] SET TRUSTWORTHY ON;
- Create the Assembly in
- get assembly bits from the
[content] field in
CREATE ASSEMBLY [temp] FROM assembly_bits;
- Create Asymmetric Key from the Assembly
- Create a Login from the Asymmetric Key
- Grant the Login the
EXTERNAL ACCESS ASSEMBLY permission xor the
UNSAFE ASSEMBLY permission (whichever one you need)
- Drop the Assembly:
DROP ASSEMBLY [temp];
ALTER DATABASE [master] SET TRUSTWORTHY OFF;