Tutorial krok po kroku, jak podpisać i zaiportować moduł CLR

W tym artykule pokażę jak od A do Z zaimplementować w .NET dwie metody służące do listowania plików i katalogów i zaimportować te funkcje do SQL 2017 z uwzględnieniem aktualnych best practice (z opcją 'clr strict security’). Czym jest ta opcja i jakie ma działanie zobacz w https://www.mobilo24.eu/sql-clr-w-wersji-2017opcja-clr-strict-security/

Utwórz klasę w Visual Studio (uwaga – koniecznie wybierz  Class Library .NET) – inaczej nie będzie do dyspozycji wszystkich wymaganych referencji (https://stackoverflow.com/questions/48130887/visual-studio-not-recogonizing-microsoft-sqlserver-namespace):

Napisz swój kod, co może wyglądać o tak:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SunClr_Namespace
{
 public class SunClr_Class
 {
 /*
 * Function listing files in a directory
 */
 [SqlFunction(FillRowMethodName = "ListFiles_GetRow")]
 public static IEnumerable ListFiles(string dirPath)
 {
 DirectoryInfo dirInfo = new DirectoryInfo(dirPath);
 return dirInfo.GetFiles();
 }

public static void ListFiles_GetRow(Object obj, out SqlString name, out SqlDateTime lastWriteDateTime, out SqlString type)
 {
 FileInfo fileInfo = (FileInfo)obj;
 name = new SqlString(fileInfo.Name);
 lastWriteDateTime = new SqlDateTime(fileInfo.LastWriteTime);
 type = new SqlString("FILE");
 }

/*
 * Function listing subdirectories in a directory
 */
 [SqlFunction(FillRowMethodName = "ListDirs_GetRow")]
 public static IEnumerable ListDirs(string dirPath)
 {
 DirectoryInfo dirInfo = new DirectoryInfo(dirPath);
 return dirInfo.GetDirectories();
 }

public static void ListDirs_GetRow(Object obj, out SqlString name, out SqlDateTime lastWriteDateTime, out SqlString type)
 {
 DirectoryInfo dirInfo = (DirectoryInfo)obj;
 name = new SqlString(dirInfo.Name);
 lastWriteDateTime = new SqlDateTime(dirInfo.LastWriteTime);
 type = new SqlString("DIR");
 }
 }
}

Otwórz właściwości projektu i podpisz go, w razie potrzeby wygeneruj „strong name key” – jest to plik pfx, który zawiera parę kluczy asymetrycznych. Hasło będzie potrzebne, jeżeli będziesz kiedyś chciał wykorzystać ten sam plik do podpisywania innego modułu. Wtedy nie trzeba będzie generować pliku na nowo, ale raczej go wskazać:

Wybierając Build >> Build zbuduj plik. Nie zapomnij, aby finalna konfiguracja była „Release”. Na tym etapie masz już plik dll:

Przejdź do SSMS. W razie potrzeby zmień 'clr enabled’ i zweryfikuj czy 'clr strict security’ jest ustawione na 1:

USE master
GO

--clr_enabled is an advanced option
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

--new option starting from 2017
sp_configure 'clr strict security'
GO

Zaimportuj klucz asymetryczny do bazy master, utwórz login połaczony z tym kluczem, nadaj temu loginowi uprawnienie „UNSAFE ASSEMBLY”:

--starting from SQL 2017 all assemblies should be signed by certificate (note the validity date) or with assymetric key
--only public key is required
CREATE ASYMMETRIC KEY Sun_ASYMETRICKEY FROM EXECUTABLE FILE = 'C:\temp\SunClr.dll' 
GO

SELECT * FROM sys.asymmetric_keys
GO

CREATE LOGIN Sun_LOGIN FROM ASYMMETRIC KEY Sun_ASYMETRICKEY
GO

GRANT UNSAFE ASSEMBLY TO Sun_LOGIN
GO

Utwórz bazę (jeśli jej jeszcze nie masz), i zaimportuj ASSEMBLY:

CREATE DATABASE Sun_DB
GO

USE Sun_DB
GO

CREATE ASSEMBLY Sun_ASSEMBLY FROM 'C:\temp\SunClr.dll' WITH PERMISSION_SET=EXTERNAL_ACCESS
GO

Utwórz funkcje, procedury itp. odpowiadające metodom z klasy zaimplementowanej w .NET i sprawdź ich działanie:

CREATE FUNCTION ListFiles(@dirPath NVARCHAR(MAX))
RETURNS TABLE(Name NVARCHAR(MAX), LastWriteTime DATETIME, Type NVARCHAR(MAX))
AS
EXTERNAL NAME Sun_ASSEMBLY.[SunClr_Namespace.SunClr_Class].ListFiles
GO

SELECT * FROM ListFiles('c:\windows')


CREATE FUNCTION ListDirs(@dirPath NVARCHAR(MAX))
RETURNS TABLE(Name NVARCHAR(MAX), LastWriteTime DATETIME, Type NVARCHAR(MAX))
AS
EXTERNAL NAME Sun_ASSEMBLY.[SunClr_Namespace.SunClr_Class].ListDirs
GO

SELECT * FROM ListDirs('c:\windows')

Pisząc artykuł korzystałem z:

Deploying SQL CLR assembly using Asymmetric key

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-asymmetric-key-transact-sql?view=sql-server-2017

https://docs.microsoft.com/pl-pl/dotnet/framework/app-domains/how-to-sign-an-assembly-with-a-strong-name

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/35b8bce5-8ea9-4fd4-aa87-1764a2071e92/long-lasting-pfx-file-for-sqlclr-externalaccess-signing?forum=sqlnetfx

https://docs.microsoft.com/en-us/dotnet/api/system.io.directoryinfo?view=netframework-4.7.2

https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/common-language-runtime-integration-overview?view=sql-server-2017