CLR im SQL Server nutzen (Teil 2)
Posted by sprinter | Filed under Coding
Der erste Artikel dieser Reihe kratzte nur an der Oberfläche und inzwischen sind auch wieder Neuerungen in diesem Bereich hinzu gekommen. In diesem Teil der Serie beschäftigen wir uns mit ein paar Besonderheiten bei der Arbeit mit SQL CLR gerade im Hinblick auf Sicherheit und Konfiguration.
Teil 1
Was hat sich getan?
Seit unserem letzten Artikel ist ja bereits ein neuer SQL-Server (2008 R2) und ein neues Visual Studio (2010) heraus gekommen. Wichtig hierbei ist zunächst erstmal nur, dass der SQL-Server nach wie vor maximal bis .NET Framework 3.5 mitspielt. Beim Anlegen der VS-Projekte sollte man also aufpassen, dass diese Framework-Version als Ziel angegeben wird.
In einem aktuellen Visual Studio fällt es außerdem teils schwer, sich für die richtige Projektvorlage zu entscheiden, da im Bereich “Datenbank” nun wesentlich mehr Vielfalt herrscht:
Abb. 1: Screenshot Projekt-Vorlagen
Der richtige Projekttyp heißt nun etwas holprig “Visual C# SQL CLR-Datenbankprojekt”. Die vielen neuen Projekttypen sind hauptsächlich dazu da, dass man als stolzer Inhaber eines Visual Studio nun kein SQL Server Management Studio mehr braucht, um seine Datenbank zu erstellen.
So gesehen, hat sich also mit anderen Worten erstmal nicht viel verändert, da 3.5 immer noch unser Basis-Framework ist. Mit Denali (SQL-Server-Update) wird sich womöglich mehr ändern.
Nicht alles ist so einfach, wie ein Hash!
Im ersten Teil haben wir eine Hash-Funktion erstellt. Das ist zwar für einen ersten Einstieg sehr hilfreich, hinkt aber deshalb ein wenig, weil wie hier keine Sicherheits-Features tangieren. Das aber ist wesentlich, um ein paar Stolpersteine zu umschiffen.
Unser neues Beispiel soll etwas tiefer in die Materie gehen. Wir möchten in eine Funktion erstellen, die es erlaubt, durch Angabe eines Dateipfades den Inhalt der Datei als Binärdaten auszulesen. Das kann sehr nützlich sein, wenn man z.B. varbinary-Felder mit Daten versorgen möchte.
Das Neue daran ist nun, dass der SQL-Server, um solche Funktionen durchführen zu können, erhöhte Rechte benötigt. Darauf müssen wir nun Rücksicht nehmen.
Sicherheits-Stufen
SQL CLR kann in 3 Sicherheitsstufen laufen:
- Sicher (SAFE)
- Extern (EXTERNAL_ACCESS)
- Unicher (UNSAFE)
Die Standard-Sicherheitsstufe, die durch das Visual Studio vorgewählt wird, ist Sicher. Will man die Sicherheitsstufe für das eigene Projekt ändern, kann man dies in den Eigenschaften des Projektes unter “Datenbank” erledigen:
Abb. 2: Sicherheitsstufe ändern
Dies sorgt dafür, dass VS beim Bereitstellen bzw. Debuggen die richtige Stufe übergibt. Wird nämlich unser Projekt bereit gestellt, führt VS letztlich Folgendes TSQL auf dem SQL-Server aus:
CREATE ASSEMBLY {ASSEMBLYNAME} from '{ASSEMBLYPFAD}' WITH PERMISSION_SET = SAFE|EXTERNAL_ACCESS|UNSAFE
Will man übrigens einen Überblick über alle installierten CLR-Assemblies einer SQL-Server-Instanz haben, bietet sich folgendes an:
SELECT * FROM SYS.assemblies;
Aber zurück zur Sicherheit: Damit man z.B. auf externe Ressourcen, wie Dateien im Dateisystem überhaupt zugreifen darf, muss mindestens “EXTERNAL_ACCESS” definiert sein. Doch das reicht nicht!
Versucht man, eine so manipulierte Projektmappe auf dem SQL-Server bereit zu stellen, wird es eine Fehlermeldung geben. Diese hat 2 Gründe:
- Unsere Assembly ist nicht signiert.
- Der Datenbank fehlt das TRUSTWORTHY-Recht.
Die erste der beiden Bedingungen ist einem gestandenen .NET-Entwickler noch relativ eingängig. EXTERNAL_ACCESS kann potenziell Schaden auf dem SQL-Server anrichten und somit ist klar, warum der SQL-Server hier nach einer signierten Assembly verlangt. Eine normale SNK-Signierung (ohne Kennwort) reicht ünrigens aus.
Den zweiten Grund für unser Scheitern werden nur SQL-DBAs logisch finden. Man muss halt wissen, dass jede SQL-Server-Datenbank ein TRUSTWORTHY-Attribute besitzt, das explizit auf “ON” gesetzt werden muss:
ALTER DATABASE {DBNAME} SET TRUSTWORTHY ON
Um dies zu tun, muss man in der sysadmin-Rolle sein. Dies trifft ebenfalls zu, wenn man Listing 1 mit einem anderen Level als SAFE ausführen möchte.
Das neue Sample
So gerüstet können wir uns nun an unser neues Beispiel wagen. Hier der Code einer CLR-Skalar-Funktion, die unsere Anforderungen erfüllt:
public partial class UserDefinedFunctions
{
/// <summary>
/// Converts the content of a file into a byte-array which can by filled into a varbinary-column.
/// </summary>
/// <remarks>
/// <para>
/// For using this, you have to ensure, that following settings where made:
/// </para>
/// <list type="bullet">
/// <item>SP_CONFIGURE 'clr enable', 1</item>
/// <item>RECONFIGURE</item>
/// <item>ALTER DATABASE {YOURDBNAME} SET TRUSTWORTHY ON</item>
/// </list>
/// <para>This assembly has to be signed and in properties there has to by security-level "External".</para>
/// </remarks>
/// <param name="imagePath">The complete URI of the file in the local file system.</param>
/// <returns>The contents as a SqlBytes-type.</returns>
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBytes GetImageBytes(SqlString imagePath)
{
SqlBytes retVal = null;
string fileName = imagePath.ToString();
if (!File.Exists(fileName))
throw new FileNotFoundException("File not found!", fileName);
try
{
retVal = new SqlBytes(File.ReadAllBytes(fileName));
}
catch
{
throw;
}
return retVal;
}
};
Hier ist eigentlich nichts Spannendes versteckt. Einzig der SQL-Datentyp SqlBytes könnte von Interesse sein, da es hier einen kleinen Stolperstein gibt. Wer sich die verfügbaren Datentypen ansieht, wird schnell SqlBinary finden und womöglich meinen, dieser wäre für Felder vom Typ varbinary gedacht. Letztere werden oft verwendet, um Bilder zu speichern, da sie mit der Größenangabe “max” wesentlich flexibler, als binary sind, das auf 8000 Bytes beschränkt ist. SqlBinary ist jedoch für binary gedacht, während SqlBytes eine Ansammlung von Bytes meint, die beliebig groß sein kann (im Rahmen des SQL-Servers natürlich). Eine vollständige Liste der Typ-Mappings findet man hier.
Der Rest ist offensichtlicher .NET-Code und wir können die Solution nun veröffentlichen. Wichtig hierbei ist eigentlich nur, dass die oben beschriebenen Vorbedingungen auf SQL-Server- und Visual-Studio-Seite alle erfüllt sind. Im Visual Studio kann man nun über den Menupunkt “Erstellen” und dort “Projekt veröffentlichen” die Übertragung auf die zu Beginn ausgewählte SQL-Verbindung starten.
Die Nutzung ist auch hier denkbar einfach:
SP_CONFIGURE "clr_enable", 1
RECONFIGURE
ALTER DATABASE {DBNAME} SET TRUSTWORTHY ON
GO
USE {DBNAME}
GO
CREATE TABLE [Pictures]
([ImageData] [varbinary](max) NULL)
ON [PRIMARY]
GO
INSERT INTO Pictures (ImageData) VALUES (GetImageBytes('C:\temp\test.jpg'));
Ausblick
In einem Folge-Artikel möchte ich Performance-Gesichtspunkte der Nutzung von .NET CLR im SQL-Server beleuchten.
Dezember 20th, 2011 at 21:39
[...] Teil 2 [...]