SQL Server integrace .NET CLR

05.11.2022

Slovníček

SQL Server Databázový server původně vyvinutý společností Sybase a poté koupený a rozvíjený společností Microsoft. První verze je z roku 1989 a jedná se o jednu z nejrozšířenějších databází.
T-SQL Transact SQL je rozšíření jazyka SQL, které používá SQL Server. Má vlastní syntaxi a funkce, podobně jako PL/SQL od společnosti Oracle.
SQL CLR Common Language Runtime nám umožňuje spustit kód napsaný v .NET z T-SQL.

Obsah

Úvod

Tento článek je doplněním předchozího článku, kde ale už nebyl prostor problematiku SQL CLR popsat do většího detailu, o což se pokusím nyní 🙃 CLR integrace nám umožňuje kód napsaný v .NET spustit v SQL Server databázi pomocí T-SQL. V praxi to znamená, že můžeme například vytvořit proceduru nebo funkci v C# a díky tomu využít funkce, které jinak v T-SQL k dispozici nemáme. Dalším důvodem proč CLR použít může být také vyšší výkon.

Objekty, které lze takto vytvořit jsou

  • stored procedures
  • triggers
  • user-defined functions
  • user-defined types
  • user-defined aggregates

Nastavení

Máme následující možnosti jak CLR nastavit (povolit)

  • Zapnout Trustworthy
  • Vypnout CLR strict security
  • Zalistovat knihovnu do seznamu důvěryhodných pomocí sp_add_trusted_assembly
  • Podepsat knihovnu certifikátem (nebo asymetrickým klíčem), tzv. module signing
Každá z možností má nějakou výhodu, ale doporučovanou praktikou je použít certifikát nebo asymetrický klíč (module signing).

Trustworthy

Asi nejjednodušší možnost na nastavení, ale zároveň nejméně bezpečná. Trustworthy příznakem říkáme, že databázi důvěřujeme, ale tím otevíráme případnému útočníkovi další možnosti útoku. 

-- Enable CLR
EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;

-- Configure database as trustworthy
ALTER DATABASE <database_name> SET TRUSTWORTHY ON

CLR Strict Security

Tato možnost je na nastavení velmi jednoduchá, o něco bezpečnější než předchozí, ale také není ideální. Před verzí SQL Server 2017 bylo možné spouštět kód, který měl oprávnění SAFE, například 

CREATE ASSEMBLY DoSomething FROM 'C:\DoSomething.dll' WITH PERMISSION_SET = SAFE;
od verze 2017 se ale toto oprávnění již nebere v potaz a Microsoft nás tímto chce donutit k tomu, abychom kód podepisovali certifikátem nebo asymetrickým klíčem. Nové chování je ale možné vypnout. 
-- Enable CLR
EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;

-- Disable CLR strict security option
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'clr strict security', 0; 
RECONFIGURE;

sp_add_trusted_assembly

Tato možnost je už složitější než předchozí dvě. Musíme získat hash naší knihovny a zaregistrovat ho do seznamu důvěryhodných knihoven. Pokud knihovnu upravíme, musíme zaregistrovat její novou verzi (a ideálně původní verzi odregistrovat). 

-- Get binary value
-- Option 1 (from already uploaded assembly): Database > Programmability > Assemblies > <assembly> > Script Assembly as > CREATE To > New Query Editor Window
-- Option 2 (from SSDT deployment script)
declare @assembly varbinary(max) = 0x...

-- Get hash
DECLARE @hash varbinary(64) = HASHBYTES('SHA2_512', @assembly);

-- Register assembly
EXEC sys.sp_add_trusted_assembly @hash, N'CLR Assembly';

-- Select all trusted assemblies
select * from sourcedatabase.sys.trusted_assemblies

-- Unregister assembly
EXEC sys.sp_drop_trusted_assembly @hash

Module Signing

Doporučená možnost, ale zároveň nejpracnější varianta. Každou knihovnu musíme podepsat certifikátem nebo asymetrickým klíčem. Abychom knihovnu mohli nahrát na SQL server tak již musí být podepsaná (teoreticky by také šlo dočasně jen pro účely deploymentu povolit CLR Strict Security a knihovnu podepsat až na serveru). Kroky jsou následující

  • Vytvořit certifikát
  • Vytvořit Login z ceritikátu s oprávněním na spouštění CLR
  • Certifikát vyexportovat
  • Podepsat knihovnu certifikátem
    • K tomu jsou potřeba nástroje pvk2pfx.exe a signtool.exe z Windows SDK
  • Naimportovat podepsanou knihovnu
  • Zaregistrovat procedury/funkce z knihovny
  • Spustit CLR
use master 
go

-- Create certificate
CREATE CERTIFICATE ClrCert
ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
WITH SUBJECT = 'Certificate for executing CLR assemblies.'
GO

-- Backup certificate
BACKUP CERTIFICATE ClrCert TO FILE ='C:\Certs\ClrCert.cer'
WITH PRIVATE KEY
(       
    FILE = 'C:\Certs\ClrCert.pvk',
    DECRYPTION BY PASSWORD = 'Pa$$w0rd',
    ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
)
GO

-- Create login from the certificate and setup permissions
CREATE LOGIN ClrAccount FROM CERTIFICATE ClrCert
GRANT UNSAFE ASSEMBLY to [ClrAccount]

/* Offline .DLL signing
---------------------------------------------------------------------------- */
-- Export certificate from the SQL Server to your C:\ drive
-- In case of Docker
-- docker cp <container id>:/Certs C:\Certs

-- Install SDK so we can use pvk2pfx and signtool programs
-- https://developer.microsoft.com/en-us/windows/downloads/windows-sdk/
-- select "Windows SDK for Desktop C++ x86 apps"

-- Search where these tool are located after installation
-- cd "C:\Program Files (x86)\Windows Kits\10\bin"
-- dir pvk2pfx.exe /s

-- Go to the location, for example
-- cd "C:\Program Files (x86)\Windows Kits\10\bin\10.0.22621.0\x86\"

-- Create PFX file 
-- pvk2pfx.exe -pvk C:\Certs\ClrCert.pvk -spc C:\Certs\ClrCert.cer -pfx C:\Certs\ClrCert.pfx -pi Pa$$w0rd -po Pa$$w0rd

-- Sign the .DLL with the PFX file
-- signtool.exe sign /fd SHA256 /f C:\Certs\ClrCert.pfx /p Pa$$w0rd W:\CLRTest\bin\Release\ClrTest.dll 

-- Copy CLR assembly to the SQL Server
-- In case of Docker we login into the shell, create directory and copy .dll
-- docker exec -t -i <container id> /bin/bash
-- mkdir ClrAssemblies
-- docker cp W:\CLRTest\bin\Release\CLRTest.dll  <container id>:/ClrAssemblies/CLRTest.dll

-- Switch to the target database
use TargetDatabase
go

-- Import assembly (TargetDatabase > Programmability > Assemblies)
CREATE ASSEMBLY CLRTest FROM '/ClrAssemblies/CLRTest.dll' WITH PERMISSION_SET = SAFE;
GO

-- Import function from the assembly
CREATE FUNCTION [dbo].[IsLeapYear] (@year INT NULL)
RETURNS BIT
AS EXTERNAL NAME [CLRTest].[UserDefinedFunctions].[IsLeapYear]

-- Execute function
select dbo.IsLeapYear(2022)

Závěr

Nyní umíme spustit .NET kód v SQL Serveru, což nám může velmi zjednodušit SQL kód nebo zvýšit výkon. Také nám to ale může zkomplikovat deployment, proto musíme vždy zvážit výhody a nevýhody 🙂

Odkazy