SQL Server 2005 Verschlüsselung, asp.net und gespeicherte Verfahren

Question

Ich muss eine Webanwendung mit SQL Server 2005, asp.net und ado.net schreiben. Ein Großteil der in dieser Anwendung gespeicherten Benutzerdaten muss verschlüsselt werden (siehe HIPAA).

In der Vergangenheit habe ich bei Projekten, die eine Verschlüsselung erforderten, die Ver- und Entschlüsselung im Anwendungscode vorgenommen. Dabei handelte es sich jedoch in der Regel um die Verschlüsselung von Kennwörtern oder Kreditkarteninformationen, also nur um eine Handvoll Spalten in einigen Tabellen. Für diese Anwendung müssen weitaus mehr Spalten in mehreren Tabellen verschlüsselt werden, so dass ich vermute, dass eine Verlagerung der Verschlüsselungsverantwortung in die Datenschicht leistungsfähiger sein wird, insbesondere angesichts der nativen Unterstützung mehrerer Verschlüsselungstypen durch SQL Server 2005. (Ich könnte vom Gegenteil überzeugt werden, wenn jemand echte, empirische Beweise hat).

Ich habe BOL konsultiert, und ich bin ziemlich gut im Umgang mit Google. Ich möchte also keine Links zu Online-Artikeln oder MSDN-Dokumentation (wahrscheinlich habe ich sie bereits gelesen).

Ein Ansatz, den ich bis jetzt verstanden habe, ist die Verwendung eines symmetrischen Schlüssels, der mit einem Zertifikat geöffnet wird.

Die einmaligen Einrichtungsschritte sind also (theoretisch von einem DBA durchgeführt):

1. Einen Hauptschlüssel erstellen
2. Sichern Sie den Hauptschlüssel in einer Datei, brennen Sie ihn auf eine CD und lagern Sie ihn außer Haus.
3. Öffnen Sie den Master Key und erstellen Sie ein Zertifikat.
4. Sichern Sie das Zertifikat in einer Datei, brennen Sie es auf eine CD und lagern Sie es außer Haus.
5. Erstellen Sie den symmetrischen Schlüssel mit dem Verschlüsselungsalgorithmus Ihrer Wahl unter Verwendung des Zertifikats.

Wenn dann eine gespeicherte Prozedur (oder ein menschlicher Benutzer über Management Studio) auf verschlüsselte Daten zugreifen muss, müssen Sie zuerst den symmetrischen Schlüssel öffnen, alle Tsql-Anweisungen oder Batches ausführen und dann den symmetrischen Schlüssel schließen.

Für die Asp.net-Anwendung und in meinem Fall für die Datenzugriffsschicht des Anwendungscodes ist die Datenverschlüsselung dann völlig transparent.

Meine Fragen lauten also:

1. Möchte ich öffnen, tsql-Anweisungen/Batches ausführen und dann den symmetrischen Schlüssel alle innerhalb der Sproc schließen? Die Gefahr, die ich sehe, ist, was, wenn etwas mit der tsql-Ausführung schief geht, und Code sproc Ausführung nie die Anweisung erreicht, die den Schlüssel schließt. Ich nehme an, dass dies bedeutet, dass der Schlüssel offen bleibt, bis Sql die SPID beendet, auf der Sproc ausgeführt wurde.

2. Sollte ich stattdessen in Erwägung ziehen, für jede auszuführende Prozedur drei Datenbankaufrufe zu tätigen (nur wenn eine Verschlüsselung erforderlich ist)? Ein Datenbankaufruf, um den Schlüssel zu öffnen, ein zweiter Aufruf, um die Sproc auszuführen, und ein dritter Aufruf, um den Schlüssel zu schließen. (Jeder Aufruf wird in eine eigene Try-Catch-Schleife eingeschlossen, um die Wahrscheinlichkeit zu erhöhen, dass ein offener Schlüssel letztendlich geschlossen wird).

3. Alle Überlegungen sollte ich Client-seitige Transaktionen verwenden müssen (d.h. mein Code ist der Client, und initiiert eine Transaktion, führt mehrere Sprocs, und dann die Transaktion unter der Annahme von Erfolg festschreiben)?

Answer 1

1) Versuchen Sie, TRY..CATCH in SQL 2005 zu verwenden. Leider gibt es kein FINALLY, so dass Sie sowohl die Erfolgs- als auch die Fehlerfälle einzeln behandeln müssen.

2) Nicht erforderlich, wenn (1) die Bereinigung übernimmt.

3) Es gibt keinen wirklichen Unterschied zwischen Client- und Server-Transaktionen bei SQL Server. Connection.BeginTransaction() führt mehr oder weniger "BEGIN TRANSACTION" auf dem Server aus (und System.Transactions/TransactionScope tut dasselbe, bis es zu einer verteilten Transaktion befördert wird). Was die Probleme mit dem mehrmaligen Öffnen/Schließen des Schlüssels innerhalb einer Transaktion betrifft, so sind mir keine Probleme bekannt, die zu beachten wären.

Answer 2

Ich bin ein großer Fan von Option 3.

Stellen Sie sich vor, Sie würden eine Transaktionsinfrastruktur einrichten, egal wo:

1. Jedes Mal, wenn ein Aufruf des Datenspeichers anstand und noch keine Transaktion gestartet worden war, wurde eine solche angelegt.
2. Wenn bereits eine Transaktion vorhanden ist, greifen die Aufrufe an den Datenspeicher auf diese Transaktion zurück. Dies ist oft nützlich für Geschäftsregeln, die durch Speicher-/Gehen-zur-Datenbank-Ereignisse ausgelöst werden. BEISPIEL. Wenn Sie eine Regel hätten, die besagt, dass Sie jedes Mal, wenn Sie ein Widget verkaufen, eine WidgetAudit-Tabelle aktualisieren müssen, würden Sie wahrscheinlich den Aufruf zum Einfügen des Widget-Audits in dieselbe Transaktion verpacken wollen wie die Transaktion, die dem Datenspeicher mitteilt, dass ein Widget verkauft wurde.
3. Sobald der ursprüngliche Aufrufer des Datenspeichers (aus Schritt 1) beendet ist, wird die Transaktion festgeschrieben/zurückgenommen, was sich auf alle Datenbankaktionen auswirkt, die während des Aufrufs stattgefunden haben (unter Verwendung eines try/catch/finally).

Sobald diese Art von Transaktionen erstellt wurde, ist es einfach, einen offenen Schlüssel am Anfang (wenn die Transaktion eröffnet wird) und einen geschlossenen Schlüssel am Ende (kurz bevor die Transaktion endet) anzuhängen. Der "Aufruf" des Datenspeichers ist nicht annähernd so teuer wie das Öffnen einer Verbindung zur Datenbank. Es sind wirklich Dinge wie SQLConnection.Open(), die Ressourcen verbrauchen (selbst wenn ADO.NET sie für Sie poolt).

Wenn Sie ein Beispiel für diese Art von Codes suchen, würde ich einen Blick auf NetTiers werfen. Es bietet eine recht elegante Lösung für die gerade beschriebenen Transaktionen (vorausgesetzt, Sie haben nicht schon etwas im Sinn).

Nur 2 Cents. Viel Glück!

Answer 3

1. können Sie @@error verwenden, um festzustellen, ob beim Aufruf eines Sproc in SQL Fehler aufgetreten sind.

2. Nein zu kompliziert.

3. Sie können, aber ich ziehe es vor, Transaktionen in SQL Server selbst zu verwenden.