Wenn du jemals mit der STRING_AGG-Funktion in SQL gearbeitet hast, ist dir vielleicht aufgefallen, dass sie keine direkte Unterstützung für die DISTINCT-Option bietet. Das bedeutet, dass du keine Möglichkeit hast, doppelte Werte innerhalb der zusammengefassten Zeichenkette einfach zu eliminieren. Zum Glück gibt es dafür eine elegante Lösung: die Installation der GROUP_CONCAT-Funktion von Orlando Colamatteo. (siehe dazu hier)

Was ist GROUP_CONCAT?

GROUP_CONCAT ist eine benutzerdefinierte Funktion, die in SQL Server implementiert werden kann, um eine ähnliche Funktionalität wie die STRING_AGG-Funktion bereitzustellen, jedoch mit zusätzlicher Flexibilität – einschließlich der Möglichkeit, doppelte Werte zu eliminieren. Im Wesentlichen ermöglicht GROUP_CONCAT, dass du eine Gruppe von Werten in einer Zeichenkette zusammenführen kannst, wobei du entscheiden kannst, ob du Duplikate einschließen möchtest oder nicht. Dies ist besonders nützlich, wenn du z. B. eine Liste von eindeutigen Werten aus einer Spalte in einer einzigen Zeichenkette zusammenfassen möchtest.

Die Installation dieser Funktion ist recht einfach und der Installationsskript kann hier heruntergeladen werden.

Installation und mögliche Fehlermeldungen

Während der Installation kann es jedoch zu einer Fehlermeldung kommen, die in etwa wie folgt lautet:

CREATE or ALTER ASSEMBLY for assembly 'GroupConcat' with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly.

Diese Fehlermeldung tritt auf, weil SQL Server seit Version 2017 die CLR Strict Security standardmäßig aktiviert hat. Diese Sicherheitsfunktion soll verhindern, dass unsichere oder externe Assemblies ohne entsprechende Sicherheitsmaßnahmen ausgeführt werden.

Was bedeutet diese Fehlermeldung?

Die Meldung besagt im Wesentlichen, dass die SQL Server-Instanz so konfiguriert ist, dass nur signierte Assemblies mit sicherer oder externer Zugriffsebenen installiert werden können. Dies ist eine Sicherheitsmaßnahme, um sicherzustellen, dass keine bösartigen oder unsicheren Assemblys in der Datenbank ausgeführt werden.

Lösung des Problems

Um die GROUP_CONCAT-Funktion dennoch zu installieren, hast du zwei Möglichkeiten:

  1. Assembly signieren: Du kannst die Assembly mit einem Zertifikat oder einem asymmetrischen Schlüssel signieren. Dieses Zertifikat oder der Schlüssel muss mit einem Login verknüpft sein, das die Berechtigung UNSAFE ASSEMBLY hat. Dieser Ansatz ist der sicherste und wird von Microsoft empfohlen.
  2. Assembly als vertrauenswürdig hinzufügen: Alternativ kannst du die Assembly manuell als vertrauenswürdig markieren, indem du den folgenden Befehl ausführst:
EXEC sp_add_trusted_assembly 'Assembly_Binary_Here';

Dieser Ansatz ist einfacher, sollte jedoch mit Vorsicht verwendet werden, da er potenziell unsichere Assemblies zulässt. Näheres dazu hier.

    Assembly signieren

    Dei folgende Anleitung beinhaltet das

    Signieren der Assembly, indem ein asymmetrischer Schlüssel (sqlKey.snk) verwendet wird. Dieser Schlüssel wird dann in SQL Server importiert und mit einem Login verknüpft, das die notwendige Berechtigung (UNSAFE ASSEMBLY) erhält, um die Assembly auszuführen.

    Schritte zur Signierung:
    1. Erstellung eines asymmetrischen Schlüssels aus der SNK-Datei:
      Dieser Schritt importiert den Schlüssel, der zur Signierung der Assembly verwendet wurde, in SQL Server.
    2. Verknüpfung des Schlüssels mit einem SQL Server-Login:
      Dies stellt sicher, dass nur dieser spezielle Schlüssel (und damit die damit signierte Assembly) die notwendigen Berechtigungen erhält.
    3. Erteilung der UNSAFE ASSEMBLY-Berechtigung:
      Diese Berechtigung erlaubt es der signierten Assembly, unsichere oder erweiterte Aktionen auszuführen.

    Der gesamte Prozess stellt sicher, dass die Assembly von einer vertrauenswürdigen Quelle stammt und sicher in SQL Server ausgeführt werden kann. Das Signieren der Assembly ist ein gängiger Ansatz, um die Sicherheitsanforderungen von SQL Server zu erfüllen, insbesondere wenn “CLR strict security” aktiviert ist.

    (1) Erstellen eines Master-Schlüssels
    • Was wird gemacht?
      Zuerst wird ein Master-Schlüssel in der master-Datenbank erstellt. Dieser Schlüssel wird verwendet, um die asymmetrische Schlüsseldatei (snk) zu schützen, die später hinzugefügt wird.
    • SQL-Befehl:
    USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My@wesom3Pwd';
    • Warum ist das notwendig?
      Der Master-Schlüssel dient dazu, kryptografische Objekte wie asymmetrische Schlüssel oder Zertifikate sicher zu speichern und zu schützen. Wenn bereits ein Master-Schlüssel vorhanden ist, kann dieser Schritt übersprungen werden.
    (2) Erstellen eines asymmetrischen Schlüssels aus einer SNK-Datei
    • Was wird gemacht?
      Ein asymmetrischer Schlüssel wird aus einer vorhandenen .snk-Datei erstellt, die Teil des Projekts ist und zur Signierung der Assembly verwendet wurde.
    • SQL-Befehl:
    CREATE ASYMMETRIC KEY clrKey FROM FILE = '\\your\path\to\sqlKey.snk';
    • Warum ist das notwendig?
      Der asymmetrische Schlüssel, der aus der .snk-Datei erstellt wird, ist erforderlich, um eine sichere Beziehung zwischen der Assembly und SQL Server zu schaffen. Dies stellt sicher, dass die Assembly von einer vertrauenswürdigen Quelle stammt.
    (3) Erstellen eines Logins, das mit dem asymmetrischen Schlüssel verknüpft ist
    • Was wird gemacht?
      Ein Login wird erstellt, das mit dem zuvor erstellten asymmetrischen Schlüssel (clrKey) verknüpft ist.
    • SQL-Befehl:
    CREATE LOGIN clrLogin FROM ASYMMETRIC KEY clrKey;
    • Warum ist das notwendig?
      Dieses Login wird verwendet, um der Assembly die notwendigen Berechtigungen zu erteilen, damit sie in SQL Server ausgeführt werden kann.
    (4) Gewähren der Berechtigung “Unsafe Assembly” für das Login
    • Was wird gemacht?
      Dem Login wird die Berechtigung “Unsafe Assembly” gewährt, wodurch es erlaubt wird, unsichere Assemblys auszuführen.
    • SQL-Befehl:
    GRANT UNSAFE ASSEMBLY TO clrLogin;
    • Warum ist das notwendig?
      Diese Berechtigung ermöglicht es der Assembly, Aktionen auszuführen, die als potenziell unsicher eingestuft werden, z.B. das Ausführen von Code außerhalb der SQL Server-Umgebung. Der Name dieser Berechtigung könnte irreführend wirken, ist aber notwendig, um die Assembly korrekt zu laden.
    (5) Erstellen eines Benutzers in der Ziel-Datenbank
    • Was wird gemacht?
      In der Datenbank, in der die Assembly verwendet werden soll, wird ein Benutzer erstellt, der mit dem zuvor erstellten Login (clrLogin) verknüpft ist.
    • SQL-Befehl:
    USE YourDb; CREATE USER clrLogin FOR LOGIN clrLogin;
    • Warum ist das notwendig?
      Der Benutzer in der Ziel-Datenbank ermöglicht es, dass das Login die entsprechenden Rechte in dieser spezifischen Datenbank hat, um die Assembly zu verwenden.
    (6) Erstellen der Assembly
    • Was wird gemacht?
      Schließlich wird die eigentliche Assembly entweder direkt aus einer DLL-Datei oder aus einer varbinary-Darstellung erstellt.
    • SQL-Befehl (direkt aus DLL)
    CREATE ASSEMBLY [GroupConcat] AUTHORIZATION [dbo] FROM '\\your\path\to\GroupConcat.dll' WITH PERMISSION_SET = SAFE;
    • SQL-Befehl (aus varbinary):
    CREATE ASSEMBLY [GroupConcat] AUTHORIZATION [dbo] FROM 0x4D5A90... WITH PERMISSION_SET = SAFE;
    • Warum ist das notwendig?
      Die Assembly muss in die SQL Server-Instanz geladen werden, damit sie verwendet werden kann. Dabei wird angegeben, ob die Assembly als “SAFE” ausgeführt werden kann, was bedeutet, dass sie nur innerhalb der SQL Server-Umgebung arbeitet und keine unsicheren Aktionen durchführt.

    Assembly als vertrauenswürdig hinzufügen

    Wie kommt man zur dll

    Die Zeichenkette 0x4D5A900003000000040... ist eine hexadezimale Darstellung des binären Inhalts einer Datei, in diesem Fall vermutlich einer DLL-Datei. Um diese hexadezimale Darstellung in eine tatsächliche .dll-Datei umzuwandeln, müssen Sie den binären Inhalt wiederherstellen und in eine Datei schreiben. Das geht nicht durch einfaches Kopieren und Einfügen der Zeichenkette. Stattdessen müssen Sie ein Programm verwenden, das die hexadezimale Darstellung in binäre Daten konvertiert und diese Daten in eine .dll-Datei schreibt.

    So konvertieren Sie den hexadezimalen Inhalt in eine DLL-Datei:

    Hier ist ein Beispiel, wie Sie dies mit PowerShell tun können:

    • Speichern des hexadezimalen Strings in eine Datei:Zuerst müssen Sie die hexadezimale Zeichenkette in eine Datei speichern. Sie können dafür PowerShell verwenden.
    • Erstellen der DLL-Datei aus dem hexadezimalen String: Angenommen, Sie haben die gesamte hexadezimale Zeichenkette, dann können Sie den folgenden PowerShell-Code verwenden, um die DLL-Datei zu erstellen: 
      $hexString = "4D5A900003000000040..." $bytes = for ($i = 0; $i -lt $hexString.Length; $i += 2) { [Convert]::ToByte($hexString.Substring($i, 2), 16) } [System.IO.File]::WriteAllBytes("C:\Pfad\zur\GroupConcat.dll", $bytes)


      Erklärung:

      • $hexString: Hier setzen Sie den vollständigen hexadezimalen String ein (ohne “0x” am Anfang).
      • $bytes: Diese Schleife konvertiert die hexadezimale Zeichenkette in ein Byte-Array.
      • WriteAllBytes: Dieser Befehl schreibt das Byte-Array in eine .dll-Datei.
      • Verwendung der generierten DLL: Sobald die DLL-Datei erstellt wurde, können Sie sie mit dem Get-FileHash-Befehl wie folgt verwenden:
      Get-FileHash -Path "C:\Pfad\zur\GroupConcat.dll" -Algorithm SHA512
      • Damit erhalten Sie den Hash-Wert, den Sie in SQL Server verwenden können.

      Zusammenfassung:

      Nein, Sie können den hexadezimalen String nicht direkt in eine .dll kopieren. Sie müssen ihn zuerst in binäre Daten konvertieren und dann in eine Datei schreiben, um eine funktionierende DLL zu erstellen. Mit PowerShell können Sie diese Konvertierung durchführen und anschließend die DLL-Datei verwenden, wie es in der Anleitung beschrieben ist.

      Zusammenfassung

      Diese Schritte konfigurieren die Sicherheitsmechanismen von SQL Server, um sicherzustellen, dass die CLR-Assembly (GroupConcat) ordnungsgemäß geladen und verwendet werden kann. Der Prozess umfasst das Erstellen von Schlüsseln und Logins, das Zuweisen von Berechtigungen und das letztendliche Laden der Assembly in SQL Server. Diese Vorgehensweise ist notwendig, um die strengen Sicherheitsanforderungen von SQL Server zu erfüllen, insbesondere wenn CLR strict security aktiviert ist.

      Fazit

      Obwohl STRING_AGG keine eingebaute DISTINCT-Option bietet, kann die Installation der GROUP_CONCAT-Funktion eine leistungsfähige und flexible Alternative sein. Beachte jedoch die Sicherheitsrichtlinien bei der Installation, um die Integrität und Sicherheit deiner SQL Server-Umgebung zu gewährleisten. Mit diesen Schritten kannst du das Beste aus deinen SQL-Abfragen herausholen, ohne auf Komfort und Sicherheit verzichten zu müssen.

      Related posts:

      Default ThumbnailData Definition Language (DDL) Monitoring eines SQL-Servers Datenbank ohne Daten sichern Default ThumbnailHORIZONTALE PARTITIONIERUNG VON TABELLEN