Für die meisten Anforderungen bietet der SQL Server genug Funktionalität, um diese erfüllen zu können. Hin und wieder kann es aber notwendig sein, dem SQL Server durch eigene Programmierungen etwas unter die Arme zu greifen. Um dies tun zu können, bietet SQL Server eine Schnittstelle, um selbst erstellte .NET-Funktionalitäten dem SQL Server zur Verfügung zu stellen. Man spricht hier von der CLR-Integration, wobei CLR ‚Common Language Runtime‘ bedeutet und einem bestimmten Layer in der .NET-Programmierung entspricht. CLR-Programme ersetzen in keinem Fall das SQL Server-interne T-SQL. Vielmehr werden die Programme integriert. So können folgende Bestandteile des T-SQL durch CLR-Programme erstellt werden:

• Stored procedures
• Trigger (DML und DDL)
• User defined functions
• Aggregate

Es soll in diesem Blogbeitrag gar nicht um die Erstellung solcher Assemblys gehen, sondern lediglich um die Integration dieser.

Bei der Integration wird das CLR-Programm UND alle anderen Komponenten, von denen das CLR-Programm möglicherweise abhängig ist, in der Datenbank gespeichert. Beim Aufruf eines Assemblys gibt also nie einen Zugriff auf das Dateisystem des Servers. Der SQL Server fungiert komplett als Host der integrierten Anwendung.

Integrierte Anwendungen sind im Management Studio unter der entsprechenden Datenbank in Programmierbarkeit\Assemblys zu sehen und können dann in stored procs oder udf’s (user defined functions) oder wie auch immer benutzt werden.

Bevor es aber soweit ist, müssen ein paar Überlegungen und Vorbereitungen getroffen werden.

CLR enable

Erster Schritt ist dabei, der Datenbank das Arbeiten mit CLR zu ermöglichen. Dafür muss die Option ‚clr enabled‘ auf 1 gestellt werden:

sp_configure 'clr enabled', 1;
reconfigure;

Mit sp_configure kann man das Ergebnis nochmals überprüfen.

Bei vielen Administratoren gibt es noch erhebliche Sicherheitsbedenken gegenüber diesem Schritt. Die Sicherheit an dieser Stelle im Auge zu behalten ist äußerst wichtig, wie wir aber noch sehen werden, ist auch mit dem Einschalten der CLR-Integrität eine hohes Sicherheitsniveau möglich, auf jeden Fall ein höheres als z.B. beim Einschalten der xp_cmdshell-Funktionalität, welche oft als Alternative zur CLR-Integrität angesehen wird.

Permission Sets

Beim Einbinden einer neuen Assembly in eine SQL Server Datenbank wird das Assembly in eine von 3 unterschiedlichen Permission Sets eingeordnet: SAFE, EXTERNALACCESS oder UNSAFE. Vor der Ausführung des Assemblys wird geprüft ob die CAS-Berechtigung (Code Access Security des Betriebssystems, oder die Frage: Wer darf WAS tun? (nicht womit)), die dafür notwendig sind, auch gegeben sind. Dabei bedeuten die Permission Sets folgendes:

SAVE: Das Assembly verwendet nur Ressourcne innerhalb Datenbank, in welche es eingebunden ist. Ein Zugriff auf externe Ressourcen ist dem Assembly nicht erlaubt.

Konkret sind folgende CAS-Berechtigungen gestattet:

• Security Permission
  Zugriff auf managed code

• SqlPermission
  Zugriff auf eigene Datenbank mit der Verbindungszeichenfolge

Context connection=true

EXTERNAL ACCESS: Der externe Zugriff erlaubt der Assembly auch Zugriff auf Resourcen außerhalb der Datenbank, wie z.B. das Dateisystem oder das Netzwerk.

Folgende CAS-Berechtigungen sind hier enthalten:

• DistributedTransactionPermission
  Verteilte Anwendungen sind erlaubt.

• DNSPermission
  Es dürfen Informationen vom DNS-Server abgerufen werden.

• EnvironmentPermission
  Umgebungsvariablen des aktuellen Users und des Systems können abgerufen werden.

• EventLogPermission
  Erzeugen einer Ereignisquelle, existierende logs lesen, löschen von Ereignisquellen und -logs, Löschen des Inhalts einen logs

• FileIOPermission
  Zugriff auf Dateien und Ordner ist erlaubt.

• KeyContainerPermission
  Zugriff auf Schlüsselcontainer ist erlaubt.

• NetworkInformationPermission
  Absetzen von pings ist erlaubt.

• RegistryPermission
  Erlaubt Lesen von Registry-Einträgen.

• SecurityPermission
  Assertion, ControlPrincipal, Execution, SerializationFormatter
• SmtpPermission
  Verbindungsaufbau über smtp (port 25) erlaubt.
• SocketPermission
  Verbindungsaufbau (alle ports, alle protokolle) ist erlaubt.

• SqlClientPermission
  Vollständiger Zugriff auf Datenbanken ist erlaubt (Vorrausetzung ist natürlich eine entsprechende Verbindungszeichenfolge)

• StorePermission
  Zugriff auf X.509-Zertifikate ist erlaubt.

• WebPermission
  Zugriff auf Webseiten ist gestattet.

UNSAFE:         Diese Assemblys sind von der CAS aus nicht eingeschränkt, es gibt keine CAS-Prüfungen.

Vielen Assemblys genügen sicher die Möglichkeiten der permission sets SAFE und EXTERNAL ACCESS. Jedoch ist auch die letzte Sicherheitsstufe ‚UNSAFE‘ in keiner Weise als wirklich ‚unsicher‘. Erstens ist hoffentlich vorher genau bekannt, was mit der Assembly alles angestellt werden kann (d.h. die Funktionalitäten sind beschränkt, anders als z.B. bei ‚enable xp_cmdshell‘!) und außerdem ist dies nur eine von mehreren Sicherheitsstufen, welche ein Assembly passieren muss. Hier geht es nur darum, was ein Assembly tun darf.

Es kann aber davon unabhängig auch noch festgelegt werden, welche SQL-Objekte ein Assembly überhaupt verwenden darf.

Wenn Ihr Assembly den Anforderungen des permission sets ‚SAFE‘ genügt, sind die Betrachtungen zum Thema Sicherheit an dieser Stelle beendet. Sie können direkt über Los gehen und ihr Assembly einbinden. Leider sind es jedoch die Funktionalitäten, die erst EXTERNAL_ACCESS oder gar UNSAFE erlauben, die häufig der Grund für CLR-Code sind. Wenn z.B. im CLR-Code auf die Analysis Services von SQL Server zugegriffen werden soll, wird die Assembly im permission set ‚UNSAFE‘ landen.

Thrustworthy

Um auch Assemblys der höheren Berechtigungsebenen in den SQL Server zu laden, ist die einfachste Möglichkeit, die Datenbank, in die das Assembly geladen werden soll, auf Thrustworthy = ON zu stellen:

Alter Database <DATABASE> SET TRUSTWORTHY ON;

In der sys.databases im Feld ‚IsTrustworthyOn‘ kann der aktuelle Status der Option für die einzelnen Datenbanken eingesehen werden.

Select name, is_trustworthy_on
from sys.databases

Diese Vorgehensweise ist jedoch nur in Entwicklungsumgebungen und Testumgebungen für das Assembly empfehlenswert. Da hier die CAS-Prüfung komplett ausgeschaltet wird, reduziert sich die Sicherheit der Datenbank deutlich. Von Microsoft wird daher NICHT empfohlen, diese Option auf Produktivserver einzustellen.

Signieren der Assembly

Assemblys in den permission sets ‚EXTERNAL ACCESS‘ und ‚UNSAFE‘ müssen bei der Erstellung signiert werden. Da es hier nicht um die Erstellung der Assemblys geht, unterstelle ich jetzt, dass das gewünschte Assembly entsprechend vorbereitet ist.

Das Signieren eines Assembly besteht aus folgenden Schritten:

• Anlegen eines asymmetrischen Schlüssels mit Hilfe der Assembly-Datei
• Erzeugen einer Anmeldung für die Assembly
• Anmeldung bekommt Berechtigung EXTERNAL ACCESS ASSEMBLY

Der asymmetrische Schlüssel kann nicht im Management Studio erstellt werden. Diese ist eine der wenigen Aufgaben, für die unbedingt ein sql-script notwendig ist:

CREATE ASYMMETRIC KEY DMM_Tools_RKEY FROM

EXECUTABLE FILE = 'C:\Test\DMM_Tools_R\DMM_Tools_R\bin\Debug\DMM_Tools_R.dll';

Nach Ausführen dieses scripts sollte jedoch der Schlüssel im Management Studio zu sehen sein:

Der nächste Schritt ist das Anlegen eines Logins auf Basis dieses Schlüssels. Dies kann auch im Management Studio erfolgen, hier soll aber der entsprechende sql-code angegeben werden:

CREATE LOGIN DMM_Tools_RLogin
FROM ASYMMETRIC KEY DMM_Tools_RKEY;

Unter <SERVER>/Sicherheit/Anmeldungen im Management Studio sollte jetzt die neue Anmeldung zu sehen sein.

Ein letzter Schritt ist noch notwendig: Das Erteilen der Berechtigung EXTERNAL ACCESS ASSEMBLY bzw. UNSAFE ASSEMBLY für diese Anmeldung:

GRANT UNSAFE ASSEMBLY to DMM_Tools_RLogin;

Das war‘s auch schon. Jetzt kann das Assembly eingebunden werden, auch wenn es in den permission sets ‚EXTERNAL ACCESS‘ oder ‚UNSAFE‘ enthalten ist.

Einbinden eines Assemblys

Egal ob SAFE, UNSAFE oder EXTERNAL ACCESS, der nächste Schritt ist das Einbinden des Assemblys:

Create ASSEMBLY DMM_Tools_R

FROM 'C:\Test\DMM_Tools_R\DMM_Tools_R\bin\Debug\DMM_Tools_R.dll'

with permission_set = unsafe

…und das Erstellen von neuen SQL-Objekten, welche den Code der Assembly nutzen:

Create Function GetASInfo(@t nvarchar(20), @d nvarchar(20)) returns nvarchar(max)

as External NAME DMM_Tools_R.GetAS_Info.DISCOVER_XML_METATDATA_FULL

Das Ausführen der Funktion GetASInfo gibt z.B. ein XML zurück, das eine Analysis-Services-Database vollständig beschreibt. Dieses SQL-Script gibt z.B. alle Dimensionen der angegeben AS-Datenbank zurück:

Declare @xml as xml;

Set @xml = (Select Cast(dbo.GetASInfo('localhost','Chair2008') as xml));

WITH XMLNamespaces
(DEFAULT 'http://schemas.microsoft.com/analysisservices/2003/engine')
Select tab.col.value('text()[1]', 'NVARCHAR(50)') as Dimension
from @xml.nodes('/Database/Dimensions/Dimension/Name') as tab(col)

Ergebnis: