SQL Server 2022 introduces several enhancements to improve the security and performance of CLR (Common Language Runtime) assemblies. While these updates, like CLR Strict Security, make SQL Server safer by default, they can also complicate workflows for developers accustomed to older versions. One key change is the requirement for assemblies to be signed with a certificate or an asymmetric key to ensure code integrity and security.

If you’re working with CLR assemblies in SQL Server 2022, you may need to adapt your process to comply with these requirements. You know you’ve ran into these issues if you’ve seen this error message:

Msg 10343, Level 14, State 1, Line 1

CREATE or ALTER ASSEMBLY for assembly ‘TestCLRAssembly2’ with the SAFE or EXTERNAL_ACCESS option failed because the ‘clr strict security’ option of sp_configure is set to 1.

This guide will walk you through the steps to create a signed assembly using a Public-Private Key Pair.

---

Why the Change?

In SQL Server, assemblies allow developers to extend functionality with .NET code. However, assemblies can pose security risks if improperly managed. SQL Server 2022’s stricter defaults ensure that only trusted, signed assemblies are allowed, reducing vulnerabilities from unauthorized or malicious code.

---

How to Work Around the Default Behavior

To meet these requirements and continue leveraging assemblies effectively, you’ll need to:

1. Create a Public-Private Key Pair
2. Compile and Sign the Assembly Using the Key Pair

Below are detailed instructions for each step:

---

Step 1: Create a Public-Private Key Pair

The first step is to create a key pair that will be used to sign your assembly. This process requires Visual Studio and the Developer Command Prompt for VS 2022.

1. Open Developer Command Prompt for VS 2022 as Administrator
   Ensure you have administrative privileges, as the operation requires elevated permissions.
2. Run the Key Pair Creation Command
   Use the following command to generate the key pair

sn -k D:\filepath\keyfilename.snk

Replace D:\filepath\keyfilename.snk with the desired file path and file name for your key pair. Once executed, this creates a .snk file containing the Public-Private Key Pair.

---

Step 2: Compile and Sign the Assembly Using the Public-Private Key Pair

After creating the key pair, you’ll need to compile your assembly and sign it. This is done in Visual Studio.

1. Load the Assembly Project in Visual Studio
   Open your assembly project in Visual Studio.
2. Access Project Properties
   In Solution Explorer, right-click your assembly project and select Properties.
3. Navigate to the SQLCLR Tab
   On the left-hand side, click on the SQLCLR tab.
4. Open the Signing Options
   At the bottom of the SQLCLR tab, click the Signing… button.
5. Enable Assembly Signing
   Check the box labeled Sign the assembly.
6. Select Your Key Pair
   Use the drop-down menu to select your key pair. If it doesn’t appear, click Browse…, navigate to the .snk file you created in Step 1, select it, and click OK.
7. Save and Build Your Project
   Save your changes, then right-click your project in Solution Explorer and select Build to compile and sign the assembly.

---

Final Thoughts

With SQL Server 2022’s stricter security settings, signing assemblies is essential to ensure compliance and maintain the functionality of your CLR-based solutions. By following the steps outlined above, you can adapt your workflows to these new requirements while continuing to leverage the power of SQL Server assemblies.