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 alsUNSAFE
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 aufON
gestellt und die Datenbank besitzt einen Owner mitUNSAFE 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!