CSharp:CLR Assembly vs SQL Server

Introduzione

E' possibile estendere le funzionalità di SQL Server per integrare Stored Procedure e Funzioni con funzioni esportate mediante DLL create ad HOC con Visual Studio, queste DLL si chiamano CLR Assembly (CLR = Common Language Runtime). Questa guida è stata realizzata seguendo il tutorial Su sharpcorner.com.
In Visual Studio si parte creando una nuova solution creando una "Class Library (.Net Framework)".

Prima di procedere è necessario render noto che con SQL Server 2017 si è introdotta una configurazione a livello di server chiamata "CLR strict security" che è abilitata per default. Questa opzione impone che tutti gli assembly devono essere accompagnati da un certificato (o chiave asimmetrica), c'è un workaround per disabilitarlo come qui.

Identificare la versione .Net eseguita dal Server

select * from sys.dm_clr_properties

Che nel mio caso restituisce:

name	value
directory	C:\Windows\Microsoft.NET\Framework64\v4.0.30319\
version	v4.0.30319
state	CLR is initialized

Eventuali DLL referenziate dalla DLL del progetto creato ad HOC eventualmente van prese da questa cartella come indicato da quanto prodotto dalla SELECT di sopra.

Creazione di un Assembly di test

Passi:

1. Da Visual Studio, creare una solution quindi una Class Library in C#, registrare la DLL con una chiave asimmetrica ma senza password;
2. Da SSMS (SQL Server Management Studio), DB "master", creare una istanza di chiave asimmetrica per l'Assembly creato prima;
3. Da SSMS, DB "master", creare una login (oggetto di autenticazione) collegata alla chiave asimmetrica creata prima;
4. Da SSMS, DB "master", assegnare permessi\grant alla login creata prima per l'utilizzo dell'assembly 'UNSAFE';
5. Da SSMS, DB "master", creare una utenza per la login creata;
6. Da SSMS, DB client, creare il collegamento alla DLL Assembly implementata;
7. Da SSMS, DB client, creare una Funzione custom in SQL Server per l'accesso all'Assembly registrato al punto precedente;
8. Da SSMS, DB client, invocare la Funzione di cui al punto precedente.

Solution

Creare la solution di nome, ad es, "CLRAssembly4SQLServer_Test1" (con unico progetto omonimo).
Nel seguente codice riportato come esempio notare l'obbligatorietà di usare una classe static oltre che il metodo pubblico esportato.

using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace CLRAssembly4SQLServer_Test1
{
    public static class ClasseDiProva1
    {
        public static string ConversioneData(string dataInput, string formato)
		{
			try
			{
				CultureInfo provider = new CultureInfo("en-US");
 
				string[] inputFormats = {
				"MM/dd/yyyy", "M/d/yyyy", "M/dd/yyyy","dd/MM/yyyy",
				"MM/d/yyyy", "M/d/yyyy h:mm:ss tt", "MM/d/yyyy h:mm:ss tt",
				"M/d/yyyy h:mm tt", "MM/dd/yyyy hh:mm:ss", "M/d/yyyy h:mm:ss",
				"M/d/yyyy hh:mm tt", "M/d/yyyy hh tt", "M/d/yyyy h:mm", "M/d/yyyy h:mm",
				"MM/dd/yyyy hh:mm", "M/dd/yyyy hh:mm",
				"MM-dd-yyyy", "M-d-yyyy", "M-dd-yyyy","dd-MM-yyyy",
				"MM-d-yyyy", "M-d-yyyy h:mm:ss tt", "MM-d-yyyy h:mm:ss tt",
				"M-d-yyyy h:mm tt", "MM-dd-yyyy hh:mm:ss", "M-d-yyyy h:mm:ss",
				"M-d-yyyy hh:mm tt", "M-d-yyyy hh tt", "M-d-yyyy h:mm", "M-d-yyyy h:mm",
				"MM-dd-yyyy hh:mm", "M-dd-yyyy hh:mm"};
 
				DateTime dt = DateTime.ParseExact(dataInput, inputFormats, provider, DateTimeStyles.None);
 
				return dt.ToString(formato);
			}
			catch
			{
				return string.Empty;
			}
		}
    }
}

Registrazione della DLL

Si associa all'assembly uno "strong name key". Tasto dx sul progetto poi cliccare sul TAB "Signing" ed infine ceccare "Sign the assembly" e scegliere ""

A questo punto si creerà il file per la registrazione crittografata con lalgoritmo di firmasha256RSA':

Quanto sopra creerà il file "CLRAssemblyTest1.snk". Tale file sarà automaticamente collocato nella cartella principale del progetto per cui si aggiorneranno anche le risorse del progetto.
A questo la DLL, "CLRAssembly4SQLServer_Test1.dll", è pronta l'uso e la si può collocare nella cartella voluta e da cui si farà puntare SQL Server per disporne dei metodi pubblici e statici esportati.

Collegamento SQL Server alla DLL

I seguenti passi per poter utilizzare le funzioni della DLL da Funzioni e Stored Procedure di un DB cliente.

Creazione Asymmetric Key

Sul DB 'master', Create Asymmetric Key from Assembly File

USE master;
GO
CREATE ASYMMETRIC KEY CLRAssembly4SQLServer_Test1_Key
FROM EXECUTABLE FILE = 'D:\Tmp\CLRAssembly_Test\CLRAssembly4SQLServer_Test1.dll';
GO

Riassumendo qui quanto creato anche negli step successivi:

Si nota oltre all'oggetto della chiave asimmetrica anche la user creata qui di seguito.

Creazione Login collegata alla Asymmetric Key

Sul DB 'master', create SQL Server Login linked to the Asymmetric Key

USE master;
GO
CREATE LOGIN CLRAssembly4SQLServer_Test1_KeyLogin FROM ASYMMETRIC KEY CLRAssembly4SQLServer_Test1_Key;
GO

Grant UNSAFE assembly alla Login

Sempre sul DB 'master', grant UNSAFE assembly permission to the login created

USE master;
GO
GRANT UNSAFE ASSEMBLY TO CLRAssembly4SQLServer_Test1_KeyLogin;
GO

Creazione dello user per la Login

Sul DB destinazione, create a SQL Server database user for the SQL Server login created

USE MioDB;
GO
CREATE USER CLRAssembly4SQLServer_Test1_KeyLogin FOR LOGIN CLRAssembly4SQLServer_Test1_KeyLogin;
GO

Che produrrà:

Create CLR Assembly

Sul DB destinazione, creazione dell'oggetto interno Assembly che è un link alle funzioni della DLL.

USE MioDB;
GO
CREATE ASSEMBLY CLRAssembly4SQLServer_Test1
FROM 'D:\Tmp\CLRAssembly_Test\CLRAssembly4SQLServer_Test1.dll' WITH PERMISSION_SET = SAFE;

Che produce:

when creating an assembly in the MS SQLServer database, you can specify one of three different levels of security.

1. SAFE
2. EXTERNAL_ACCESS
3. UNSAFE

Per verificare più facilmente se l'Assembli è stato registrato basta eseguire la seguente query:

SELECT 
  * 
FROM sys.assembly_files

SAFE is the default permission set and works for the majority of scenarios.

EXTERNAL_ACCESS addresses scenarios in which the code needs to access resources outside the server, such as files, network, registry, and environment variables.

UNSAFE code permission is for those situations in which an assembly is not verifiably safe or requires additional access to restricted resources, such as the Microsoft Win32 API.

Any one of the below conditions must be met for creating an EXTERNAL_ACCESS or UNSAFE assembly.

The assembly is a strong name signed or Authenticode signed with a certificate. This strong name (or certificate) is created inside SQL Server as an asymmetric key (or certificate), and has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission (for external access assemblies) or UNSAFE ASSEMBLY permission (for unsafe assemblies). The database owner (DBO) has EXTERNAL ACCESS ASSEMBLY (for EXTERNAL ACCESS assemblies) or UNSAFE ASSEMBLY (for UNSAFE assemblies) permission, and the database has the TRUSTWORTHY Database Property set to ON.

We have created the assembly with SAFE permission. So no need to disable the CLR Strict Security feature and enable the Database Trustworthy feature in the MS SQL Server.

Es. Custom Function

Nel DB di destinazione in cui si userà si crea una funzione di "interfaccia" che la utilizzerà:

USE MioDB
GO 
CREATE FUNCTION dbo.DateConvert_Mio(@date NVARCHAR(100), @format NVARCHAR(100))
RETURNS NVARCHAR(100)
AS
     EXTERNAL NAME [CLRAssembly4SQLServer_Test1].[CLRAssembly4SQLServer_Test1.ClasseDiProva1].[ConversioneData];

Uso

SELECT dbo.DateConvert_Mio('02/12/2020', 'dd-MMM-yyyy') AS DataConvertita

Rimozione

Comodamente usando SSMS, occorre rispettare una corretta sequenza:

• Cancellare lo user: Databases\master\Security\Users\UserDaCancellare
• Cancellare gli oggetti che fan uso dell'Assembly
• Cancellare l'Assembly (o meglio il link creato in SQL Server)
• Cancellare la Login: \Security\Logins\NomeDellaLoginDaCancellare
• Cancellare l'Asymmetric Key: Databases\master\Security\Asymmetric Keys\ChiaveDaCancellare

Attenzione

NOTE

• Attenzione nel creare un Assembly che usa altre DLL sulle quali non c'è controllo in quanto di terze parti, il problema è che per poter essere registrato occorre ricostruire tutta la catena delle dipendenze con altre DLL e tali DLL devono risiedere nella stessa cartella dell'assembly principale.
• Altro problema è che NON DEVONO essere definite proprietà o attributi statici se non di tipo read only. QUESTO perché altrimenti l'Assembly non sarebbe thread safe e sarebbe pericoloso usarlo in SQL Server!

CREATE ASSEMBLY failed because type 'VF_UtilServices.Security' in safe assembly 'VF_UtilServices' 
has a static field 'EncryptionPassword'. 
Attributes of static fields in safe assemblies must be marked  readonly in Visual C#, ReadOnly in Visual Basic, 
or initonly in Visual C++ and intermediate language.

• Nella classe dell'Assembly NON SI POSSONO usare gli Overloads!

Assembly White List

Da waynesheffield.com sys.sp_add_trusted_assembly da microsoft.com
Per forzare un assembly UNSAFE:

DECLARE @clrDescription nvarchar(4000) = N'Aggiunta per VF_Digitalk_MngNewCharityGW.dll';
DECLARE @clrBin varbinary(max) = 0x4d5a...etc..contentfile;
DECLARE @hash varbinary(64);
SET @hash = HASHBYTES('SHA2_512', @clrBin);
SELECT @hash;
 
EXECUTE sys.sp_add_trusted_assembly @hash, @clrDescription

--Elenco degli Assembly registrati:
SELECT * 
FROM sys.trusted_assemblies

Errori

CREATE ASSEMBLY

CREATE ASSEMBLY __nomeAssembly__
FROM 'D:\__PathAssembly__\__NomeDLLPricipale__.dll' WITH PERMISSION_SET = UNSAFE;

Assembly references assembly which is not present

Assembly '__NomeDLLPricipale__.dll' references assembly 
'system.servicemodel.internals, version=4.0.0.0, culture=neutral, publickeytoken=31bf3856ad364e35.', 
which is not present in the current database.
SQL Server attempted to locate and automatically load the referenced assembly from the same location 
where referring assembly came from, but that operation has failed 
(reason: 2(Impossibile trovare il file specificato.)). 
Please load the referenced assembly into the current database and retry your request.

Cercare il giusto file DLL da aggiungere alla cartella della DLL principale per ricostruire la sequenza di referenze. Es. aggiungere "System.ServiceModel.Internals.dll" che è nella cartella:

C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0

In realtà la migliore cartella le cui DLL darebbero maggiori garanzie di riuscita sono quelle della cartella indicata come depositaria del Framework adottato dal SQL Server. Cartella ottenuta col comando:

select * from sys.dm_clr_properties

Assembly could not be installed because existing policy

Msg 6586, Level 16, State 1, Line 3
Assembly 'System.ServiceModel.Internals' could not be installed because existing policy would keep it from being used.

Mappa e Link

---

CLR Assembly in SQL Server | C# | Integrazioni e librerie

---

Visual Studio | MS SQL | Dizionario

---

Parole chiave: