Migrating a SQL Database with CLR Integration from Old SQL Server Versions

Introduction

Part of life as an admin is doing lifecycle management for third party applications. Many times, that boils down to a version of the following scenario:

1. The company buys software from Vendor A for business purposes.
2. The application is installed on a computer, a database is created on a central SQL server. There may or may not be a support contract.
3. The software works, users are happy, and because the software works well, it is used for years and years. Because it's likely installed in virtual machines, hardware lifecycle management is not an issue and the whole system runs for a decade.
4. Eventually, Microsoft stops supporting the Windows / SQL version of that application, and the corporate cyber security people get more and more insistent on moving to a new Windows / SQL combination.
5. You move the database to a modern platform, and suddenly things don't work anymore because security has been tightened.

Further complications may arise if the original vendor is no longer in business, there is no support contract, there is no supported migration for the application which is a decade old, or ...

The case for which I write this article is that the database uses an unsigned assembly for clr integration which, for various reasons, is no longer under development.

Background

SQL Server supports the loading of external dotNET assemblies to compound the default T-SQL functionality with basically everything you could program in such an assembly. This is a very powerful functionality because you can use custom calculations and algorithms as part of a regular query.

In the past, a DBA could import an assembly, and simply mark it 'safe'. 'Safe' in this context means "Only internal computation and local data access are allowed. SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry."

So the short of it is that safe assemblies could be executed without problem. And this was especially convenient for extending SQL functionality like converting UTC to local time for stored timestamps where you only do data transformation. However, starting with SQL 2017, Microsoft has decided that setting the permissions in the Assembly properties is ignored and all Assemblies are UNSAFE by default regardless of that setting.

This is called CLR Strict Security.

CLR uses Code Access Security (CAS) in the .NET Framework, which is no longer supported as a security boundary. A CLR assembly created with PERMISSION_SET = SAFE may be able to access external system resources, call unmanaged code, and acquire sysadmin privileges. Beginning with SQL Server 2017 (14.x), an sp_configure option called clr strict security is introduced to enhance the security of CLR assemblies. clr strict security is enabled by default, and treats SAFE and EXTERNAL_ACCESS assemblies as if they were marked UNSAFE.

As a result, when you try to run a query that would cause an Assembly to be loaded, an error occurs similar to this one.

Msg 10314, Level 16, State 11, Procedure dbo.sp_GetJournalRecords, 
Line 25 [Batch Start Line 2]
An error occurred in the Microsoft .NET Framework while trying to 
load assembly id 65536. The server may be running out of resources, 
or the assembly may not be trusted. Run the query again, 
or check documentation to see how to solve the assembly trust issues. 
For more information about this error: 
System.IO.FileLoadException: Could not load file or assembly 'databasefunctions, 
Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. 
An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

In order to be able to run the code, the Assembly would have to be signed with a certificate or asymmetric key that has a matching login with the 'UNSAFE ASSEMBLY' permission. This may be problematic especially when you are migrating an existing legacy database and you don't have the original Assembly or means to sign it.

Don't get me wrong. If you are designing or setting up a new project, that should be the way to go. I am now talking about the scenario where you have to move an existing setup with running code which you know you can trust.

Setting the Database Owner

After migrating the database to the new server, the first order of business is to correct the ownership of the database. By default, this is sa. However, the GUID of the sa account on the original server will have been different than the GUID of the sa account on the new server.

To fix that, we use the following query:

EXEC sp_changedbowner 'sa'
GO

This is good practice in all cases, but in this case, it is even more so because granting the right to load an unsafe assembly needs to be done by a trusted account with the right privileges such as the sa account. And for that to work, the sa account owning the database needs to be the actual sa account.

Disabling Strict Security

This is the simplest, but also worst solution. You could disable CLR strict security, which leaves everything on that instance in a trusted state. This is the nuclear option and not recommended.

EXEC sp_configure 'clr strict security', 0
RECONFIGURE
GO

I am listing it for the sake of completeness. Do not do this unless you have no other option.

Explicitly Add the Assembly to the Trusted Assemblies

SQL Server has the option to add an assembly to the list of Trusted Assemblies. This is effectively a bypass of 'clr strict security' for that particular Assembly. This is not a good practice for new projects, but if you're dealing with legacy code that was already trusted by your organization, it is an acceptable tradeoff.

You can do this with sp_add_trusted_assembly which requires a hash of the assembly and the descriptive name of the assembly. This approach does require you to calculate that hash. For that, you can use this query. This is not my original work. I pieced it together from various public code fragments:

declare
     @hash binary(64),
     @description nvarchar(4000)

select
    @hash = HASHBYTES('SHA2_512', af.content),
    @description = a.clr_name
FROM sys.assemblies a
JOIN sys.assembly_files af
    ON a.assembly_id = af.assembly_id
WHERE
    af.name = 'databasefunctions'

EXEC sys.sp_add_trusted_assembly  @hash, @description
go

sys.assemblies contains the name, clr name and internal ID of the assembly. sys.assembly_files contains the binary file data. Note that it is important that the where clause is performed on the name field of the sys.assembly_files table.

In my case, not only the DatabaseFunctions.dll file is deployed in the database, but also the DatabaseFunctions.pdb file. As a result, if we would select on the name field of the sys.assemblies table in the join operation, BOTH files would be hashed and the resulting hash would be incorrect because SQL Server needs the hash of only the DLL file.

When doing this, you can enable the clr for that particular assembly even if strict security is enabled. The approach for trusting a specific assembly is a good one if you have only a single or a handful of assemblies which you already trusted. It's not good practice, but you already trusted those assemblies you're not decreasing your overall security and it ensures that new projects on that database are forced to go through the signing procedure before being deployed and released.

Explicitly Trusting the Whole Database

An alternative to trusting only specific assemblies is to trust an entire database and everything in it. In general, this isn't a stellar idea because it makes it possible to keep deploying unsigned assemblies going forward which would decrease the security of your system below what it was.

ALTER DATABASE ClrTest SET TRUSTWORTHY ON; GO

The benefit to this approach is that you don't have to individually configure the trust of the assemblies. And if the database is host to a gazillion assemblies or it is only a sandbox system, it may be the most convenient way to get going.

Conclusion

I have shown three approaches for dealing with unsigned assemblies in a clr integrated database that is migrated from an older SQL version to 2017 or higher. In descending order of security / ascending order of 'don't do this', these are:

1. configuring trust for a single assembly
2. configuring trust for a whole database
3. disabling strict security for the entire instance

Which one you choose is up to your specific considerations, and if possible, the best approach is of course getting the assemblies signed so you can avoid this issue altogether.

History

• 4^th May, 2023: First version