Integrace CLR na SQL Serveru

05.11.2022

Slovníček

SQL Server Databázový server, původně vyvinutý společností Sybase, byl později zakoupen a dále rozvíjen společností Microsoft. První verze pochází z roku 1989 a dnes patří mezi nejrozšířenější databázové systémy.
T-SQL Transact-SQL je rozšíření jazyka SQL, které využívá SQL Server. Nabízí vlastní syntaxi a funkce, podobně jako PL/SQL používaný v databázích Oracle.
SQL CLR SQL Common Language Runtime umožňuje spouštět kód napsaný v .NET přímo z T-SQL.

Obsah

Úvod

Tento článek navazuje na předchozí článek, ve kterém nebyl prostor problematiku SQL CLR popsat podrobněji. Nyní se to pokusím napravit. CLR integrace umožňuje spouštět kód napsaný v .NET přímo v databázi SQL Server pomocí T-SQL. V praxi to znamená, že například můžeme vytvořit proceduru nebo funkci v jazyce C#, což nám umožní využít funkcionalitu, která není jinak v T-SQL dostupná. Dalším důvodem pro použití CLR může být i 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ěkolik možností, jak nastavit (povolit) CLR:

  • 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 module signing, tj. certifikát nebo asymetrický klíč.

Trustworthy

Pravděpodobně nejjednodušší možnost nastavení, ale zároveň nejméně bezpečná. Nastavením příznaku Trustworthy deklarujeme, že databázi důvěřujeme, čímž však zároveň otevíráme potenciální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 velmi jednoduchá na nastavení, o něco bezpečnější než předchozí, ale stále není ideální. Před verzí SQL Server 2017 bylo možné spouštět CLR kód s oprávněním SAFE, například: 

CREATE ASSEMBLY DoSomething FROM 'C:\DoSomething.dll' WITH PERMISSION_SET = SAFE;
Od verze SQL Server 2017 se ale toto oprávnění již nebere v úvahu. Microsoft tímto krokem usiluje o to, aby byl kód podepisován certifikátem nebo asymetrickým klíčem. Nové chování je však možné deaktivovat. 
-- 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 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, je nutné 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, musí být již podepsaná. Alternativně můžeme dočasně povolit CLR Strict Security pouze pro účely nasazení 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í jsme schopni spustit .NET kód v SQL Serveru, což může výrazně zjednodušit SQL kód, přidat chybějící funkcionalitu nebo optimalizovat výkon. Tato možnost však může také zkomplikovat deployment, a proto je vždy nutné zvážit výhody a nevýhody.

Odkazy