How to fix Error 4504 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 4504 – CLR Type Does Not Exist in Target Database

Why This Error Happens

This error occurs when a table, function, or parameter references a CLR (Common Language Runtime) user-defined type that was never registered in the target database via CREATE TYPE or CREATE ASSEMBLY. It commonly happens after restoring or migrating a database to a new SQL Server 2025 instance without also deploying the associated CLR assembly and type bindings. It can also occur if the assembly was dropped or failed to register due to clr strict security restrictions introduced in later SQL Server versions.

Step-by-Step Resolution Guide

Step 1: Identify the Missing CLR Type and Source Assembly

  • Note the exact type name and database from the error message text.
  • Query the source database (where the object originally worked) using:
  SELECT name, assembly_class FROM sys.assembly_types;
  • Identify which .dll assembly backs this type.

Step 2: Extract and Register the Assembly in the Target Database

  • On the source server, export the assembly binary:
  SELECT * FROM sys.assembly_files;
  • On the target database, enable CLR integration if disabled:
  EXEC sp_configure 'clr enabled', 1;
  RECONFIGURE;
  • Register the assembly manually:
  CREATE ASSEMBLY MyAssembly
  FROM 'C:\SQLAssemblies\MyAssembly.dll'
  WITH PERMISSION_SET = SAFE;

Step 3: Recreate the CLR Type Binding

  • After the assembly is registered, bind the type explicitly:
  CREATE TYPE dbo.MyCLRType
  EXTERNAL NAME MyAssembly.[Namespace.ClassName];
  • Ensure the namespace and class name match exactly (case-sensitive) with the original assembly definition.

Step 4: Validate CLR Strict Security Settings

  • On SQL Server 2025 / Windows Server 2025, confirm clr strict security is set to 1 (default) and that the assembly is signed or added to the trusted assembly list:
  EXEC sp_add_trusted_assembly @hash = <assembly_hash>, @description = N'MyAssembly';
  • This prevents silent registration failures caused by unsigned assemblies.

Step 5: Rebuild Dependent Objects

  • Recompile or re-run the failing CREATE TABLE, CREATE FUNCTION, or CREATE PROCEDURE statement that originally triggered Error 4504.
  • Confirm no orphaned references remain using:
  SELECT * FROM sys.types WHERE is_assembly_type = 1;

Alternative or Verification Steps

  • Run SELECT * FROM sys.assemblies; on the target database to confirm the assembly now appears and its is_visible column equals 1.
  • If registration still fails, drop and fully reinstall the CLR assembly package from the original deployment script rather than manually recreating individual types, as partial installs often leave metadata inconsistencies.