Addressing CLR assembly deprecation in Amazon RDS for SQL Server

Microsoft SQL Server 2016 reaches its end of extended support on July 14, 2026. If you run SQL Server 2016 on Amazon Relational Database Service (Amazon RDS) for SQL Server, you need to plan your upgrade path to a newer version. If you have deployed SQL Common Language Runtime (CLR) assemblies in your databases, that upgrade presents a significant challenge. User-defined CLR assemblies are no longer supported on Amazon RDS starting with SQL Server 2017. In this post, we show you how to identify your CLR dependencies. We also compare the replacement strategies so that you can upgrade without disrupting your production workloads.

Important considerations about upgrading from SQL Server 2016 to a newer version on Amazon RDS for SQL Server

Review the following considerations before you start your upgrade.

User-defined CLR assemblies are no longer supported

Amazon RDS for SQL Server 2016 supports CLR integration with PERMISSION_SET=SAFE. However, starting with SQL Server 2017 and newer versions, Amazon RDS no longer supports SQL CLR with PERMISSION_SET=SAFE. This is because Microsoft introduced CLR strict security, a security enhancement that is enabled by default in SQL Server 2017 and later. CLR strict security treats all assemblies, including those marked as SAFE, as if they were UNSAFE, requiring sysadmin permissions to configure workarounds such as disabling strict security or using sp_add_trusted_assembly. Because sysadmin permissions are not available to you in the managed Amazon RDS environment, CLR integration cannot be supported on SQL Server 2017 and later versions.

If you’re running SQL Server 2016 on Amazon RDS and have deployed CLR assemblies with SAFE permission set, you must take action before you upgrade to a newer version. Either stop using CLR integration, or choose one of the replacement strategies for SQL CLR in this post.

Step 1: Identify CLR dependencies

1. Connect to your Amazon RDS for SQL Server instance as the master user.
2. Open your preferred SQL query editor.
3. Copy the following assessment script into the query window and run it. The script identifies CLR assemblies, assemblies currently loaded in memory, and dependent objects. It returns two sections:

• Section 1: User-defined assemblies across your databases, and the assemblies currently loaded in memory, which indicates active usage.
• Section 2: CLR-based objects, such as functions, procedures, and types.

-- =====================================================
-- Comprehensive CLR Assembly Assessment Script
-- Purpose: Identify CLR assemblies, loaded instances, and dependent objects
-- Author: RDS SQL Server Team
-- =====================================================
SET NOCOUNT ON;

PRINT '========================================';
PRINT 'CLR ASSEMBLY ASSESSMENT REPORT';
PRINT 'Generated: ' + CONVERT(VARCHAR(23), GETDATE(), 121);
PRINT '========================================';
PRINT '';

-- =====================================================
-- ASSEMBLIES & CHECK TO SEE IF CLR LOADED OR NOT
-- =====================================================
PRINT '========================================';
PRINT 'ASSEMBLIES & LOADED INSTANCES';
PRINT '========================================';
PRINT '';

DECLARE @command VARCHAR(4000);

DECLARE @clr_scan TABLE
(
    [database_name] [sysname] NOT NULL,
    [name] [sysname] NOT NULL,
    [assembly_id] [int] NOT NULL,
    [clr_name] [nvarchar](4000) NULL,
    [permission_set_desc] [nvarchar](60) NULL,
    [create_date] [datetime] NOT NULL,
    [modify_date] [datetime] NOT NULL,
    [is_user_defined] [bit] NULL
);

SELECT @command = '
IF DB_ID(''?'') > 5
    SELECT ''?'' AS database_name,
        name,
        assembly_id,
        clr_name,
        permission_set_desc,
        create_date,
        modify_date,
        is_user_defined
    FROM [?].sys.assemblies
    WHERE is_user_defined = 1';

INSERT INTO @clr_scan EXEC sp_MSforeachdb @command;

-- Create temp table for loaded assemblies
DECLARE @loaded_assemblies TABLE
(
    [database_name] [sysname] NOT NULL,
    [assembly_id] [int] NOT NULL,
    [appdomain_name] [nvarchar](256) NULL,
    [load_time] [datetime] NULL,
    [creation_time] [datetime] NULL,
    [total_processor_time_ms] [bigint] NULL,
    [total_allocated_memory_kb] [bigint] NULL
);

INSERT INTO @loaded_assemblies
SELECT
    DB_NAME(d.db_id) AS database_name,
    a.assembly_id,
    d.appdomain_name,
    a.load_time,
    d.creation_time,
    d.total_processor_time_ms,
    d.total_allocated_memory_kb
FROM sys.dm_clr_loaded_assemblies a
INNER JOIN sys.dm_clr_appdomains d ON a.appdomain_address = d.appdomain_address
WHERE d.db_id > 5;

-- SINGLE COMBINED RESULT SET
SELECT
    cs.database_name AS [Database],
    cs.name AS [Assembly Name],
    cs.assembly_id AS [Assembly ID],
    cs.clr_name AS [CLR Name],
    cs.permission_set_desc AS [Permission Set],
    cs.create_date AS [Created],
    cs.modify_date AS [Modified],
    la.appdomain_name AS [AppDomain],
    la.load_time AS [Load Time],
    la.creation_time AS [Creation Time],
    la.total_processor_time_ms AS [CPU Time (ms)],
    la.total_allocated_memory_kb AS [Memory (KB)],
    CASE WHEN la.assembly_id IS NOT NULL THEN 'Yes' ELSE 'No' END AS [Currently Loaded]
FROM @clr_scan cs
LEFT JOIN @loaded_assemblies la
    ON cs.database_name = la.database_name
    AND cs.assembly_id = la.assembly_id
ORDER BY cs.database_name, cs.name;

DECLARE @assembly_count INT, @loaded_count INT;
SELECT @assembly_count = COUNT(*) FROM @clr_scan;
SELECT @loaded_count = COUNT(*)
FROM @clr_scan cs
INNER JOIN @loaded_assemblies la
    ON cs.database_name = la.database_name
    AND cs.assembly_id = la.assembly_id;

PRINT '';
PRINT 'Total User-Defined Assemblies: ' + CAST(@assembly_count AS VARCHAR(10));
PRINT 'Currently Loaded in Memory: ' + CAST(@loaded_count AS VARCHAR(10));
PRINT '';
PRINT '';

-- =====================================================
-- CLR OBJECTS WITH COUNT
-- =====================================================
PRINT '========================================';
PRINT 'CLR OBJECTS IN THE DATABASE';
PRINT '========================================';
PRINT '';

DECLARE @clr_objects TABLE
(
    [database_name] [sysname] NOT NULL,
    [object_name] [sysname] NOT NULL,
    [object_type] [nvarchar](60) NULL,
    [assembly_name] [sysname] NOT NULL,
    [object_definition] [nvarchar](MAX) NULL
);

SELECT @command = '
IF DB_ID(''?'') > 5
BEGIN
    USE [?];
    SELECT
        ''?'' AS database_name,
        o.name AS object_name,
        o.type_desc AS object_type,
        a.name AS assembly_name,
        m.definition AS object_definition
    FROM sys.objects o
    INNER JOIN sys.assembly_modules am ON o.object_id = am.object_id
    INNER JOIN sys.assemblies a ON am.assembly_id = a.assembly_id
    LEFT JOIN sys.sql_modules m ON o.object_id = m.object_id
    WHERE a.is_user_defined = 1;
END';

INSERT INTO @clr_objects EXEC sp_MSforeachdb @command;

-- RESULT SET WITH OBJECTS AND CORRECT COUNT (1 per object)
SELECT
    database_name AS [Database],
    object_name AS [Object Name],
    object_type AS [Object Type],
    assembly_name AS [Assembly Name],
    1 AS [Object Count]
FROM @clr_objects
ORDER BY database_name, assembly_name, object_type, object_name;

PRINT '';
PRINT '========================================';
PRINT 'END OF REPORT';
PRINT '========================================';

SET NOCOUNT OFF;

The following screenshot shows sample output from the script.

Figure 1: CLR assembly assessment script output

If your instance has CLR assemblies that are actively in use, review their application-side dependencies and plan replacements before you upgrade SQL Server. As described in the following sections, you can replace them with AWS services such as AWS Lambda on Amazon RDS for SQL Server 2025. You can also convert them to T-SQL procedures or functions, or implement equivalent logic in your application code. Without these changes, your workload might be affected after the instance is upgraded to a newer SQL Server version.

Step 2: Document current functionality

For each CLR object identified:

1. Document its purpose and business logic.
2. Identify calling applications or stored procedures.
3. Determine if the functionality can be replicated in T-SQL.
4. Assess performance requirements.

Step 3: Choose your replacement strategy

Four replacement options are available. Choose between them based on how your CLR objects are used and how much refactoring you can take on.

Option A: Upgrade to Amazon RDS for SQL Server 2025 with external REST endpoint invocation

Best for: Staying on a fully managed Amazon RDS platform while you replace CLR functionality with cloud-native AWS services such as AWS Lambda, AWS Step Functions, and other AWS APIs.

How it works:

• Upgrade to Amazon RDS for SQL Server 2025, which introduces support for External REST Endpoint invocation through the native sp_invoke_external_rest_endpoint stored procedure.
• Convert CLR assembly logic into AWS Lambda functions or other AWS services that expose REST APIs.
• Invoke AWS services directly from within SQL Server using sp_invoke_external_rest_endpoint, eliminating the need for CLR assemblies.
• Use AWS services such as AWS Lambda, Amazon Simple Queue Service (Amazon SQS), Amazon Simple Notification Service (Amazon SNS), and AWS Step Functions to replicate and extend CLR functionality.

Pros:

• Stays on the fully managed Amazon RDS platform, with no infrastructure to manage.
• Uses a modern cloud-native architecture with AWS Lambda and other services.
• Better security, scalability, and maintainability compared to CLR assemblies.
• Direct integration from SQL Server to AWS services without middleware.
• Future-proof approach aligned with cloud-native best practices.
• No sysadmin permissions required.

Cons:

• Requires a one-time refactor of CLR logic as AWS Lambda functions or REST-accessible AWS services.

Example: Rewrite CLR assembly logic, such as regex validation or string operations, as an AWS Lambda function and invoke it directly from SQL Server. For more information, see Invoke AWS services directly from Amazon RDS for SQL Server 2025 and Building agentic AI patterns with Amazon Bedrock and SQL Server 2025 on Amazon RDS.

Option B: Convert to Transact-SQL (T-SQL) on Amazon RDS for SQL Server 2017 and later

Best for: String manipulation, calculations, data validation, and basic business logic.

How it works:

• Rewrite CLR functions/procedures using native T-SQL equivalents.
• Use built-in T-SQL functions such as STRING_SPLIT, JSON functions, and window functions.
• Use SQL Server 2017 and later features such as STRING_AGG, TRIM, and CONCAT_WS.

Pros:

• No infrastructure changes required.
• Stays within the Amazon RDS for SQL Server managed service.
• No additional AWS costs.
• Lower latency, because there are no network calls.
• Easier to maintain for database teams.

Cons:

• T-SQL might be less performant for complex operations, such as regex and complex calculations.
• Limited functionality compared to full programming languages.
• Might require significant code rewriting.
• Some CLR operations have no direct T-SQL equivalent.

Example: Replace CLR regex validation with T-SQL LIKE or PATINDEX patterns, or with JSON validation functions.

Option C: Move to the application layer on Amazon RDS for SQL Server 2017 and later

Best for: Complex business logic, external API calls, data transformations, and operations that require third-party libraries.

How it works:

• Extract the CLR logic and reimplement it in your application code, such as C#, Java, or Python.
• Your application handles the logic before or after the database calls.
• Replace CLR stored procedures with standard stored procedures that return data.
• Your application processes the data and writes the results back if needed.

Pros:

• Better separation of concerns, with the database for data and the application for logic.
• Easier to version control, test, and deploy.
• Can use full programming language features and libraries.
• Easier to maintain and debug.
• Better scalability for compute-intensive operations.

Cons:

• Requires application code changes.
• Additional network round-trips between app and database.
• Potential performance impact for data-intensive operations.
• May require refactoring existing application architecture.
• Increased application complexity.

Example: Move a CLR assembly that performs complex calculations into a C# service layer method that your application calls.

Option D: Migrate to SQL Server on Amazon EC2

Best for: Heavy CLR usage that is difficult to replace, applications with extensive CLR dependencies, and time-sensitive migrations.

How it works:

• Amazon Elastic Compute Cloud (Amazon EC2): Full control over the SQL Server instance, with complete CLR support and sysadmin access.
• Migrate your database to the new environment while you keep your CLR assemblies.
• Configure CLR strict security and trust assemblies as needed.

Pros:

• Maintains full CLR support.
• Minimal code changes required.
• Faster migration path.
• Can use existing CLR assemblies.

Cons:

• More management overhead for patching, backups, and monitoring.
• Higher costs compared to Amazon RDS for SQL Server.
• Full responsibility for database administration.
• Might delay modernization efforts.
• Potential security risks if CLR strict security is disabled.

Example: Migrate from Amazon RDS for SQL Server 2016 with CLR to SQL Server on Amazon EC2, with CLR enabled and the assemblies trusted.

Conclusion

The deprecation of SQL CLR support on Amazon RDS for SQL Server 2017 and later requires proactive planning to maintain business continuity during the upgrade. We recommend taking the following actions:

1. Run the assessment script provided in this post to identify CLR assemblies and dependent objects in your SQL Server 2016 instances.
2. Document the functionality of each CLR object and assess which replacement strategy best fits your use case.
3. Begin testing your chosen replacement approach in a non-production environment.
4. Plan your migration timeline to complete CLR replacement before Amazon RDS for SQL Server 2016 is retired.

If you have questions about upgrading from SQL Server 2016, contact AWS Support or see the Amazon RDS for SQL Server documentation.

About the authors