SQLCLR Support on AWS SQL Server 2017 RDS

Question

I need to load an Assembly in AWS SQL Server 2017 RDS.

I am not able to find it in the documentation. Is this possible? If yes, then how?

This is similar to, but not a duplicate of, "SQL Server CLR stored procedures in AWS?", since I need to create the assembly in SQL Server 2017 AWS and changing parameter clr enabled to 1 is not possible in this version, and this is not addressed in that question or answer.

Answer 1

While this used to work (I did test on AWS 2016 SP1 CU2), it seems that it is now (as of SQL Server 2017) being blocked by AWS due to the new "CLR strict security" instance-level configuration setting.

As per the Reddit discussion noted in the comment on the answer linked by @Larnu,

In RDS SQL Server 2016 and backwards, CLR is supported in SAFE mode and using assembly bits only. Since in SQL Server 2017 the parameter 'CLR strict security' is set to 1 by default and even using CLR SAFE and assembly bits options, you will receive an error because the parameter 'CLR strict security' need to be disabled or need to trust the assembly by using the stored procedure "sp_add_trusted_assembly". But this stored procedure, requires sysadmin permissions, that is not supported in RDS SQL Server as you can see in the Microsoft Documentation: ...

Therefore, RDS does not support UNSAFE assemblies, and all the assemblies are treated like UNSAFE with this new feature, the feature CLR will not be supported in RDS SQL Server 2017. The parameter 'CLR strict security' will be blocked by our automation's and customers will not be able to modify this. CLR uses CAS (code access security) in the .NET Framework and is no longer supported as a security issue. A CLR assembly created with PERMISSION_SET = SAFE may be able to access to external resources, unmanaged code and acquire sysadmin privileges.
...
It is unfortunate that because of the above, CLR feature is NOT SUPPORTED in RDS SQL Server 2017.

You can however, also consider using SQL Server on EC2 which gives you full Administrative privileges just like you would with on-prem installations.

At first I was hopeful that there would be a way to get AWS to lift this restriction, given that AWS's reasons are:

1. the parameter 'CLR strict security' need to be disabled or need to trust the assembly by using the stored procedure "sp_add_trusted_assembly"

   This is not entirely true. You can also use Module Signing (which is the better approach anyway)

2. RDS does not support UNSAFE assemblies, and all the assemblies are treated like UNSAFE with this new feature

   That wording is a MAJOR failing on Microsoft's part. Assemblies are not treated as UNSAFE in an operational sense, only in the requirements for being loaded. Meaning ALL assemblies, even those marked as SAFE and EXTERNAL_ACCESS, must pass the same checks previously reserved for only UNSAFE assemblies.

Since both of those reasons are essentially misunderstandings, I thought that maybe AWS could re-allow "CLR enabled" and just support SAFE assemblies via Module Signing. But, then I re-read the message from "AWS Premium Support" and noticed that he ("Johnson") stated that the reason for not supporting sp_add_trusted_assembly was that it requires sysadmin permissions, which is not supported. The Module Signing approach requires granting the certificate-based login the UNSAFE ASSEMBLY permission, and only a sysadmin can do that. So not even that will work 😾. Looks like your only option there is to run an EC2 VM with SQL Server running on it (suggested by AWS support and @BeardOfTriumph's answer).

This situation is very similar to Azure: Azure SQL Database does not support SQLCLR at all (though it previously did for about 18 months, ending abruptly in mid-April, 2016), but you can run SQL Server on an Azure VM for full SQLCLR support, and more recently they introduced Azure SQL Database Managed Instances, which supports SQLCLR.

Regardless, creating from a DLL was never supported, only creating from a VARBINARY literal / hex bytes. And you can convert a DLL into a VARBINARY string using an open source utility that I created, BinaryFormatter. Also, please see my series on this: SQLCLR vs SQL Server 2017

Answer 2

Unfortunately in 2017+, it is not supported. And even if you did find some way to hack into the settings and get it to work, it would be in no way supported by Amazon when it decided to break. We went round and round trying to get it to work based on the fact that it would work in 2016, but they did completely disable it with the new security settings in SQL Server 2017. One of the several reasons we ended up sticking with an EC2 instance hosting SQL Server, as opposed to RDS.