SQL Server CLR functions are not working after enabling FIPS mode in Database Server

Question

SQL Server CLR functions are not working after enabling FIPS mode in Database Server

getting below error:

System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host

After doing some research I found

** solution is while creating assembly I need to use USAFE as permission set like below bolded

CREATE ASSEMBLY assembly_name

[ AUTHORIZATION owner_name ]

FROM { <client_assembly_specifier> | <assembly_bits> [ ,...n ] }

[ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]

But we can't make it unsafe can anyone please help me what is the alternative we can't move out of using CLR functions it will be huge change.

Answer 1

What does "SELECT @@version" report?

What is the is output of

sp_configure  'clr strict security'

Note: this configuration parameter is not present in SQL 2016 and earlier.

Why can't you mark your assemblies as UNSAFE?

Note that starting with SQL 2012, all assemblies are by definition unsafe. When the CLR was introduced in SQL 2005, SQL Server relied on a feature in .NET known as Code Access Security in order divide assemblies into safe and unsafe. But CAS ceased to be a security boundary with .NET 3, so even if an assembly is created as "safe", there is no guarantee that it cannot affect the server in general.

Answer 2

Hi @Banerjee, Debasis

How about sign CLR assembly with asymmetric key.

Step 1: Create Asymmetric Key from Assembly File

USE master;
GO
CREATE ASYMMETRIC KEY CLRStringSplitKey FROM EXECUTABLE FILE = 'C:\CLRStringSplit.dll';
GO

Step 2: Create SQL Server Login linked to the Asymmetric Key

USE master;
GO
CREATE LOGIN CLRStringSplitKeyLogin FROM ASYMMETRIC KEY CLRStringSplitKey;
GO

Step 3: Grant UNSAFE assembly permission to the login created in Step 2

USE master;
GO
GRANT UNSAFE ASSEMBLY TO CLRStringSplitKeyLogin;
GO

Step 4: Create a SQL Server database user for the SQL Server login created in Step 2

USE SampleTest2017;
GO
CREATE USER CLRStringSplitKeyLogin FOR LOGIN CLRStringSplitKeyLogin;
GO

Step 5: Create CLR Assembly

USE SampleTest2017;
GO
CREATE ASSEMBLY CLRStringSplit FROM 'C:\CLRStringSplit.dll' WITH PERMISSION_SET = SAFE;
GO

Please refer to these two articles for more details:

Impact of CLR Strict Security configuration setting,

Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies).

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.