Create Assembly is denied on AWS RDS (SQL Server). Is there a workaround?

Question

I want to copy a database using a build script to RDS. In one stored procedure there is a create assembly command. I cannot create this procedure because it says "Permission denied". I am admin, but I do not have 'sys_admin' rights, as it is not possible in RDS.

I created an AWS-RDS instance with SQL Server Enterprise version. I connected from my local MS SQL Management Studio. I run the create procedure script.

This is similar to the script I ran.

USE [master]
GO
CREATE DATABASE [xxx]
GO
ALTER DATABASE [xxx] SET COMPATIBILITY_LEVEL = 130
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [xxx].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO


CREATE ASSEMBLY [xxx_clr]
FROM 0x4...
WITH PERMISSION_SET = SAFE
GO
-- error will appear here !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

CREATE PROCEDURE [pm].[xxx_SP]
    @srcTblName [nvarchar](4000),
    etc..
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [xxx_clr].[xxxCLR].[xxxMain]
GO

Is there a way to do a workaround to create an assembly on RDS?

The script you posted will attempt to create the assembly in the master database. Have you tried adding a USE statement for your user database? — Dan Guzman
Does Amazon RDS allow SQLCLR at all? Even Azure SQL doesn't allow it — Panagiotis Kanavos
Doco says no. There really isn't much more to it. docs.aws.amazon.com/AmazonRDS/latest/UserGuide/… Is it really necessary to do whatever you need to do in an assembly? If it's complicated (requiring other assemblies, communicating with external services), it's not going to work anyway. If it's not complicated you can just build it in SQL — Nick.McDermaid
I tried to create an assembly on the user database. I received the following error: Msg 10343, Level 14, State 1, Line 415 CREATE or ALTER ASSEMBLY for assembly 'function_xxxx_clr' with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly. — Jsimp
Unfortunately, it is necessary to create an assembly. The proposed steps in the error message cannot be performed, as I do not have the sys_admin rights. — Jsimp

user4603841 user4603841 · Accepted Answer · 2019-07-16T17:27:39

CREATE ASSEMBLY statements are no longer possible in RDS since SQL Server 2017 because the clr strict security DB parameter group value is set to 0 by default and cannot be overridden. Assembly whitelisting using sp_add_trusted_assembly is also not possible because sysadmin priviledges are needed to execute the statement.

The only other approaches to achieve this within AWS would be to

(1) Create a SQL Server instance on EC2 to grant the DB user unfettered privileges.

(2) Attempt to use an RDS SQL Server instance earlier than the 2017 edition. You may need to sign the assembly.

This post details some of the implications of the clr strict security setting updates in SQL Server 2017.

Create Assembly is denied on AWS RDS (SQL Server). Is there a workaround?

1 Answers