Add Trusted Assemblies to SQL Server 2022
Since SQL Server 2017, Microsoft introduced the clr strict security option. This option is enabled by default and requires that all assemblies loaded into SQL Server are signed by a certificate. This is a security feature that helps protect your database from malicious code.
When you migrate a database to SQL Server 2017 or later, you may need to load assemblies that are not signed by a certificate. In this case, you need to add the assemblies as trusted assemblies to SQL Server.
In this blog post, I will show you how to add trusted assemblies to SQL Server 2022.
What are Trusted Assemblies?
Trusted Assemblies are assemblies that are loaded into SQL Server without being signed by a certificate. This means that you can load assemblies into SQL Server without having to sign them with a certificate.
This is a great feature because it allows you to load assemblies into SQL Server without having to go through the process of signing them with a certificate.
Do keep in mind that you should only load assemblies into SQL Server that you trust. If you load an assembly into SQL Server that you do not trust, it could compromise the security of your database.
How to add Trusted Assemblies to SQL Server 2022
To add Trusted Assemblies to SQL Server 2022, you need to follow these steps:
- Open SQL Server Management Studio.
- Find the assembly that needs to be loaded into SQL Server in the
sys.assemblies. - Copy the
clr_name of the assembly. - Copy the SHA512 hash of the assembly from the
sys.assembly_files table. - Run the
sp_add_trusted_assembly stored procedure with the clr_name and the SHA512 hash of the assembly.
To make it easier for you, I have created a script that you can use to add Trusted Assemblies to SQL Server 2022. You can find the script below:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
| -- Steps to run this script:
-- 1. Change the name of the database to connect to
-- 1. Run step 2 to get the trusted assemblies and assemblies in general
-- 2. Copy the name of the CLR you want to add to step 2
-- 3. Select the from below the 'RETURN' to the end and run it
----------------------------------------------------------
-- Step 1
----------------------------------------------------------
USE [Change DB name to your DB]
GO
----------------------------------------------------------
-- Step 2
----------------------------------------------------------
-- Check the trusted CLRs
SELECT * FROM sys.trusted_assemblies;
SELECT * FROM sys.assemblies;
RETURN;
----------------------------------------------------------
-- Select from here to the end to add the trusted assembly
----------------------------------------------------------
-- Declare variables
DECLARE @name VARCHAR(50);
DECLARE @clr_name NVARCHAR(255);
DECLARE @sha512 VARBINARY(64);
----------------------------------------------------------
-- Step 3: Set the name
----------------------------------------------------------
SET @name = 'name of the clr you want to add'
----------------------------------------------------------
-- Get the name of the CLR
----------------------------------------------------------
SELECT @clr_name = clr_name
FROM sys.assemblies
WHERE [name] = @name
----------------------------------------------------------
-- Get the SHA512 value
----------------------------------------------------------
SELECT @sha512 = sha2_512
FROM sys.assembly_files
WHERE name = @name;
----------------------------------------------------------
-- Output the retrieved values
----------------------------------------------------------
SELECT @name as [Name], @clr_name AS [CLRName], @sha512 AS [SHA512];
----------------------------------------------------------
-- Add the hash if it doesn't exist yet
----------------------------------------------------------
IF EXISTS(SELECT [hash] FROM sys.trusted_assemblies WHERE [hash] = @sha512)
BEGIN
PRINT 'Assembly is already in trusted assemblies';
SELECT 'Assembly is already in trusted assemblies';
END
ELSE
BEGIN
EXEC sp_add_trusted_assembly
@hash = @sha512,
@description = @clr_name;
END
|
Remove Trusted Assemblies
If you want to remove a Trusted Assembly from SQL Server, you can use the sp_drop_trusted_assembly stored procedure. You can use the following script to remove a Trusted Assembly from SQL Server:
1
| EXEC sys.sp_drop_trusted_assembly @hash = 0x74FFDB3599860419859C11FE967B225E884...;
|
Replace the @hash parameter with the SHA512 hash of the assembly you want to remove.
Recap
The script makes it a little easier to add Trusted Assemblies to SQL Server.
If you have any questions or comments, feel free to reach pit. I’m happy to help you out.