SQL Server & BI Blog by Andreas Wolter

(DE)

Was für ein Jahr: nachdem ich bereits  3 SQLSaturdays (Dänemark, Niederlande, Deutschland) hinter mir habe, werde ich nun auch auf dem SQSaturday #258 (5. Okt.) in Istanbul sprechen.
Und nach meiner ersten PreCon dieses Jahr auf dem SQLSaturday in Deutschland, die ich zusammen mit Mladen Prajdić, zum Part “XEvents” gegeben habe, werde ich dieses mal einen ganzen Tag PreCon (4. Okt.) zum Thema “Performance Analyse & Tuning Techniken” geben!

(EN)

What a year: after already 3 SQLSaturdays (Denmark, Netherlands, Germany) behind me, I will now also speak at SQSaturday #258 (Oct. 5th) in Istanbul, Turkey.
And after my first PreCon this year at the SQLSaturday in Germany, which I was co-presenting together with Mladen Prajdić, on the part “XEvents”, I will now be giving a full day PreCon (Oct. 4th) on “Performance Analysis & Tuning Techniques”!

Ich freue mich wirklich sehr über das Thema und zugleich auch über den Austragungsort.
Istanbul ist für Geschichts- & Fotografie Enthusiasten wie mich ein absolutes Muss. (Meine Facebook Wand erwartet wieder tolle Bilder :)

Zurück zum Technischen: Hier ist die Vortrags-Beschreibung & Agenda:

Sei bereit für einen vollen Tag Abtauchen in SQL Server Performance Analyse.
Diese Session konzentriert sich auf Werkzeuge und Techniken und wie man häufige Probleme löst.
Wir starten auf Betriebssystem-Ebene mit PerfMon, und steigen dann weiter ein in SQL Server Wait Stats Analyse für Flaschenhalsanalyse. Wir verwenden verschiedene Werkzeuge wie SQL Trace & Profiler und den Nachfolger: Extended Events für detaillierte Analyse und sehen auch, welche anderen Tools von Codeplex zur Verfügung stehen.
Wir untersuchen den Plan Cache und Indexierung mithilfe von DMVs und steigen schließlich auch in Sperren und Parallelitätsprobleme ein.
Macht Euch auf viele Demos und einen Deep Dive in Kern-Konzepte von SQL Server gefasst.“

I am really excited about the topic and location at the same time. Istanbul is a must-see city for history- & photography enthusiasts like me. (My Facebook wall is awaiting great images :)

Back to the technical stuff. Here is the session description & agenda:

“Be ready for a full day of diving into SQL Server Performance Analysis.
This session is focussed on tools and techniques and how to resolve common problems.
We will start on Operating System level using Perfmon and advance into SQL Server Wait Stats analysis for identifying bottlenecks. We will use different tools like SQL Trace & Profiler and the successor: Extended Events for detailed analysis and see what other tools are available from codeplex. We will examine the Plan cache and Indexing using DMVs and finally get into locking & concurrency problems.
Expect lots of demos and a deep dive into core concepts of SQL Server.”

Enthaltene Themen:

• Performance Monitor für Server-Ebenen Performance Analyse
• Wait-Stats Methodik
• SQL Trace & Profiler bis Extended Events für Arbeitslast & Abfrage-Analyse
• Tempdb Performance Optimimierung
• Plan-Cache Analyse
• Index & Statistiken, Analyse & Tuning
• Sperren & Blockade Analyse / Parallelitätsprobleme

Included Topics:

• Performance Monitor for Server-Level Performance Analysis
• Wait-Stats Methology
• SQL Trace & Profiler to Extended Events for Workload & Query-Analysis
• Tempdb Performance Optimization
• Plan-Cache Analysis
• Index & Statistics, Analysis & Tuning
• Locking & Blocking Analysis / Concurrency problems

And hier ist der Link zur PreCon (4. Okt.) Registrierung: www.eventbrite.com/event/7619115981

Wer ein Wochenende in einer historischen Stadt voller Sehenswürdigkeiten mit deep-dive SQL Server Themen kombinieren möchte, ist gern willkommen :-)

And here is the link to the PreCon (4th of Oct.) registration:
www.eventbrite.com/event/7619115981

If you want to combine a week-end in a city of history and beauty, with some deep-dive SQL Server topics, come and join us :-)

Neben der PreCon werde ich auch einen Vortrag am Samstag (5. Okt) halten.

Thema: Sicherheit

Titel: "SQL Attack…ed” – SQL Server under attack."

Und auch zwei weitere Kollegen von der PASS Deutschland e.V. werden dabei sein: Tillmann Eitelberg und Oliver Engels.

Besides the PreCon I will also hold a presentation on Saturday (5th of Oct.).

Topic: Security

Title: "SQL Attack…ed” – SQL Server under attack."

And also two other colleagues from the German PASS will be there: Tillmann Eitelberg and Oliver Engels.

(DE)

Inspiriert durch den Blog Post Post “Measuring Observer Overhead of SQL Trace vs. Extended Events” von Jonathan Kehayias, war ich neugierig, ein paar weitere Benchmarks durchzuführen. Die Resultate habe ich bereits in meinen SQL Server Master-Class Workshops zu XEvents und auf der PreCon des SQLSaturday #230 präsentiert. Nun teile ich sie endlich hier.

Vor allem folgende Aspekte interessierten mich:

• Was ist der Performance-overhead einer Extended Event Session selber – ohne die Verarbeitung und des Dispatchens zu den Targets
• Wie verhält sich das im Vergleich zu SQL Trace – auch das ohne Overhead für ein Ziel (Keine Datei, kein Profiler: gar kein Konsument)

Da ich einmal das Setup bereit hatte, habe ich mich entschieden, alle anderen Ziele ebenfalls aufzunehmen, sowie einige grundlegende Options-Variationen, um einen kompletten Überblick zu erhalten, der in sich vergleichbar ist.

(EN)

Inspired by the Blog Post “Measuring Observer Overhead of SQL Trace vs. Extended Events” by Jonathan Kehayias, I was curious to do some more benchmarking.
I have already presented the results in my SQL Server Master-Class workshops on XEvents as well as at the PreCon of the SQLSaturday #230. Now I am finally sharing them with you here.

I was interested in the following aspects, in particular:

• What is the Performance-overhead of an Extended Event Session itself – not including the processing/dispatching for the target
• How does this compare to the old-school SQL Trace – again with no Target overhead (No File, no Profiler: no consumer at all)

Besides that, once I had the setup ready, I decided to include all the other targets and some basic option-variations, to have a complete overview that is comparable in itself.

Ich entschied mich für ein System, das bereits unter CPU Druck steht – die CPU Verwendung liegt fast vollständig bei 100%.
Unter diesen Umständen hoffte ich die maximalen Auswirkungen von Extended Events („Erweiterte Ereignisse“) als auch SQL Trace (Zur Erinnerung: SQL Profiler basiert auf SQL Trace) zu erhalten. Man könnte meinen, dass eine E/A(I/O)-lastige Arbeitslast noch mehr beeinflusst werden würde, aber das hängt an den Targets für Extended Events. – 4 davon gehen lediglich in den Arbeitsspeicher, gegenüber einem File-Target (welches man ohnehin auf ein Nicht-Daten-Volume legen sollte). Und da ich auch den reinen Overhead der Trace-Technologien (ohne Ziel) messen möchte, macht das noch mehr Sinn. Abgesehen davon kann I/O in der Regel meist recht einfach getuned werden, indem man auf ein ansonsten unverwendetes und schnelles Device schreibt, wohingegen es für CPU nicht so einfach ist, die Kosten unsichtbar zu halten. Das im Hinterkopf zu behalten hilft, die Resultate, die hier präsentiert werden zu evaluieren und sie in den rechten Kontext zu rücken.

I also decided for a test of a system that’s already under CPU pressure - so the Processor Usage is almost constantly hitting 100%.
Under these circumstances I was hoping to get the maximum impact of Extended Events as well as SQL Trace. (Remember: SQL Profiler is based on SQL Trace) One could think that an I/O driven workload would be even more influenced, but that depends on the targets of Extended Events. - 4 of which only go to memory, vs. one file target (which one should put onto a non-data volume anyways). And since I also want to measure the pure overhead of the tracing technologies (without any target), this makes even more sense.
Besides that, I/O usually can be tuned quite easily by writing to an otherwise idle and fast device, whereas for CPU it is not as simple to keep the costs invisible. Keeping this in mind helps to evaluate the results presented here and bringing them into the right context.

Test Umgebung

“Einfach aber physikalisch”

1 Windows 2008 R2 Server mit SQL Server 2012 SP1. Keine laufenden Dienste abseits des minimal Notwendigem, um Seiten-Effekte zu verhindern.
Ich habe auch die Default-Trace sowie die system_health_session gestoppt.
Der Distributed Replay-Client wurde für alle Test local ausgeführt, und Ja, das hat Effekt auf den Server, aber das war gleich für alle Tests. Alle Tests wurden 3 Mal wiederholt (einige sogar öfter), um sicherzustellen, dass der Durchschnitt/Median valide ist.
Der Server, wie gesagt, war physikalisch (das kommt praktisch, wenn man Benchmarks mit einer Gesamtzeit von ~50 Stunden durchführt.): 1 Dual Core Intel Xeon mit 8 GB RAM (was bei Weitem genug war), Daten Volumen und Volumen für Trace-Targets auf separate Controllers.

Die Arbeitslast war ein Mix von meist kurzen (Sub-Sekunden) aber auch einigen Abfragen mit mehreren Sekunden Laufzeit, (Ad Hoc und gespeicherte Prozeduren), um eine einigermaßen realistische Decision-Support-System Last zu simulieren – jedoch ohne Datenänderungen.
Die Baseline Workload dauerte 00:24:24 Minuten. Die Statistiken blieben für alle Workload-Zyklen identisch. Die verwendete Datenbank war AdventureWorks2012.

Test Environment

“Simple but physical”

1 Windows 2008 R2 Server with SQL Server 2012 SP1. No running services above the minimum required, to avoid side-effects.
I also shut down the default trace as well as the system_health_session.
The Distributed Replay-client was run locally for all tests, so yes, this has effect on the server, but it was equally for all tests. All tests have been repeated 3 times (some even more) to make sure the average/median is valid.
The server, as I already said, was physical (which comes in handy when you are running benchmarks for a total of ~50 hours): 1 Dual Core Intel Xeon with 8 GB of RAM (which was by far enough), Data Volumes and Volumes for Trace-Targets on separate Controllers.

The workload was a mixture of mostly short (sub-second) but also several queries of several second runtime, (Ad Hoc and stored procedures) to simulate a somehow realistic decision-support-systems’ load – no data changes though.
The baseline workload took 00:24:24 minutes. The statistics stayed the same for all workloads cycles. The database used was AdventureWorks2012.

Trace-Konfigurationen

Ich habe mich für eine einfache, aber nicht unübliche Trace entschieden, die typischerweise viele Daten sammelt.
Die Trace sammelt nur 2 Ereignisse:

Trace-Configurations

I decided for a simple but not uncommon trace that typically collects a lot of data. The trace collects only 2 events:

Ausgefiltert: System-Sitzungen und andere Datenbanken als die, die hier unter Last steht.

Gesammelte Daten:

Filtered out: System-sessions and other databases than the one under load here.

CollectedData:

Das gab mir den Vorteil, einen validen Test für alle Extended Event Targets, die bereitgestellt werden, zu haben (lediglich ETW-Tracing wurde ausgelassen) - speziell das Histogramm und die „Ereignispaarbildung“ (Pair-Matching)(Die wenigen Ereignisse von Lock-Escalation störten mich bewusst nicht).

Die folgenden Trace Technologien und -Ziele wurden verwendet:

This gave me the advantage to have a valid test for all Extended Event Targets that are provided (only ETW-Tracing was left out) - specifically histogram and Pair Matching (I did not care about the few occasions of lock escalation on purpose).

The following Trace technologies and -Targets were used:

Für jedes Ziel der Extended Events habe ich 4 Varianten getestet, basierend auf den Sitzungsoptionen:

For each Extended Event Target I tested 4 variations, based on the session options:

Alle anderen Optionen verwendeten die Standardwerte für diese Tests.

All other options were using the defaults for these tests.

Die Ergebnisse

Ich habe die Gesamtlaufzeit für die Workload sowie Batches pro Sekunde und CPU Zeit % gemessen.

Und hier ist die vollständige Tabelle mit den Ergebnissen:

The Results

I measured the total time for the workload to take as well as batches per second and CPU time %.

And here is the complete Table of Results:

EL = Ereignisverlust. S = Verlust einzelner Ereignisse, N = Kein Ereignisverlust, M = Verlust mehrerer Ereignisse

CT = Kausalitätsverfolgung (Causality Tracking) Ein

EL = Event Loss. S = Single event loss, N = No event loss, M = Multiple event loss

CT = Causality Tracking On

Um Zeit und Platz zu sparen, konzentriere ich mich auf die Benchmarks mit den Optionen single event loss und no event loss ohne Causality Tracking. Tatsächlich waren die Kosten von Causality Tracking weniger als 1% für alle Ziele.

Hier ist daher das komprimierte Ergebnis:

For the sake of saving space and time, I will focus on the benchmarks with the options single event loss and no event loss without causality tracking. In fact, the cost of causality tracking was less than 1% for all targets.

So this is the more condensed result:

Was wir demnach sagen können, ist:
(Erinnern wir uns daran: alles läuft unter Hoher CPU Last. Das ist die Grundlage aller Ergebnisse.)

What we can tell from that, is:
(Remember: Everything is running under High CPU Load, so that’s the base of all results.)

• Eine blanke XEvent Session alleine, ohne Targets, hat bereits einen spürbaren Einfluss von ~15 % Leistungsverlust.
• Der Aufschlag („Overhead“) der künstlich erzeugten (nicht unterstützten) Version einer blanken SQL Trace, ohne den Aufschlag eines Rowset oder File Providers („Target“), ist nicht allzu weit von dem einer Extended Events Session ohne dem Dispatchen zu einem Target entfernt (~19% gegen ~15%).
• Der Ringpuffer (Ring Buffer) ist das XE-Target mit dem größten Overhead (~40%). (!)
• Das Ereignisdateiziel (“File-Target”) hat, wie erwartet, wirklich den geringsten Overhead, aber dieser beläuft sich immer noch auf 25%.
• Die Unterschiede zwischen den Targets Ereigniszähler („Counter“), Histogramm und Ereignispaarbildung („Pair Matching“) sind irrelevant.
• Ja, sogar der simple Counter hat einen höheren Overhead als das File Target.
• Das File Target mit No Event Loss zu konfigurieren, fügt fast den vierfachen Overhead hinzu.
• Die Unterschiede für die anderen Targets zwischen single, multiple und sogar no event loss ist in diesem Szenario fast negierbar.
• Verglichen mit Extended Events ist der Overhead durch eine SQL Trace fast schon “brutal”: Er vervierfacht die Gesamtlaufzeit.
• Und noch mehr: Tracing mit dem SQL Profiler ist in solch einem Szenario schlicht ein Ding der Unmöglichkeit: Ich habe 21 Stunden eingetragen, aber das ist tatsächlich eine Schätzung: Ich beendete diese Test-Zyklen nach rund 3 Stunden (man möge mir verzeihen, aber es ist schlicht Zeitverschwendung...), zählte die beendeten Events und rechnete aus, wie lange es noch gedauert hätte, grob – aber tatsächlich eher optimistisch. Profiler ist, wie ich es immer sage, ein “No-Go” um für längere Zeiträume zu tracen, vor allem, wenn die Performance ohnehin bereits leidet.

• A plain XEvent Session itself, with no Targets, already does have a noticeable impact of ~15 % performance decrease.
• The overhead of the artificial (not supported) version plain SQL Trace, without the overhead of a rowset or file provider (aka target), is not too far off the Extended Events Session without the dispatching to any target (~19% versus ~15%).
• The Ring Buffer is the XE-Target with the greatest overhead (~40%). (!)
• The File-Target really does have the lowest overhead as expected, but it’s still 25%.
• The differences between the Targets Counter, Histogram and Pair Matching are irrelevant.
  • Yes, even the simple Counter has a higher overhead than the File Target.
  • Configuring the file target with no event loss adds approximately 4 times as much performance overhead.
  • The difference for the other targets between single, multiple and even no event loss is mostly negligible in this scenario.
  • Compared to Extended Events, the overhead through SQL Trace is almost “brutal”: It quadruplicated the total runtime.
  • And there's even more: Tracing with SQL Profiler is absolutely impossible for such a scenario: I filled in 21 hours, but that’s actually an estimate: I stopped those test cycles after around 3 hours (forgive me, but it’s just a waste of time...), counted the finished events and calculated how much else it would have taken, roughly – in an actually rather optimistic manner. Profiler, as I always say, is a “no go” for tracing longer periods of time, especially if performance is already at stake.

Ein schönes Bild der SQL Trace Architektur findet sich in den BOL: msdn.microsoft.com/en-us/library/hh245121.aspx
Die Extended Events Architektur wird hier bildlich gezeigt: msdn.microsoft.com/en-us/library/bb630318%28v=sql.105%29.aspx

You have a nice picture of the SQL Trace Architecture in BOL: msdn.microsoft.com/en-us/library/hh245121.aspx
The Extended Events Architecture is depicted here: msdn.microsoft.com/en-us/library/bb630318%28v=sql.105%29.aspx

Hinzufügen von Filtern

Wie ändert das Hinzufügen von Filtern den Beobachter-Overhead?

Es ist wichtig, zu wissen, was ich als Filter verwendet habe: Die database_id / source_database_id.
- Tatsächlich habe ich einen Filter verwendet, der niemals “true” zurückgeben würde, indem ich einen Filter auf database_id = 77 angelegt habe – da keine Sitzung/Abfrage/Sperre je auf dieser nicht-existenten Datenbank lag.
Warum habe ich das gemacht? – Das ist ein rein künstlicher Test/Benchmark. Was mich interessiert, ist der reine Overhead des Tracen, und nicht, was passiert, wenn ein bestimmter Prozentsatz der Aktivität nicht protokolliert wird. (Das könnte vielleicht etwas für einen anderen Benchmark sein. :-))

Adding Filters

How does adding filters change the observer overhead?

Now it is important to note what I actually used as a filter: The database_id / source_database_id.
- I essentially added a filter that would never return “true” by adding a filter on database_id = 77 – since no session/query/lock was on this non-existing database.
Why did I do that? – This is a totally artificial test/benchmark. I am interested what the pure overhead of tracing is, and not what happens if a certain percentage of activity is not logged. (That might be something for a different benchmark. :-))

Dieses Mal konzentriere ich mich nur auf „single“ und „no event loss“.

Die Resultate des gefilterten Tracing, ohne tatsächlich irgendwelche Events zu speichern/protokollieren, ist wie folgt:

This time I only focused on “single” and “no event loss”.

The results of Filtered Tracing, without actually storing/logging any events is as follows:

• Wie man sehen kann, fällt der Overhead der reinen XEvent Session ohne Target von ~ 15% auf 9% bzw. ~ 16,5% gg. 12,5 % (No Event loss)
• Die anderen Targets zeigen in diesem Setup tatsächlich die größten Unterschiede:
  • Das Counter Target mit single event loss profitiert am Meisten von dem Filter und fällt von ~33,5 auf 8,4 % Overhead.
  • Nur Ring Buffer und Pair Matching zeigen keinen Unterschied zwischen single und no event loss.
  • SQL Trace mit und ohne rowset Provider (Datei) als Target haben fast den gleichen Overhead – keine Überraschung, wenn man die Architektur kennt. Das ist tatsächlich ein weiterer risen Unterschied, der für Extended Events spricht, wo Filters viel früher in der Trace berücksichtigt werden.
  • Das der Overhead von SQL Trace ohne Target und zugleich aller Events herausgefiltert sogar höher ist, als der Overhead ohne Target aber auch ohne Filter (von dem vorhergehenden Test-Zyklus), muss an dem Verarbeiten des Ausfilterns selber liegen. Ich werde mich damit aber nicht weiter befassen, da das Setup weder dokumentiert noch unterstützt ist.
    Und in meinen Augen ist “SQL Trace“ sowieso „tot“ ;-) – Okay, ich übertreibe hier ein wenig, aber ich bin auch ganz offen: Für SQL Server 2012 gibt es fast keinen Grund mehr zu dessen Verwendung. – Benchmarks mithilfe von Distributed Replay durchzuführen, ist so ein Grund – ist das nicht ironisch?

Für diesen Test habe ich SQL Profiler nicht ausgeführt, Pardon. Sie wissen bis hierhin wahrscheinlich schon, warum. ;-)

• As one can see, the overhead of the pure XEvent session without target drops from ~ 15% to 9% respectively ~ 16.5% vs. 12.5 % (no Event loss)
• The other targets do in fact show more differences in this setup:
• The Counter Target with single event loss benefits most of the filter and drops from ~33.5 to 8.4 % overhead.
• Only Ring Buffer and Pair Matching show no difference between single and no event loss.
• SQL Trace with and without rowset provider (file) as target have almost the same overhead – no surprise if you know the architecture. This is in fact another huge difference which counts for Extended Events, where filters get honored much sooner within the Trace.
• That the overhead of SQL Trace without target and at the same time all events being filtered out is actually higher than the overhead without target but no filtering (from the former test cycle) must be caused by the processing of for filtering out. I will not dive more into this though, since this setup is not documented and or supported.
  And in my eyes, “SQL trace is dead” anyways ;-) – okay, I am exaggerating a bit here, but I am also just being frank: For SQL Server 2012 there is almost no need to use it any more. – Running benchmarks using Distributed Replay is such a reason – isn’t that ironic?

I did not run SQL Profiler for this Test, forgive me. You probably know why by now. ;-)

Wait-Types für Extended Events

Ein weiterer Aspekt, der mich interessierte, waren die XEvent Wait-Typen, die auftreten würden, wenn man Extended Event Sessions ausführt. (Die Warte-Statistiken sind oft die Basis für Performance-Analysen)

Allgemein sieht man folgende:

Wait-Types for Extended Events

Another aspect I was interested in were the specific XEvent wait-types which would occur when running Extended Event sessions.
(Wait-Statistics are often the base for performance analysis)

In general, you will see the following:

Beim Starten einer Sitzung:

When starting a session:

Beim Beenden einer Sitzung:

When stopping a session:

 Während Sessions aktiv sind, sieht man:

While running sessions you will see:

Beim Starten eines File-Targets sieht man außerdem:

When starting the File target you will also see:

Wenn man eine Sitzung mit der No Event Loss Option ausführt, sieht man gegebenenfalls:

If you run a session with the no event loss option you might see

So. Ich hoffe das war interessant. Man kann noch weitere und andere Schlüsse aus den Ergebnissen ziehen. Immer im Hinterkopf zu behalten, ist, dass das ein sehr spezielles Szenario ist, wo keine CPU Reserven zur Verfügung stehen, so das der Trace/Beobachter-Overhead sich manifestieren muss – keine Chance, als eben (CPU) Ressourcen wegzunehmen.

So, I hope this was interesting for you. You may draw more and other conclusions out of these results. Remember though: this is a very specific scenario with no CPU reserves, so the Tracing/observer overhead has to show up – no choice but to take away (CPU) resources.

(DE)

Seit SQL Server 2005 gibt es die Serverweite Berechtigung CONTROL SERVER. Prinzipiell eine Alternative zur sysadmin-Mitgliedschaft, blieb sie dennoch nicht viel mehr als ein Ladenhüter – kaum bekannt und noch weniger verwendet.

Einer der Hauptgründe dafür war die fehlende Möglichkeit, dieses Recht einer Gruppe von Prinzipalen/Logins auf Server-Ebene zu geben.

Seit SQL Server 2012 ist es ja nun möglich, eigene Server-Rollen zu definieren, so dass diese Berechtigung nun immer mehr Verwendung findet.

Dennoch ist sie kein vollständiger Ersatz für sysadmin. Warum das so ist wo die Unterschiede liegen und wo es sogar ein Risiko sein kann, möchte ich im Folgenden für meine Leser festhalten und demonstrieren.

(EN)

Since SQL Server 2005, the server wide permission CONTROL SERVER has been existing. In principle being an alternative to sysadmin-membership, it did not turn out to be much more than a shelf warmer. - Little known and even less used.

One of the main reasons for this was the absence of an option to grant this permission to a group of principals/Logins on server-level.

Since SQL Server 2012, it has been possible to define custom server-roles, so now this permission is used more and more.

However, it is no complete replacement for sysadmin. In the following, I want to demonstrate to my readers exactly why this is, where the differences are and where it can even be a risk.

Grundlegende Kenntnisse über das Verhalten von sysadmin und dbo setze ich als hinreichend bekannt voraus, und konzentriere mich auf das, was bei der Verwendung von CONTROL SERVER anders ist.

I will take as a given basic knowledge about the behavior of sysadmin and dbo and therefore focus on the things that are different when using CONTROL SERVER.

Zunächst wird ein neuer Login, „DBA_TheDude“, angelegt – das Passwort natürlich streng meinen Richtlinien (für Blogs) entsprechend. ;-)

Es folgen eine neue Serverrolle, „Role_DBA“, in die der Login als Mitglied aufgenommen wird.

Und da diese Rolle für Administratoren gedacht ist, erhält sie die weitest-möglichen Rechte: CONTROL SERVER.

First of all, a new Login, „DBA_TheDude“, is created – the password of course adhering strongly to my rules (for blogs). ;-)

This is followed by a new server role “Role_DBA” to which the Login will be added as a member.

And since this role is meant for administrators, it receives the most extensive permission: CONTROL SERVER.

Als nächstes melden wir uns als DBA am System an und lassen uns „die (effektiven) Rechte verlesen“ :-)

Next we log onto the system as DBA and have "our (effective) rights" displayed. :-)

permission_name

server

CONNECT SQL

server

SHUTDOWN

server

CREATE ENDPOINT

server

CREATE ANY DATABASE

server

CREATE AVAILABILITY GROUP

server

ALTER ANY LOGIN

server

ALTER ANY CREDENTIAL

server

ALTER ANY ENDPOINT

server

ALTER ANY LINKED SERVER

server

ALTER ANY CONNECTION

server

ALTER ANY DATABASE

server

ALTER RESOURCES

server

ALTER SETTINGS

server

ALTER TRACE

server

ALTER ANY AVAILABILITY GROUP

server

ADMINISTER BULK OPERATIONS

server

AUTHENTICATE SERVER

server

EXTERNAL ACCESS ASSEMBLY

server

VIEW ANY DATABASE

server

VIEW ANY DEFINITION

server

VIEW SERVER STATE

server

CREATE DDL EVENT NOTIFICATION

server

CREATE TRACE EVENT NOTIFICATION

server

ALTER ANY EVENT NOTIFICATION

server

ALTER SERVER STATE

server

UNSAFE ASSEMBLY

server

ALTER ANY SERVER AUDIT

server

CREATE SERVER ROLE

server

ALTER ANY SERVER ROLE

server

ALTER ANY EVENT SESSION

server

CONTROL SERVER

Klingt gut. Scheint Nichts zu fehlen.
Oder doch?

Vergleichen wir es mal mit der Liste aller Rechte, die es auf Server-Ebene überhaupt gibt:

Sounds good. Nothing seems to be missing at first glance.
Or does it?

Let’s compare it to the list of all permissions which exist on server-scope:

Ok, also wir haben wirklich alle Rechte, die man (auf Serverebene) vergeben kann.
Das ist auch dokumentiert: Permissions (Database Engine)
und hier: GRANT Server Permissions (Transact-SQL)

Diese Berechtigungen und Kommandos funktionieren auch wie dokumentiert.

Wie sieht es aber mit gespeicherten Systemprozeduren aus?

Ok, we really do have all permissions which can be granted (at server scope).
This is also documented: Permissions (Database Engine)
and here: GRANT Server Permissions (Transact-SQL)

Those permissions and commands really do work as documented.

But how about system stored procedures?

Warum das jetzt?

Ganz einfach. Sicher ist es den meisten noch gut aus SQL Server 2000-Zeiten bekannt, dass sicherheitskritische Systemprozeduren einen Check auf Mitgliedschaft in bestimmten Rollen enthalten. Und das tun die meisten derer auch heute noch. Ein Artefakt der damaligen unflexiblen Rechte-Verwaltung, kann man sagen.

Wenn man in den Code der sp_readerrorlog schaut, wird es dort genau so gemacht:

Why ist that now?

Quite simple. Most do probably remember from SQL 2000 times, that security wise critical system procedures include a check for membership in certain roles. And most of them still do the same today. One can call it an artefact of the then inflexible permission-management.

When we look into the code of sp_readerrorlog, this is what is being done there as well:

Okay.
Natürlich könnten wir diesen Code umgehen und die xp_readerrorlog direkt aufrufen.

In der GUI-Anzeige im Management Studio hilft uns das nicht, wir können es als manuellen Workaround verwenden.

Weitere Systemprozeduren, die über SSMS aufgerufen werden und die Ausführung verweigern sind unter anderem:
sp_addumpdevice - für Backup-Devices
sp_enum_oledb_providers – wird beim Konfigurieren und Betrachten von Linked Servers verwendet.

sp_addlinkedserver lässt sich dafür ohne Probleme ausführen – nur eben nicht über die GUI wegen eben genannter Prozedur.

Die Einrichtung von Database-Mail über die GUI bleibt ebenfalls den sysadmins vorbehalten. Via Script sollte es jedoch funktionieren (ungetestet).

Auch die Konfiguration des Distributors für die Replikation mittels sp_adddistributor ist ohne den sysadmin-Bit nicht möglich.
Andere Replikationskonfigurationsaufgaben sind teilweise sogar über die GUI möglich.

Unter SQL Server 2005 – 2008R2 gibt es für das Hinzufügen/Entfernen von (Server-)Rollenmitgliedern nur die Prozedur sp_addsrvrolemember / sp_dropsrvrolemember. Diese führt ebenfalls eine Serverrollen-Mitgliedschafts-Prüfung durch. - Unter SQL Server 2012 ist diese Prozedur aus Rückwärtskompatibilitätsgründen noch vorhanden, jedoch wurde dieser Check aus dem Code entfernt. Damit verhält sie sich in dieser Hinsicht somit wie der direkte Aufruf von ALTER SERVER ROLE {RoleName} ADD MEMBER {Loginname}.

Das gleiche gilt für sp_addrolemember / sp_droprolemember für Datenbankrollen.

Okay.
Of course we can go round this code by calling xp_readerrorlog directly.

This does not help in the GUI-presentation in Management Studio but we can use it as a manual workaround.

Among other system procedures that are called via SSMS and refuse execution are:
sp_addumpdevice - for backup-devices
sp_enum_oledb_providers – is used for viewing and configuring Linked Servers.

sp_addlinkedserver on the other hand can be executed without problems – just not via the GUI because of the just mentioned procedure.

Setting up Database-Mail via the GUI is also reserved for sysadmins. It should work via script though (untested).

Also the configuration of the distributor for replication by means of sp_adddistributor is not possible without the sysadmin bit.
Other replication tasks are partially possible even via the GUI.

Under SQL Server 2005 – 2008R2, to add/remove (server) role members, solely the system procedure sp_addsrvrolemember / sp_dropsrvrolemember exists. This one also conducts a server-role-membership-check. - Under SQL Server 2012 this procedure still exists for backwards-compatibility reasons, but this specific check has been removed from the code. Thereby it now behaves like the direct call of ALTER SERVER ROLE {RoleName} ADD MEMBER {Loginname}.

Same applies to sp_addrolemember / sp_droprolemember for database roles.

Soweit zu Systemprozeduren. Eigentlich ziemlich simpel, wenn man die Hintergründe kennt.

Daher, wo möglich: DDL-Kommandos verwenden, wie seit SQL Server 2005 nahegelegt wird.

Randnotiz: Es gibt mindestens 7 Variationen der Prüfung auf sysadmin-Mitgliedschaft. :-) :-(

Eine Kostprobe:

So much for system stored procedures. Quite simple after all, if you know the background.

Therefore, wherever possible: use DDL-commands, as it is advised since SQL Server 2005.

Side-note: there are at least 7 variations for the check of sysadmin-membership. :-) :-(

A taster:

Die häufigsten Fehlermeldungen, die dann ggfls. ausgelöst werden, sind:

The most frequent error messages which are triggered if applicable are:

message_id

text

14126

You do not have the required permissions to complete the operation.

14260

You do not have sufficient permission to run this command. Contact your system administrator.

15003

Only members of the %s role can execute this stored procedure.

15247

User does not have permission to perform this action.

21089

Only members of the sysadmin fixed server role can perform this operation.

22904

Caller is not authorized to initiate the requested action. DBO privileges are required.

message_id

text

14126

Sie haben nicht die erforderlichen Berechtigungen, um den Vorgang abzuschließen.

14260

Sie haben nicht die erforderliche Berechtigung, um diesen Befehl auszuführen. Wenden Sie sich an den Systemadministrator.

15003

Nur Mitglieder der %1!-Rolle können diese gespeicherte Prozedur ausführen.

15247

Der Benutzer besitzt nicht die Berechtigung zum Ausführen dieser Aktion.

21089

Nur Mitglieder der festen Serverrolle 'sysadmin' können diesen Vorgang ausführen.

22904

Der Aufrufer ist nicht zum Initiieren der angeforderten Aktion berechtigt. Es sind DBO-Privilegien erforderlich.

Eine vollständige Liste aller Prozeduren, die auf sysadmin-Rollenmitgliedschaft prüfen, folgt am Ende.

A complete list of procedures that check for sysadmin-membership is included at the end of this post.

Kommen wir zu einem anderen wichtigen Bereich:

DBCC-Kommandos

Let’s come to another important area:

DBCC commands

Das geht ja gut los.

Die „Regel“ ist bei DBCC noch einfacher:
Fast jedes DBCC-Kommando prüft auf sysadmin.

Von DBCC CHECKDB über DBCC LOGINFO bis zu DBCC TRACEON. Also auch durchaus wertvolle Kommandos auch für den externen Support.
- Tatsächlich ist selbst DBCC HELP nur für sysadmins zugelassen...

Die einzigen Ausnahmen, die mir bekannt sind:
DBCC SHOW_STATISTICS

• man muss jedoch mindestens ddl_admin oder Besitzer der Tabelle sein

DBCC DETACHDB

• das ist ein spezieller Fall, da es über sp_detach_db ausgeführt wird
  
• db_owner oder dbo der datenbank ist ausreichend
  

DBCC FREEPROCCACHE und DBCC SQLPERF

• Diese setzen lediglich die ALTER SERVER STATE Berechtigung voraus.

DBCC DROPCLEANBUFFERS wiederum setzt die sysadmin-Rollenmitgliedschaft voraus.

Diese Berechtigungen sind übrigens recht gut dokumentiert.

That’s a good start, isn’t it..

The „rule“ for DBCC is even simpler:
Almost every DBCC-command checks for sysadmin.

From DBCC CHECKDB via DBCC LOGINFO to DBCC TRACEON. Thus some quite valuable commands also for external support.
- In fact even DBCC HELP is only allowed for sysadmins...

The only exceptions known to me are:
DBCC SHOW_STATISTICS

• one needs to be at least ddl_admin or owner of the table

DBCC DETACHDB

• this is a special case, since it's executed via sp_detach_db
• db_owner or dbo of the database is sufficient

DBCC FREEPROCCACHE and DBCC SQLPERF

• Those require only the ALTER SERVER STATE permission.

DBCC DROPCLEANBUFFERS on the other hand requires sysadmin-role membership.

Those permissions are documented quite well by the way.

Datenbankberechtigungen

Mit CONTROL SERVER hat ein Prinzipal auch vollen Zugriff auf alle Datenbanken.
Fast, aber auch nur fast wie sysadmins, die bekanntlich auf den „dbo“ gemappt werden.

Logins mit lediglich CONTROL SERVER-Berechtigung werden nicht(!) zu dbo gemapped.

Welche Auswirkungen das hat, sieht man im Folgenden:

Database permissions

With CONTROL SERVER, a principal has full access to all databases.
Almost, but only almost like sysadmins, which are, as is well-known, mapped to “dbo”.

Logins with merely CONTROL SERVER permission are not(!) mapped to dbo.

The consequences of this can be seen in the following:

Was ist passiert?

Erinnern Sie sich noch an das pre-SQL 2012 Problem, das man Windows-Gruppen kein Default Schema zuweisen konnte? Und was passierte, wenn ein Entwickler über solch eine Gruppe vergaß, beim Anlegen eines Objektes das Schema anzugeben?

Richtig. Das ist genau das gleiche: es wurde ein Schema mit dem Login-Namen des Entwicklern angelegt. Eine reine Freude für spätere Aufräumarbeiten, die „richtigen falschen“ Schemas zu identifizieren und zu löschen.
Dieses Issue (eines der am höchsten votierten Connect-Items) wurde mit SQL Server 2012 ja gelöst.

Leider wurde dabei verpasst, auch die Behandlung von CONTROL SERVER Berechtigten anzupassen. Dazu hatte ich ebenfalls ein Connect-Item erstellt.: „Login with CONTROL SERVER Permission Creating an Object without specifying Schema leads to creation of new Schema with Login-Name”. - Leider zu spät. :-/

Vermutlich wird dazu ein identischer Code-Block verwendet, denn genau wie in dem Windows-Gruppen-Szenario ist dieses implizite Anlegen des Schemas nicht abfangbar.

Fazit: Immer ein Schema angeben, wenn man als CONTROL Server Berechtigter Objekte anlegt – am besten einfach wirklich immer ein Schema angeben (es gibt noch andere Gründe dafür).

What happened?

Do you remember the pre-SQL 2012 problem, that windows groups could not be assigned a default schema? And what happened if a developer forgot to specify a schema when creating an object?

Right. This is exactly the same: a schema with the Login-Name of the developer has been created. A true joy for later clean-up, to identify and drop the „right wrong” schemas.
This Issue (one of the most voted Connect-Items) has been resolved with SQL Server 2012.

Unfortunately the handling of the CONTROL SERVER permission has been overlooked to be fixed. I did create a connect item: „Login with CONTROL SERVER Permission Creating an Object without specifying Schema leads to creation of new Schema with Login-Name”. – Unfortunately too late. :-/

Probably an identical code-block is being used for that, since just like in the Windows-Group scenario, this implicit creation of the schema is uncatchable.

Conclusion: Always specify a schema when creating an object as a CONTROL SERVER granted principal – best: always specify a schema. (there are other reasons for that, too)

Privilege-Escalation-Risiko:

Abschließen möchte ich mit einem sicherheitstechnisch extrem wichtigen Hinweis:

Logins mit CONTROL SERVER Berechtigung können standardmäßig JEDEN Login impersonifizieren. JEDEN. Also auch alle Sysadmins und sa selber! – Und auch wenn SQL Authentifizierung nicht aktiv ist!

Und so lässt sich das wunderbar ausnutzen:

Privilege-Escalation-risc:

I want to wind up with a security-wise extremely important hint:

Logins with CONTROL SERVER permission can impersonate EVERY Login. ALL OF THEM. Thus also all sysadmins and sa itself! – And even if SQL authentication is not active!

And this is how this can be exploited marvellously:

Wer sich also gegenüber dieser Form von Privilege-Escalation schützen möchte, muss das impersonate (mithilfe von DENY) verbieten.
- Und warum sollte man das nicht wollen, würde es doch ggfl. das gesamte Konzept von CONTROL SERVER in Frage stellen (?! ) -
Denn das ist der finale und entscheidende Unterschied:

Sysadmin-Mitglieder können nicht mit DENY eingeschränkt werden. Prinzipale mit bloßer CONTROL SERVER Berechtigung jedoch schon.

So funktionieren alle reinen DDL/DML Kommando-Berechtigungen eben.

Also, um es noch einmal klar zu sagen, da es so entscheidend ist:
Ein Login mit CONTROL SERVER Recht macht nur dann Sinn, wenn ihm gleichzeitig verboten wird, andere sysadmins (oder am besten: alle Logins) zu impersonifizieren. Andernfalls kann man ihn gleich zum sysadmin machen!

Dummerweise geht das nur über "DENY IMPERSONATE ON LOGIN::[Loginname]". Denn IMPERSONATE ist hierarchisch direkt CONTROL SERVER untergeordnet!

Who wants to protect himself from this form of privilege escalation must DENY impersonating.
- And why would you not want to do that since this eventually would put the whole concept of CONTROL SERVER in question (?!) -
Because this is the final and crucial difference:

Sysadmin-members cannot be restricted anything via DENY. Principals with mere CONTROL SERVER permissions yet can.

This is how all plain DDL/DML commands work.

So, to say it clearly again, because this is vital:
A Login with CONTROL SERVER Permission only makes sense, when at the same time it is explicitely denied impersonation of any sysadmin (or better: any Logins). Otherwise you may as well just make him sysadmin right away!

Unfortunately this only works via "DENY IMPERSONATE ON LOGIN::[Loginname]". This is because IMPERSONATE is hierarchically a direct subordinate of CONTROL SERVER!

Bei einer sich ändernden Liste an Systemadministratoren hat man ein Problem, wenn man nicht an dieses Deny denkt! (Nächstes Risiko!)

- Spätestens an dieser Stelle sollte man sich mit dem Überwachungs-Feature von SQL Server befassen.

For a changing list of system administrators one has a real problem, in case of not remembering this crucial Deny! (Next risk!)

- At the latest at this point one should address the Auditing feature of SQL Server.

Mein Fazit und Lessons-learned daher:

CONTROL-SERVER selber ist eine konsequente Fortführung der Bemühungen des Sicherheits-Teams, SQL Server sicherheitstechnisch noch einfacher und damit robuster zu machen.

Jedoch ist aus dem Grund der Impersonate-Rechte eine Privilege Escalation zu sysadmin sehr einfach. Um diese zu verhindern, sollte man gut mit der Berechtigungs-Hierarchie von SQL Server vertraut sein. – Wie immer: „Wissen schafft Sicherheit.“

Was wirklich schmerzt, ist, das eines der besten Anwendungsszenarien: Rechte für Support-Personal, intern oder Extern.
Das Fehlen der Möglichkeit, DBCC-Kommandos direkt zu berechtigen, macht uns hier einen Strich durch die Rechnung. Wenn man nicht jedes wichtige DBCC-Kommando in eine eigene Prozedur wrappen und somit berechtigen möchte, führt am sysadmin in diesen Anwendungsfällen nichts vorbei. (!)

Im Endeffekt ist eine strikte „Separation of Duties“, die Funktionstrennung auf verschiedene „Rollen“ das eherne Ziel.

Hier kann man dazu Nachlesen – auch das „SQL Server Separation of Duties Framework“ von codeplex, mit vielen guten Ideen kann ich empfehlen einmal anzusehen:

My conclusion and lessions-learned therefore are:

CONTROL SERVER itself is a consistent continuation of the efforts of the security team to make SQL Server even simpler and thereby more robust.

Because of the impersonate-permissions, a privilege escalation is really simple. In order to prevent this, one should be familiar with the permission hierarchy of SQL Server. – As always: “Knowledge leads to security.”

What really does hurt, is one of the best application cases: Permissions for support-personnel, internal or external.
The missing possibility to grant permissions to DBCC directy throws a monkey wrench in our plans. If one does not want to create a procedure as wrapper for every important DBCC command, there is no way around sysadmin in those scenarios. (!)

In the end, a strict “separation of duties” is the iron goal.

Here you can read more about it – you should also check out the “SQL Server Separation of Duties Framework” on codeplex with many good ideas:

Update 04-2014:

Mit SQL Server 2014 wird CONTROL Server etwas sicherer. Hier kann man mehr zu den neuen Möglichkeiten lesen:

New Permissions in SQL Server 2014: IMPERSONATE ANY LOGIN, SELECT ALL USER SECURABLES, CONNECT ANY DATABASE and the old CONTROL SERVER

Update 04-2014:

With SQL Server 2014 CONTROL Server becomes a bit safer. You can read more on the new possibilities here:

New Permissions in SQL Server 2014: IMPERSONATE ANY LOGIN, SELECT ALL USER SECURABLES, CONNECT ANY DATABASE and the old CONTROL SERVER

Eine Ablösung des „sa" ist übrigens nicht geplant, jedoch sinkt mit jedem Release die Anzahl der Szenarien, wo sa/sysadmin zwingend benötigt wird.

A displacement of „sa“ is not planned by the way, but with every release the scenarios where sa/sysadmin is imperatively needed become less.

Anbei die komplette Liste aller Systemprozeduren unter SQL Server 2012, die auf sysadmin-Rollenmitgliedschaft prüfen (171 – gegenüber 173 unter SQL Server 2008R2. SQL Server 2014 CTP1 ist noch das Gleiche):

(Hinweis: Insgesamt 197 Systemprozeduren prüfen auf Serverrollenmitgliedschaft, wie diskadmin oder serveradmin usw.)

Enclosed the list of all system procedures under SQL Server 2012 that check for sysadmin-role membership (171 – vs. 171 under SQL server 2008. SQL Server 2014 CTP 1 still the same):

(Hint: Altogether 197 system procedures check for server role membership like diskadmin or serveradmin and so on)

Module_Name

fn_yukonsecuritymodelrequired

sp_add_agent_parameter

sp_add_agent_profile

sp_adddatatype

sp_adddistributiondb

sp_adddistributor

sp_addqreader_agent

sp_addsubscriber

sp_addsubscriber_schedule

sp_addtabletocontents

sp_attachsubscription

sp_cdc_cleanup_change_table

sp_cdc_disable_db

sp_cdc_disable_table

sp_cdc_drop_job

sp_cdc_enable_db

sp_cdc_enable_table

sp_cdc_restoredb

sp_cdc_vupgrade

sp_certify_removable

sp_change_agent_parameter

sp_change_agent_profile

sp_change_subscription_properties

sp_change_users_login

sp_changedistpublisher

sp_changedistributiondb

sp_changedistributor_password

sp_changedistributor_property

sp_changemergesubscription

sp_changeqreader_agent

sp_changereplicationserverpasswords

sp_changesubscriptiondtsinfo

sp_checkinvalidivarticle

sp_copysubscription

sp_create_removable

sp_cycle_errorlog

sp_dbcmptlevel

sp_dbmmonitoraddmonitoring

sp_dbmmonitorchangealert

sp_dbmmonitordropalert

sp_dbmmonitordropmonitoring

sp_dbmmonitorhelpalert

sp_dbmmonitorhelpmonitoring

sp_dbmmonitorresults

sp_dbmmonitorupdate

sp_dbremove

sp_drop_agent_parameter

sp_drop_agent_profile

sp_dropdatatypemapping

sp_dropdistpublisher

sp_dropdistributiondb

sp_dropdistributor

sp_dropmergepullsubscription

sp_droppullsubscription

sp_dropsubscriber

sp_dsninfo

sp_enumdsn

sp_flush_commit_table_on_demand

sp_generate_agent_parameter

sp_get_distributor

sp_get_Oracle_publisher_metadata

sp_getagentparameterlist

sp_getdefaultdatatypemapping

sp_grant_publication_access

sp_help_agent_default

sp_help_agent_parameter

sp_help_agent_profile

sp_helpdistpublisher

sp_helpdistributor

sp_helpmergesubscription

sp_helpqreader_agent

sp_helpreplicationdboption

sp_identitycolumnforreplication

sp_IHValidateRowFilter

sp_IHXactSetJob

sp_link_publication

sp_monitor

sp_MSadd_distribution_agent

sp_MSadd_logreader_agent

sp_MSadd_merge_agent

sp_MSadd_snapshot_agent

sp_MSadd_subscriber_schedule

sp_MSadd_tracer_history

sp_MSadd_tracer_token

sp_MScdc_cleanup_job

sp_MScdc_db_ddl_event

sp_MScdc_ddl_event

sp_MSchange_distribution_agent_properties

sp_MSchange_logreader_agent_properties

sp_MSchange_merge_agent_properties

sp_MSchange_snapshot_agent_properties

sp_MSchangedynamicsnapshotjobatdistributor

sp_MSchangedynsnaplocationatdistributor

sp_MScheck_pull_access

sp_MScleanupmergepublisher_internal

sp_MSclear_dynamic_snapshot_location

sp_MScreate_dist_tables

sp_MSdbuserpriv

sp_MSdeletefoldercontents

sp_MSdrop_6x_replication_agent

sp_MSdrop_merge_agent

sp_MSdrop_snapshot_dirs

sp_MSdropmergedynamicsnapshotjob

sp_MSdynamicsnapshotjobexistsatdistributor

sp_MSenumallpublications

sp_MSfetchAdjustidentityrange

sp_MSfix_6x_tasks

sp_MSforce_drop_distribution_jobs

sp_MSget_agent_names

sp_MSget_jobstate

sp_MSget_oledbinfo

sp_MSget_publication_from_taskname

sp_MSgetdbversion

sp_MSgetmaxsnapshottimestamp

sp_MShelp_repl_agent

sp_MShelp_replication_status

sp_MShelp_snapshot_agent

sp_MShelpconflictpublications

sp_MShelpdynamicsnapshotjobatdistributor

sp_MShelplogreader_agent

sp_MShelpsnapshot_agent

sp_MShelptranconflictcounts

sp_MSinit_publication_access

sp_MSreinit_failed_subscriptions

sp_MSremoveoffloadparameter

sp_MSrepl_backup_complete

sp_MSrepl_backup_start

sp_MSrepl_createdatatypemappings

sp_MSrepl_dropdatatypemappings

sp_MSrepl_enumarticlecolumninfo

sp_MSrepl_enumpublications

sp_MSrepl_enumpublishertables

sp_MSrepl_enumsubscriptions

sp_MSrepl_enumtablecolumninfo

sp_MSrepl_getdistributorinfo

sp_MSrepl_startup_internal

sp_MSreplagentjobexists

sp_MSreplcheck_permission

sp_MSreplcheck_pull

sp_MSreplcheck_subscribe

sp_MSreplcheck_subscribe_withddladmin

sp_MSreplcopyscriptfile

sp_MSreplremoveuncdir

sp_MSsetalertinfo

sp_MSSetServerProperties

sp_MSsetupnosyncsubwithlsnatdist

sp_MSsetupnosyncsubwithlsnatdist_cleanup

sp_MSsetupnosyncsubwithlsnatdist_helper

sp_MSstartdistribution_agent

sp_MSstartmerge_agent

sp_MSstartsnapshot_agent

sp_MSstopdistribution_agent

sp_MSstopmerge_agent

sp_MSstopsnapshot_agent

sp_MSupdate_agenttype_default

sp_oledbinfo

sp_procoption

sp_removedbreplication

sp_removesrvreplication

sp_replication_agent_checkup

sp_replicationdboption

sp_resetstatus

sp_restoredbreplication

sp_SetAutoSAPasswordAndDisable

sp_setdefaultdatatypemapping

sp_updatestats

sp_validatelogins

sp_vupgrade_mergeobjects

sp_vupgrade_replication

sp_vupgrade_replsecurity_metadata

xp_repl_convert_encrypt_sysadmin_wrapper

(DE)

Auf dem diesjährigen SQLSaturday in Deutschland habe ich wieder einmal einen meiner Sicherheitsvorträge gehalten, in denen ich mich auf „Angriff“ konzentriere. Für mich eine gute Gelegenheit, mich wieder einmal intensiv mit SQL Server Sicherheit und einigen Penetrationstest-Tools auseinanderzusetzen, und SQL Server auf Fallstricke in Sachen Sicherheitskonfiguration zu untersuchen. Am Ende hatte ich eine lange Liste an möglichen Demonstrationen, worunter auch eine von mir frisch entwickelte DoS-Attacke via SQL Injection zählt (zumindest habe ich nirgends einen Hinweis auf eine Beschreibung dieser Art Angriff gefunden oder auf meine Nachfragen erhalten), sowie eine „privilege elevation“(Privilegienerweiterung)-Attacke, die in dieser Form auch noch unbekannt zu sein scheint. – Alles jedoch unter Ausnutzung von angepassten Einstellungen, und keine Schwächen in der Engine(!).

Da es speziell zu SQL Server kaum nennenswerte Sessions in Deutschland zu diesem Thema gibt (selbst auf den Summits in den USA war ich damit meist recht allein unterwegs), und mir das Thema in dieser Tiefe natürlich auch besonders Spaß macht, habe ich mich entschieden, hier meine möglichen Themen zu sammeln. Ich werde sie nicht nur auf weiteren kommenden Konferenzen in Europa oder USA präsentieren, sondern auch den Regionalgruppen der deutschen PASS anzubieten – die sich hier sozusagen im „Menü“ bedienen können :-)

An einem Abend schafft man vermutlich maximal ein Drittel der möglichen Themen. – Und damit wälze ich nun die Qual der Wahl auf die Kollegen RGVs ab ;-)

(EN)

At this year’s SQLSaturday in Germany I have shown one of my sessions again, in which I concentrate on “attack”. For me a great opportunity to dive deep into SQL Server Security and several penetration-test-tool, and to explore SQL Server for pitfalls and security configuration. At the end I had a long list of possible demonstrations. Among them a just recently developed DoS-attack via SQL Injection (at least I did not find any clue on a description for this kind of attack anywhere or got an answer on my inquiries), as well as a “privilege elevation”, which in this form seems to be quite unknown as well. – Everything is just done by exploiting customized settings and not by weaknesses in the engine (!).

Since there are barely any nameable sessions on this topic specifically for SQL Server in Germany (even at the Summits in the US I tended to be quite alone with my sessions on security), and I enjoy this topic in this a lot, I have decided to collect all possible topics here.
I will not only present them on upcoming conferences in Europe or the US, but also I am offering these to the regional chapter leaders in Germany  – “serve yourself” - style :-)

Session Beschreibung:

SQL Server kann als "secure by default" gelten, aber da das am häufigsten erfolgreich angegriffene Ziel die Daten, die in der Datenbank liegen, sind, möchte ich in dieser Session für das Thema sensibilisieren.
Die meisten der ausgenutzten Schwachstellen in einer SQL Server Umgebung sind auf Miss-konfiguration, schwache Sicherheitseinstellungen oder ungenügende Programmierpraktiken zurückzuführen.

In dieser rein Demo-basierten Session werden verschiedene Angriffsszenarien auf verschiedenen Ebenen gezeigt. Auf speziellen Wunsch auch einige Advanced SQL-Injection Beispiele. Des Weiteren zeige ich, wie eine „privilege elevation“ aufgrund einer nicht unüblichen Einstellung möglich ist und die Ausnutzung von Rechten mit einem Datenbank-Rootkit durch einen "Insider".

In dieser Art Vortrag gebe ich natürlich keine Anleitung "wie man hackt", sondern ich beleuchte häufige Schwachstellen - "Was kann unter welchen Umständen passieren".

Session Description:

SQL Server is considered "secure by default", but one of the most often successfully attacked targets is the data that resides in a Database Server.
Most of the exploited weaknesses in a SQL Server environment are due to misconfiguration weak security settings or inadequate coding practices.

In this purely demo-based security session, I am showing several attack scenarios on different layers. Due to special request this includes some special SQL Injection types. Furthermore I show how an evaluation of privileges attack is possible due to a not uncommon configuration as well as an “insider-exploit” with a database root kit.

Note that in this kind of session I do not give instructions on “how to hack” but rather I am highlighting common weaknesses - “what can happen and under which circumstances”.

(Web)Application Layer

• Mein Formular und die WAF lassen nichts durch – oder doch?
  • Standard SQL Injection
  • Blind / Error-based /Time-based SQL Injection, Encoding Injection
  • 2nd Order SQL Injection
  • Privilege Escalation über SQL Injection und trustworthy
  • Automatisierte Attacken mit Tools, weitere „Features“ (sqlmap,...)
  • “Der Fall der nicht-terminierbaren Transaktion“ -  DoS Attack über SQL Injection (von mir "erfunden" für den SQLSaturday Germany 2013)

• Schwachstellen in Zeilen-basierter Sicherheit
• XML Bomb
• Spaß mit Zeichensätzen
  • Tabellen ohne Namen? – Oder überhaupt keine Tabellen?
    „Garantiert eindeutige Objektnamen“ ;-)

(Web)Application Layer

• My form and the WAF don’t let anything pass through – or do they?
  • Standard SQL Injection
  • Blind / Error-based /Time-based SQL Injection, Encoding Injection
  • 2nd Order SQL Injection
  • Privilege Escalation via SQL Injection and trustworthy
  • automated attacks using tools, further “features” (sqlmap,...)
  • “case of the unkillable transaction” - DoS Attack via SQL Injection ("invented" by me for SQLSaturday Germany 2013)

• SQL Server Row- and Cell-Level Security – Disclosure vulnerability
• XML Bomb
• A bit of fun with collation
  • Tables without a name? Or no tables at all?
    “guraranteed unique object names” ;-)

Innerhalb des Netzwerk

• Aufklärung: Erkennen von SQL Server Instanzen
• SQL Authentifizierung
• Beobachten von SQL Traffic (Login + Select)
• Automatisierte brute-Force Attacke gegen sa-Passwörter

Inside the Network

• Reconnaissance: Detecting SQL Server Instances
• SQL authentication
• Watching SQL Traffic (Login + Select)
• Automated brute-Force Attack against sa-passwords
  

Server & Datenbank-Ebene – Angriffe von Innen, Teil 1: der böse Consultant

• SQL Authentifizierung
  • Cracken von Passwörtern – möglich? Wie?
  • Auslesen von Passwörtern aus dem Arbeitsspeicher
• Was ein Consultant so hinterlassen kann
  
  • Automatisierte Einrichtung eines SQL Server Rootkits
• „Wenn der Gast die Party wechselt“
  • Umgehen von Denies durch „guest-guest-impersonation“ – erstmalig gezeigt auf dem PASS Summit 2010 in Seattle :-)

Server & database-Level – attacks from inside, Part 1: evil Consultant

• SQL authentication
  
  • Cracking Passwords – possible? How?
  • Reading passwords from memory
• What a Consultant may leave behind
  
  • Automated install of a SQL Server rootkit
• „When the guest switches the party“
  • Circumventing denies by “guest-guest-impersonation“
    – first shown at PASS Summit 2010 in Seattle :-)

Server & Datenbank-Ebene – Angriffe von Innen, Teil 2: der böse Entwickler

• „Kenne Deine Rechte“
  • "Transfer-Schema Attack"
    – erstmalig gezeigt auf dem PASS Summit 2010 in Seattle :-)

• „Alles meins“ – oder nicht?
  • Datenbankbesitzverkettung
  • Db_owner unterschätzt & ausgenutzt
  • Schema-ownership-chaining

Server & database-Level – attacks from inside, Part 2: evil Developer

• „Know your rights“
  • "Transfer-Schema Attack"
    – first shown at PASS Summit 2010 in Seattle :-)
• „Everything belongs to me“ – does it?
  • Database-ownership-chaining
  • Db_owner underestimated & exploited
  • Schema-ownership-chaining

Alias "Apollo ist gelandet"

(Apollo war der Codename für das Columnstore Index-Projekt vor SQL Server 2012)

(DE)

Während In-Memory Processing mit XTP die Grundlage für die Veränderung in der Verarbeitung von OLTP im SQL Server 2014 formt (Update 09-2013: XTP werde ich auf der TechNet Conference erstmalig  in Deutschland präsentieren), wurde für OLAP/Datawarehouse-Systeme die Columnstore Indexe fundamental verbessert. Tatsächlich sind die Neuerungen so gewaltig, das es sich vorhersehen lässt, das Columnstore (spaltenorientiertes Speichern) mit dem SQL Server 2014 der Standard Speichertyp für DataWarehouses wird (zumindest für Systeme, welche die Enterprise Edition verwenden).

Beginnen wir mit einem Blick auf das, was wir mit SQL Server 2012 erhalten und zugleich vermisst haben, als ColumnStore Indexe eingeführt wurden:

aka "Apollo has landed"

(Apollo was the Codename for the Columnstore Index project before SQL Server 2012)

(EN-US)

Whereas In-Memory Transactional Processing with XTP builds the foundation for the shift in processing OLTP in SQL Server 2014, for OLAP/datawarehouse-systems Columnstore Indexes have been improved fundamentally. In fact the improvements are so huge, that it can be foreseen that Columnstore will become the standard type of storage for DataWarehouses starting in SQL Server 2014 (at least for systems running Enterprise Edition).

Let’s start with what we had and missed at the same time so far, in SQL Server 2012 when Columnstore Indexes were introduced:

1. Ein Non-clustered columnstore index (ab hier jetzt abgekürzt als NCCI) pro Tabelle – der geclusterte war weiterhin row-basiert
2. Keine DML Unterstützung: keine Updates (Datenaktualisierung) – größte Einschränkung für nahe-Echtzeit DataWarehouses und ständige Aktualisierungen
3. Mittelmäßiges memory management – z.B. wurde der Resource Governor nicht beachtet
4. Kein Batch hash join spilling
5. Eingeschränkte Datentypen Unterstützung – ok, Ich persönlich habe wenig Probleme damit, da es Entwickler dazu animiert/zwingt, Spalten-Datentypen und Tabellendesign bewusst zu wählen (wir erinnern uns, das, Columnstore Indexe hauptsächlich für Faktentabellen in DataWarehouses gedacht waren), aber das ist nur meine persönliche Meinung dazu :-)
6. Eingeschränkte Batch Operationen unterstützt– ein nicht unterstützter Operator führte zu row-mode-Verarbeitung der gesamten Abfrage

1. One Non-clustered columnstore index (I’ll refer to that as NCCI from now on) per table – the clustered was still row-based
2. No DML support: no updates (data refresh) – biggest bummer for close to real-time DataWarehouses and continuous updating
3. Mediocre memory management – i.e. Resource Governor not honored
4. No batch hash join spilling
5. Limited data types support – ok, I personally have little problems with that as it forced developers to wisely chose column-types and table design (remember, that columnstore indexes were mainly meant for FactTables in DataWarehouses), but that’s just my personal opinion on that :-)
6. Limited batch operations supported – one unsupported plan operator led to row-mode processing of the whole query

Aus dieser Liste bereiten sicherlich die folgenden Dinge die größten Probleme unter SQL Server 2012: Updates auf columnstore indexierte Tabellen sind nur mittels drop NCCI/Daten laden/create NCCI oder partition switching möglich und zum zweiten: viele Abfragen erhalten nicht einmal den vollständigen möglichen performance-Schub durch den NCCI, weil eine nicht unterstützte Operation im Abfrageplan stattfindet.

Auf der TechEd Europe 2013 in Madrid wurde die CTP1 des  SQL Server 2014 als öffentlich verfügbar verkündet (www.insidesql.org/blogs/andreaswolter/2013/06/ctp1-sql-server-2014-release-techedeurope ) und da detaillierte Informationen über die gewaltigen Verbesserungen endlich offiziell sind, kann ich die guten Neuigkeiten mit meinen Lesern teilen (Ein großes Dankeschön an Igor Stanko vom Parallel Datawarehouse-Team bei Microsoft, der alle Fragen im Detail beantwortet hat, während er mit mir und anderen MCMs und MVPs am Microsoft Stand "gefangen" war;-) )

Zu den Fakten:

Out of that list, probably the biggest issues in SQL Server 2012 are, that updates to columnstore indexed tables are only possible by drop NCCI/load data/create NCCI or partition switching and secondly: many queries actually do not get even get all of the possible performance gain from the NCCI because of an unsupported operation inside the query (plan).

At the TechEd Europe 2013 in Madrid CTP1 of SQL Server 2014 was announced publically available (www.insidesql.org/blogs/andreaswolter/2013/06/ctp1-sql-server-2014-release-techedeurope ) and as detailed information on the big improvements are finally official, I can share the good news with my readers (Big Thanks to Igor Stanko from the Parallel Datawarehouse-Team at Microsoft who answered all questions in detail, while "bound" with me and other MCMs and MVPs at the Microsoft booth ;-) )

To the facts:

Im SQL Server 2014,

1. Können wir einen Clustered Columnstore Index (CCI) anlegen
2. sind Clustered Columnstore Indexes mit Standard DML-Operationen aktualisierbar! – das funktioniert über eine delta store und einem tuple mover, der ein einmal volles Segment in columnstore konvertiert
   
3. ist Memory Management dynamisch und Resource Governor Einstellungen werden beachtet
4. unterstützt (Hash Join) Spilling den Batch Mode
5. funktioniert ein Mix von row und batch mode innerhalb einer Abfrageausführung
6. haben wir mehr unterstützte Operatoren im Batch Mode (Outer Join, partial Aggregates, Union All)
7. erhalten wir mit dem neuen Komprimierungstyp
   “COLUMNSTORE_ARCHIVE” weitere 30% Komprimierung für die ausgewählte Tabelle/Partition
8. werden mehr Datentypen unterstützt (decimal mit hoher Präzision, (var)binary, uniqueidentifier)

In SQL Server 2014,

1. We can create a Clustered Columnstore Index (CCI)
2. Clustered Columnstore Indexes will be Updatable with standard DML-Operations! – this works via a delta store and a tuple mover, that converts a once full segment into column store
   
3. Memory management is dynamic and honors resource governor settings
4. (Hash Join) Spilling now supports Batch Mode
5. Mixed row and batch mode inside query execution now works
6. We have more supported operators in batch mode (Outer Join, partial Aggregates, Union All)
7. The new compression type: “COLUMNSTORE_ARCHIVE” adds another 30% compression for chosen table/partition
8. More data types are supported (high precision decimal, (var)binary, uniqueidentifier)

– Diese Verbesserungen in Kombination können schwerlich überschätzt werden, da sie letztlich zu einer Schlussfolgerung führen:

“Columnstore (und clustered columnstore Index) wird die BEVORZUGTE Storage Engine für DW Szenarien”

(das ist ein original Microsoft-Zitat - und ich habe keinen Grund, das irgendwie anders zu auszudrücken)

Hinweis: Das Clustered Columnstore Index Feature wurde von dem Parallel Data Warehouse (PDW)-Team für die PDW Version 2012 entwickelt, welche Anfang 2013 released worden ist. (Danke an Henk van der Valk für die Erinnerung an diesen nicht unerheblichen Fakt.)

Und hier ist ein Vergleich der  möglichen Speicherplatz-Ersparnisse bei der Verwendung von CCI gegenüber allen anderen Indexierungs-Methoden bisher (herausgegeben auf der TechEd 2013 - ich lasse das Diagramm für sich selbst sprechen):

– Those improvement combined can hardly be overestimated as they ultimately lead to one conclusion:

“Columnstore (and clustered columnstore index) will be PREFERRED storage engine for DW scenarios”

(this is an official quote from Microsoft - and I see no need to say it any differently)

Note: The Clustered Columnstore Index feature is was actually developed by the Parallel Data Warehouse (PDW)-Team for the PDW Version 2012 which was released in the beginning of 2013. (Thanks, Henk van der Valk for reminding me of that not so insignificant fact.)

And here is a comparison of the possible space savings when using CCI over any other indexing methods so far (released at TechEd 2013 - I'll let the graph speak for itself):

Nach all den guten Neuigkeiten: Ist da noch etwas, was uns fehlt, bei der Verwendung von ColumnStore? Ich möchte auch die wenigen Dinge, die noch schmerzen können, nicht vergessen:

1. keine Constraints, die Indexe verwenden, werden unterstützt, noch nicht einmal Fremdschlüssel ohne Index. Wenn solchermaßen benötigt wird, muss man zum hybriden Ansatz mit NCCIs wechseln und Partition switching oder eine andere Methode wie zuvor verwenden.
2. Auch DML-Trigger sind mit CCIs nicht möglich.
3. Nur der Vollständigkeit halber: keine anderen Indexe sind möglich, wenn ein CCI auf einer Tabelle vorliegt. Aber dafür wäre auch keine Verwendung mehr, wie der aufmerksame Leser bis hier sicherlich erkannt hat :-)

After all the good news: Is there anything that we might still miss when using ColumnStore? I do not want to forget to mention the few things that might still hurt:

1. No constraints using index are supported, not even Foreign Keys without index. So if such is needed, one has to revert to using a hybrid approach with NCCIs and using partition switching or any other of the methods as before.
2. Also DML-Triggers are not possible with CCIs.
3. Just for completeness: no other indexes are possible when a CCI is present on a table. But there is no use for it anyways, as the attentive reader might have noticed by now :-)

Als Datawarehouse-Architekt bin ich begeistert und freue mich auf die ersten Implementierungen von DataWarehouses unter SQL Server 2014. Das Index-Design wird viel einfacher, in einigen Umgebungen sogar trivial. - Man möge beachten, dass ich nicht "Database-Design" schrieb: Ein geeignetes Datenbank-Design (Hinweis: "Star-Schema") ist weiterhin notwendig um die maximal mögliche Performance herauszuholen - nicht vergessen, dass Batch-Mode-Verarbeitung und Segment-elimination die Schlüssel zum Maximum sind.

So, as a datawarehouse-architect I am really excited and looking forward to the first implementations of Datawarehouses under SQL Server 2014. The index-design will become much easier, in fact in many environments even trivial. - Note, that I am not using the word “database-design” itself: A proper database-design (hint: “star-schema”) is still necessary to get the maximum performance gain possible – don’t forget that batch mode processing and segment elimination are the keys to the max.