SQL Server und CLR Assemblies

In manchen Fällen ist es ganz praktisch, Stored Procedures oder Functions für den SQL Server via CLR zur Verfügung zu stellen. Nachdem dies schon ein alter Hut ist, findet man auch zahlreiche Infos zu diesem Thema, aber nicht in einem Artikel.

Verwaltung von Assemblies

Für den Umgang mit Assemblies im SQL Server gibt es doch einiges, was es zu wissen gilt. Nachfolgend die wichtigsten Informationen, damit der erste Start schnell gelingt.

Erstellen von Assemblies

Damit Assemblies im SQL Server verwendet werden können, müssen sie hinzugefügt/registriert werden:

CREATE ASSEMBLY TestAssembly
FROM 'C:\Path\To\Assembly.dll'
WITH PERMISSION_SET = SAFE;

Wichtig ist hierbei das PERMISSION_SET. Hier gibt es drei unterschiedliche Einstellungen:

• SAFE: Das ist die restriktivste Einstellung. Externe Ressourcen (Dateien, Netzwerk, Umgebungsvariablen etc.) dürfen nicht verwendet werden.
• EXTERNAL_ACCESS: Der Zugriff auf einige externe Ressourcen (Dateien, Netzwerk, Umgebungsvariablen, Registry) ist möglich.
• UNSAFE: Uneingeschränkten Zugriff auf externe und interne Ressourcen. Unmanaged Code kann ebenfalls ausgeführt werden.

Werden externe Ressourcen benötigt, ist zusätzlich die TRUSTWORTHY-Einstellung auf ON zu setzen. Hierfür ist ein Benutzer mit der Serverrolle sysadmin notwendig:

ALTER DATABASE test SET TRUSTWORTHY ON;

Ab SQL Server 2017 ändert sich da ein wenig, siehe CLR strict security. Im Grunde wird die Code Access Security (CAS) nicht mehr länger unterstützt. Deswegen kann es auch bei der Einstellung SAFE dazu kommen, dass Zugriff auf externe Ressourcen besteht. Es wurde eine Option clr strict security (siehe sp_configure) eingeführt.

• Disabled: Das bisherige Verhalten wird verwendet
• Enabled: PERMISSION_SET wird ignoriert und Assemblies werden immer als UNSAFE interpretiert. Das ist der Standard für SQL Server 2017

Ist CLR strict security aktiviert, sind folgende Berechtigungen notwendig, damit eine Assembly erstellt werden kann:

• Der Benutzer muss die CREATE ASSEMBLY Berechtigung besitzen
• Die Assemly wurde mit einem asymmetischen Schlüssel signiert, zu dem auch ein entsprechendes Login mit der Berechtigung UNSAFE ASSEMBLY besteht ODER
• die Datenbank-Einstellung TRUSTWORTHY wird auf ON gestellt und die Datenbank besitzt einen Owner mit UNSAFE ASSEMBLY Berechtigung.

Weitere Details sind unter CREATE ASSEMBLY zu finden.

Aktualisierung einer Assembly

Wurde eine Assembly bereits erstellt, kann diese auch aktualisiert werden:

ALTER ASSEMBLY TestAssembly
FROM 'C:\Path\To\Assembly.dll'

Das geht sogar so weit, dass Dateien zur Assembly hinzugefügt werden können:

ALTER ASSEMBLY TestAssembly
ADD FILE FROM 'C:\Path\To\Class.dll'

Auch die Berechtigung kann verändert werden:

ALTER ASSEMBLY TestAssembly WITH PERMISSION_SET = EXTERNAL_ACCESS;

Weitere Informationen gibt es unter ALTER ASSEMBLY.

Löschen einer Assembly

Eine Assembly kann folgendermaßen entfernt werden:

DROP ASSEMBLY TestAssembly

Zu beachten ist, dass keine Funktionalität der Assembly mehr verwendet werden darf, bevor sie gelöscht werden kann.

Die vollständige Dokumentation findet sich unter DROP ASSEMBLY.

Verwenden einer Stored Procedure

Damit eine in der Assembly enthaltene Stored Procedure verwendet werden kann, muss diese am SQL Server noch erstellt werden:

CREATE PROCEDURE dbo.sp_ReadDataFromWebservice @url nvarchar(255)
WITH EXECUTE AS CALLER AS EXTERNAL NAME TestAssembly.StoredProcedures.ReadData;

Im Endeffekt wird hier eine Stored Procedure mit einem Parameter erstellt. Der externe Name ist der volle Namespace inkl. Methoden-Name der CLR Stored Procedure.

Ein Aufruf erfolgt so:

exec sp_ReadDataFromWebservice @url='http://hier.ist-mein.webservice'

Vertiefende Informationen sind unter CREATE PROCEDURE zu finden.

Unterstützendes Tooling

Visual Studio kann uns allerdings unterstützen. So gibt es im Kontextmenü zum CLR-Datenbankprojekt die Möglichkeit Publish Database zu wählen. Damit wird nachfolgender Dialog geöffnet:

Hier können wir uns ein passendes Script generieren oder aber die Änderungen direkt in die gewählte Connection einspielen lassen. Unter Advanced verstecken sich zahlreiche Einstellungen, die Beachtung finden sollten.

Sind mehrere Datenbanken betroffen bietet sich die Arbeit mit Profilen an. Diese können über denselben Dialog verwaltet werden. Ein Profil wird im jeweiligen Projekt als XML-Datei abgespeichert und kann jederzeit geladen und für eine Veröffentlichung genutzt werden.

Fazit

Dieser Beitrag hat die wichtigsten Informationen, um ans Ziel zu kommen, aufgezeigt. Für entsprechende Vertiefungen empfiehlt es sich, den ausgehenden Links zu folgen.

Happy Coding!