How to setup Assemblies in Azure SQL Managed Instance

                                       Azure SQL Managed Instance - Assembly Setup 

Here we are discussing how can we setup CLR Assemblies and resolve common Assemblies error in Azure SQL managed instance. 

CLR (Common Language Runtime) enables you to implement functionality of .NET framework in SQL Server. Assemblies are supported in Azure SQL MI not in Azure SQL Database.

CLR integration means we can write stored procedures, triggers, user-defined types, user defined functions (scalar and tables valued), and user-defined aggregate using .NET framwork language, including Microsoft Visual Basic .NET and Microsoft Visual C#.

Assemblies can be migrated to Azure SQL MI using Database backup restore.

Pre-requisites:-

You must have sysadmin privilege to setup assemblies. 

Please follow below steps to setup Assemblies in Azure SQL MI instance

1. Change the database owner to SA, run below query for the same

EXEC sp_changedbowner 'sa'

2. CLR integration is off in Azure SQL MI by default, we must enable the CLR integration. 

To check CLR integration run below query:-

SELECT * FROM sys.configuration WHERE name='clr enabled'

If the value is 0 it is not enabled and run below query to enble it.

EXEC sp_configure 'clr enabled', 1;

RECONFIGURE;

GO

3. To view existing assemblies run below query under user-defined database.

SELECT * FROM sys.assemblies

4. Permission set of an assemblies should be 1 (Please read about permission set of assemblies)

1.   SAFE: The default permission set applied when not specified explicitly2.

2.   EXTERNAL_ACCESS: Specifies a set of code access permissions that are granted to the assembly when it is accessed by SQL Server2.

3.   UNSAFE: Specifies a set of code access permissions that are granted to the assembly when it is accessed by SQL Server2.

Please note that when clr strict security option is enabled, all assemblies are treated as UNSAFE

5. Make the Assemblies trusted, to make the Assemblies trusted you have to pass the Hash value of that Assembly.

SELECT HASHBYTES(‘SHA2_512’, ‘Assembly_name’) FROM sys.assemblies

6. To make it trusted

EXEC sp_add_trusted_assembly Hash_value_of_assembly

GO

7. Make the database trustworthy

ALTER DATABASE [db_name] SET TRUSTWORTHY ON

GO

8. Use the below sp to drop the assembly from trusted

sp_drop_trusted_assembly)

Resources

Enabling CLR Integration - SQL Server | Microsoft Learn 

CREATE ASSEMBLY (Transact-SQL) - SQL Server | Microsoft Learn 

sys.trusted_assemblies (Transact-SQL) - SQL Server | Microsoft Learn