2011-06-24

SQL Client konfigurieren

Wie überprüfe ich, mit welchem Protokoll ein Client die Verbindung zum SQL Server aufbauen möchte? Wo kann man das "mal eben" nachsehen? Die Antwort ist (wie fast immer) einfach, wenn man das richtige Werkzeug kennt. In diesem Fall hat das Werkzeug den kryptischen Namen cliconfg.exe.

Auswahl der Protokolle mit cliconfg.exe
Es wird mit verschiedenen Produkten installiert (z. B. SQL Server) und ist in der MDAC 2.1 ab SP2 enthalten. Daher ist es in der Regel auf allen Clients installiert.

Mit cliconfg.exe lassen sich einerseits die Protokolle einstellen, über die der Client Kontakt zum SQL Server aufnimmt. Wenn Sie im Unternehmen ausschließlich TCP/IP einsetzen, dann brauchen Clients erst gar nicht zu versuchen, über das Named Pipes Protokoll eine Verbindung zum SQL Server herzustellen.
Darüber hinaus kann das Programm auch verwendet werden, um Alias-Namen für SQL Server zu erstellen. Das kann nützlich sein, wenn eine Client Anwendung einen bestimmten Servernamen erwartet, dieser sich aber geändert hat. Das folgende Bild zeigt zwei konfigurierte Alias-Namen.

Alias-Namen für Serververbindungen

Der untere Alias zum Beispiel heißt "Testserver" und verweist auf die SQL Server Instanz LEV\INST1. Beim Konfigurieren einer ODBC Verbindung kann nun "Testserver" als Servername verwendet werden, wie das folgende Bild zeigt.

ODBC Verbindung zum SQL Server "Testserver"

Tatsächlich wird die ODBC Verbindung zum SQL Server LEV\INST1 hergestellt. Als Kommunikationsprotokoll verwendet der Client TCP/IP.

Übrigens ist der Alias auch eine Möglichkeit um zusätzlich zum SQL Servernamen auch noch die Portnummer zu hinterlegen. Der Standard-Port ist ja 1433, aber erstens muss das nicht immer so sein und zweitens wird bei benannten Instanzen in der Regel ein anderer Port verwendet. Die eleganteste Lösung hierfür ist der Einsatz des Browser Dienstes (siehe auch die Anmerkung am Ende dieses Artikels ), aber wenn dieser nicht eingesetzt werden kann, dann ist der Client Alias eine mögliche Lösung.

Die mit cliconfg.exe gemachten Einstellungen (Reihenfolge der Protokolle und Alias-Namen) werden übrigens in der Registry gespeichert:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

Links
http://support.microsoft.com/kb/289573/en-us

2011-06-22

Fehler beim Erstellen eines Failover Clusters

Ich wollte in diesem Blog über merkwürdige Dinge berichten - hier ist etwas wirklich Merkwürdiges!
Zum Einrichten eines Failover Clusters mit zwei Knoten startete ich den Cluster Manager auf auf dem ersten Knoten und führte dort den Assistenten zur Validierung aus. Dabei erhielt ich die folgende Meldung:

Unable to determine if you have administrator privileges on server 'servername'. Please ensure sure that the server service and remote registry services are enabled, and that the firewall is properly configured for remote access.

Weil mich Fehlermeldungen in einem Cluster immer besonders beunruhigen und ich auch mit dem Setup nicht weiter kam, ging ich der Sache auf den Grund. Die genannten Dienste liefen beide und auch ein vorübergehendes komplettes Ausschalten der Firewalls beider Knoten brachte keine Änderung

Um es kurz zu machen:
Auf einem der beiden Knoten hatte ich das Protokoll "File and Printer Sharing for Microsoft Networks" deaktiviert.


Das Bild zeigt die Einstellung, mit der es funktioniert. Anscheinend braucht der Assistent zur Validierung eines Clusters dieses Protokoll, um zu ermitteln, ob die Anmeldung, mit der der Assistent ausgeführt wird, über ausreichend Rechte verfügt.

Fazit:
Ein schwer zu findender Fehler, der viel Zeit kosten kann. Die Fehlermeldung ist nicht sehr hilfreich, da sie nicht alle Fehlerursachen auflistet.

2011-06-21

Wie erhält der SQL Server Dienst seine Rechte?

Die Botschaft ist nicht neu aber wichtig: Der SQL Server Dienst sollte mit einem Benutzerkonto ausgeführt werden, das mit minimalen Rechten ausgestattet ist. Um das zu erreichen, erstellen Sie ein Windows Benutzerkonto (lokal oder in der Domäne) und weisen mit Hilfe des Programms SQL Server Configuration Manager (SSCM) dieses Konto dem SQL Server Dienst zu. Das funktioniert einfach und sicher.

Aber was genau macht der Configuration Manager?

Der erfahrene Berater antwortet: "Das kommt drauf an..."
Es kommt auf die SQL Server Version und auf das Betriebssystem an.
  • Fall 1: SQL Server 2005 oder Betriebssystem XP / Windows 2003 Server
  • Fall 2: SQL Server 2008 (und neuer) und Betriebssystem Vista / Windows 2008 (und neuer)
Fall 1
Dieser Fall trifft immer zu, wenn Sie SQL Server 2005 installieren. Er trifft aber auch zu, wenn Sie SQL Server 2008 (und neuer) auf einem "Pre-Vista" Betriebssystem installieren (XP oder Windows 2003 Server).
In diesem Fall werden bei der Installation der SQL Server Instanz Windows Gruppen angelegt, die lange aber sprechende Namen haben, zum Beispiel: SQLServer2005MSSQLUser$AC$SQL2005
In diesem Fall ist AC der Name der Windows Maschine und SQL2005 der Name der SQL Server Instanz. Dieser Windows Gruppe werden alle Berechtigungen erteilt, die der SQL Server Dienst benötigt. Das sind Zugriffe auf Ordner, Dateien und Registry Einträge. Sie können das zum Beispiel sehen, wenn Sie sich die Berechtigungen auf die Datei sqlservr.exe ansehen:
ACL für sqlservr.exe (Fall 1)
Die Windows Gruppe SQLServer2005MSSQLUser$AC$SQL2005 hat auf diese Datei Lese- und Ausführungs-Berechtigung. Wenn Sie mit SSCM das Dienstkonto ändern, dann wird dieses Konto Mitglied der Windows Gruppe SQLServer2005MSSQLUser$AC$SQL2005 und hat somit alle erforderlichen Berechtigungen.
Der technische Ausdruck für die Erteilung der Berechtigungen heißt übrigens Access Control List (ACL). Das obige Bild ist die grafische Darstellung dieser Zugriffsliste für die Datei sqlservr.exe
Das folgende Bild zeigt, dass das Dienstkonto von SQL Server Mitglied in der Gruppe SQLServer2005MSSQLUser$AC$SQL2005 ist.
Mitglieder der Windows Gruppe MSSQLUser (Fall 1)

Fall 2
Etwas anders sieht es aus, wenn Sie SQL Server 2008 (oder neuer) auf einem aktuellen Betriebssystem (ab Vista / Windows Server 2008) installieren. Auch dann erhält die Windows Gruppe die erforderlichen Berechtigungen auf Ordner, Dateien und Registry Einträge:
ACL für sqlservr.exe (Fall 2)
In diesem Fall heißt die Windows Gruppe SQLServerMSSQLUser$LEV$MSSQLSERVER, weil der Windows Server den Namen LEV hat und die SQL Server Instanz die Standardinstanz ist. So weit, so gut. Aber wenn wir nun nachsehen, wer Mitglied dieser Windows Gruppe ist, erleben wir eine Überraschung:
Mitglieder der Windows Gruppe MSSQLUser (Fall 2)
Mitglied dieser Gruppe ist nicht das SQL Server Dienstkonto, sondern der SQL Server Dienst selbst! Man sagt auch, es handelt sich um die Service-SID. SID ist die Abkürzung für Security-ID; das ist die Nummer S-1-5-80-..., die hinter dem Namen NT Service\MSSQLSERVER steht.
Und was bedeutet diese Änderung gegenüber Fall 1?
  • Die Service-SID ändert sich nicht, wenn das Dienstkonto geändert wird. SSCM muss also nicht mehr das Dienstkonto in die Windows Gruppe aufnehmen.
  • Das Dienstkonto bekommt keine Berechtigungen mehr auf die Ordner, Dateien und Registry Einträge der SQL Server Instanz. Die Folge: höhere Sicherheit. Denn selbst wenn nun ein Eindringling das Passwort des Dienstkontos in Erfahrung bringt, erhält er damit keine Zugriffsberechtigungen auf die Dateien der SQL Server Instanz.

Änderungen bei sysadmin
Diese Änderung betrifft auch die Mitglieder in der Serverrolle sysadmin. Beim SQL Server 2005 (genauer: im Fall 1) sind die Anmeldungen der Windows Gruppen MSSQLUser und SQLAgentUser Mitglieder dieser kritischen Rolle. Wer also das Passwort des Dienstkontos von SQL Server oder SQL Server Agent errät, der kann mit der SQL Server Instanz tun und lassen, was er/sie möchte!
Mitglieder der sysadmin-Rolle (Fall 1)
Seit SQL Server 2008 (genauer: im Fall 2) sind die Service-SID von SQL Server und SQL Server Agent Mitglieder dieser Rolle. Eine Angriffsmöglichkeit weniger!
Mitglieder der sysadmin-Rolle (Fall 2)


Links
Dieser Beitrag von Dan Jones hat mir beim Lösen des Rätsels geholfen:
http://blogs.msdn.com/b/dtjones/archive/2010/12/15/changing-service-account-amp-service-account-password.aspx

Was ist Datenbankdatei-Initialisierung?

Wenn Sie große Datenbanken administrieren, haben Sie sicherlich schon mehr als einmal auf den SQL Server gewartet, zum Beispiel beim
  • Erstellen von Datenbanken
  • Vergrößern von Datendateien
  • Hinzufügen neuer Datendateien
  • Wiederherstellen einer Datenbank aus einem Backup
Es gibt ein recht unbekanntes Feature, mit dem Sie diese Wartezeiten deutlich verkürzen können. Dieses hat den Namen Sofortige Dateiinitialisierung (Instant File Initialization). Um einen Eindruck von der Beschleunigung zu bekommen, habe ich auf meinem Notebook eine 20GB große Datenbank angelegt. Hier die Zeiten:
  • ohne sofortige Dateiinitialisierung: 2:10 Minuten
  • mit sofortiger Dateiinitialisierung: 0:02 Minuten
Wie funktioniert es?
Normalerweise füllt SQL Server eine neue Daten- oder Transaktionsprotokoll-Datei direkt nach der Erstellung mit "0"-Werten. Wenn sofortige Dateiinitialisierung aktiviert ist, dann werden bei Datendateien keine "0"-Werte geschrieben (bei Transaktionsprotokoll-Dateien wird das in jedem Fall gemacht). Eigentlich wird die Datendatei also nicht initialisiert, wenn sofortige Dateiinitialisierung aktiv ist - aber die Namensgebung ist nun einmal so.

Wie wird es aktiviert?
Der Grund, warum dieses Feature so wenig bekannt ist, ist wahrscheinlich darin begründet, dass es nicht eine Option des SQL Servers ist, sondern dass es von den Berechtigungen des SQL Server Dienstkontos abhängig ist. Um die Berechtigung zu erteilen, starten Sie das Programm secpol.msc.
Hier wählen Sie unter "Sicherheitseinstellungen" - "Zuweisen von Benutzerrechten" - "Durchführen von Volumenverwaltungsaufgaben" und fügen im nachfolgenden Dialog das Benutzerkonto, mit dem der SQL Server Dienst ausgeführt wird, hinzu.
Falls das Konto bereits Mitglied der lokalen Administratoren-Gruppe ist, dann ist dieser Schritt nicht erforderlich, denn diese Windows Gruppe hat bereits diese Berechtigung. Aber mal ehrlich: so etwas haben Sie doch nicht gemacht, oder? Um SQL Server sicher zu betreiben, ist es äußerst wichtig, dass er mit einem Dienstkonto ausgeführt wird, das minimale Berechtigungen hat. Und die Mitgliedschaft in der Gruppe lokaler Administratoren ist ziemlich genau das Gegenteil von minimalen Rechten ;-)
Erstellen Sie also für jede SQL Server Instanz ein eigenes neues Benutzerkonto (Domänenkonto oder lokales Konto) und nutzen Sie dann das Programm SQL Server Configuration Manager, um dieses Konto dem SQL Server Dienst zuzuweisen.
Der Configuration Manager sorgt dafür, dass das SQL Server Dienstkonto mit allen erforderlichen Berechtigungen ausgestattet wird (Zugriff auf Registry Einträge, Zugriff auf Dateien, etc.). Es ist also ganz einfach, ein Dienstkonto mit minimalen Berechtigungen auszustatten.

Wo ist der Haken?
Für jeden Vorteil müssen wir bezahlen. Beim SQL Server bezahlen wir in der Regel entweder mit Geld (Hardware, Lizenzen) oder indem wir Nachteile an anderer Stelle in Kauf nehmen. In diesem Fall ist der Preis eine verringerte Sicherheit. Dadurch, dass die Datendatei nicht initialisiert wird, enthält sie die Inhalte der Festplatte, die vorher dort gespeichert waren. Diese Inhalte kann man sich anschauen:
  • mit DBCC PAGE (undokumentierte Option) bei laufender Datenbank
  • mit einem Dateieditor, nachdem die Datendatei abgehängt wurde
  • in der Datensicherung
Ist das für Ihre SQL Server Instanz ein Problem? Das müssen Sie entscheiden. Ich denke, dass dies in den allermeisten Fällen akzeptabel ist, da Sie ja sowieso dafür sorgen, dass Datendateien und Backups nicht in falsche Hände gelangen können.

Links
Hier habe ich die Informationen für diesen Beitrag gefunden:
http://msdn.microsoft.com/de-de/library/ms175935.aspx
http://www.sqlskills.com/blogs/kimberly/post/Instant-Initialization-What-Why-and-How.aspx
http://www.brentozar.com/sql/blitz-minute-sql-server-takeovers/

2011-06-15

Reguläre Ausdrücke im SQL Server

So mächtig die Sprache SQL auch ist - reguläre Ausdrücke zum Mustervergleich sind nicht Bestandteil der ANSI Spezifikation. Wenn es also darum geht, mit SQL das Format einer Telefonnummer oder einer E-Mail Adresse zu überprüfen, dann wäre eine Erweiterung des Sprachumfangs hilfreich.
Genau das können mit .NET programmierte benutzerdefinierte Funktionen leisten. Schließlich bietet das .NET Framework eine Klasse RegularExpressions, die genau die hierfür benötigten Methoden enthält.

Das Problem

Wie erstelle ich eine benutzerdefinierte Funktion mit .NET Code, um sie anschließend im SQL Server zu verwenden?

Die Lösung
  1. Erstellen Sie in Visual Studio ein Projekt vom Typ "SQL Server Projekt". Dafür benötigen Sie Visual Studio Professional - die mit SQL Server ausgelieferte Version "Business Intelligence Development Studio" kann das nicht.
  2. Veröffentlichen Sie die Funktion im SQL Server
  3. Nutzen Sie diese Funktion in Ihren SQL Statements.
Nachdem Sie in Visual Studio ein neues SQL Server Projekt angelegt haben, wird folgender Rahmen erstellt:





Erweitern Sie nun diesen Rahmen:
  • Fügen Sie den Namespace System.Text.RegularExpressions hinzu. Darin sind die Methoden zum Verarbeiten regulärer Ausdrücke vorhanden.
  • Wir verwenden in diesem Beispiel die Methode Match(), um zu überprüfen, ob der Parameter matchString dem Muster pattern entspricht.


Das Veröffentlichen der Funktion erledigt Visual Studio vollautomatisch. Hinter den Kulissen führt es dabei folgende Schritte aus:
  1. Es kompiliert die DLL-Datei (Assembly) mit dem CLR-Code der neu erstellten Klasse UserDefinedFunctions.
  2. Es lädt diese Assembly wird in den SQL Server:
    CREATE ASSEMBLY <name der klasse> FROM <dateipfad>
  3. Dann deklariert es die Methode udf_RegExMatch als Funktion, deren Programmcode in dieser Assembly liegt:
    CREATE FUNCTION udf_RegExMatch (@pattern nvarchar(4000), @matchString nvarchar(4000))
    RETURNS int
    AS EXTERNAL NAME <name der methode>
Wohlgemerkt - diese Schritte müssen Sie nicht manuell ausführen, weil Visual Studio das für Sie erledigt. Sie können das überprüfen mit den folgenden Anweisungen:

select * from sys.assembly_files
select * from sys.assembly_modules

Danach finden Sie diese Funktion im SQL Server Management Studio (Object Explorer) unter "Programmierbarkeit" - "Funktionen". Bevor Sie diese Funktion testen können, muss zunächst die Ausführung von .NET Code zugelassen werden. Das ist standardmäßig nicht erlaubt.

exec sp_configure 'clr enabled', 1;
reconfigure


Sie können die Funktion nun testen, zum Beispiel mit folgendem Statement:

SELECT [DemoDB].[dbo].[udf_RegExMatch] (
   '[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+(com|org|edu|de)'
  ,'v.heck@netcologne.de')


Das Select Statement liefert den Wert 1 zurück, wenn die E-Mail Adresse v.heck@netcologne.de dem vorgegebenen Muster entspricht; ansonsten liefert sie den Wert 0 zurück. Probieren Sie es aus!
Mit dem folgenden Statement beispielsweise können Sie alle E-Mail Adressen in der Tabelle Person.EmailAddress in der AdventureWorks2008R2 Datebank überprüfen:

select
 EmailAddress,
 DemoDB.dbo.udf_RegExMatch

   ('[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+(com|org|edu|de)', 
    EmailAddress)
from AdventureWorks2008R2.Person.EmailAddress


Fazit

Anhand dieses Beispiels ließ sich zeigen, wie eine Funktion in .NET programmiert werden kann, die anschließend als Funktion im SQL Server zur Verfügung steht. Wie so oft beginnt die eigentliche Arbeit im Projekt dort, wo diese Demo aufhört. Insbesondere haben wir noch keinerlei Fehlerbehandlung hinzugefügt. Und auch die Performance-Aspekte haben wir bisher außer acht gelassen. Denn so, wie wir die Aufgabenstellung bisher gelöst haben, wird bei jedem Aufruf der Funktion die Regex neu instanziiert. Bei größeren Datenmengen (siehe zweites SELECT Statement) kann das zu einem echten Performance-Engpass werden.
Mein Ziel war es, hier das Prinzip aufzuzeigen. Weiter führende Informationen finden Sie zum Beispiel hier:

http://msdn.microsoft.com/de-de/magazine/cc163473.aspx
http://blogs.msdn.com/b/sqlclr/archive/2005/06/29/regex.aspx