Category: "Security"

Where is that Preemptive Wait coming from? Database Ownership and Performance: a journey through SQL Server internals with XEvents, Windbg and Wireshark

 

(EN)
In this article I will bring together several techniques for troubleshooting a performance- and security-related “phenomenon” I recently noticed when doing some tests with Natively Compiled Stored Procedures.

(DE)
In diesem Artikel zeige ich einige Techniken zum Troubleshooten eines Performance- und sicherheitsrelevanten „Phänomens“, das mir kürzlich aufgefallen ist, als ich ein paar Tests mit Natively Compiled Stored Procedures durchführte.

Background:

In SQL Server 2014, Natively Compiled Stored Procedures did not support EXECUTE AS CALLER but EXECUTE AS “SpecificUser” was required. This user could be specified with “Username”, “Self” or simply “Owner” – in which case the owner of the procedure usually reverts to the schema owner, which mostly reverts to the Database Owner altogether.

Hintergrund: In SQL Server 2014 unterstützten Natively Compiled Stored Procedures EXECUTE AS CALLER nicht, sondern es war EXECUTE AS “SpecificUser” erforderlich. Dieser Nutzer konnte mit “Username”, “Self” oder einfach “Owner” spezifiziert werden – in dem Fall kehrt der Besitzer der Prozedurnormalerweise zum „schema owner“ zurück, was meistens ganz auf den Database Owner zurückkehrt. 

The phenomenon I encountered was that I noticed some pretty strange long execution times when running a workload consisting of a very basic stored procedure that does nothing more than insert one row of data into a small, unspectacular table.

The insert itself should run in less than a second for 1000 rows, but I could see up to 5 seconds.
The body of the procedure looks like this:

Das Phänomen, dem ich begegnete, war, dass ich einige ziemlich seltsam lange Ausführungszeiten bemerkte, als ich eine Workload laufen ließ, die aus einer sehr einfachen Stored Procedure bestand, die nichts weiter tat, als eine Datenreihe in eine kleine, unspektakuläre Tabelle einzufügen.
Das Insert selbst sollte weniger als eine Sekunde für 1000 Zeilen laufen, doch ich konnte bis zu 5 Sekunden sehen.
Der Inhalt der Prozedur sieht so aus:

 

SET NOCOUNT ON;
SET
XACT_ABORT ON;

INSERT UserSchema.TableName
      
(4 columns, int and char)
VALUES
      
(Parameters);

RETURN 0;

 

The analysis:

Since neither the query plan nor “Show Statistics ON” showed anything unusual, I took a step back and started a top-down-analysis with the Waits and Queues Methodology (which, if you are new in this area, has been a proven methodology for performance analysis for over a decade and is explained for SQL Server probably for the first time in detail here: SQL Server 2005 Performance Tuning using the Waits and Queues)
Using the new session-level waits-DMV in SQL Server 2016 (sys.dm_exec_session_wait_stats) I saw the following distribution of waits:

Die Analyse:

Da weder der Abfrageplan noch “Show Statistics ON“ irgendetwas Ungewöhnliches zeigten, ging ich einen Schritt zurück und begann eine Top-Down-Analyse mit der Waits and Queues Methode (die, wenn ihr in diesem Bereich neu seid, eine seit über einem Jahrzehnt bewährte Methode zur Performance-Analyse ist und für SQL Server wahrscheinlich zum ersten Mal im Detail hier erklärt wird: SQL Server 2005 Performance Tuning using the Waits and Queues)
Bei der Anwendung der neuen Session-Level Waits-DMV im SQL Server 2016 (sys.dm_exec_session_wait_stats) sah ich die folgende Waits-Verteilung: 

 

SELECT dm_exec_session_wait_stats.wait_type
       
, SUM(dm_exec_session_wait_stats.wait_time_ms) - SUM(dm_exec_session_wait_stats.signal_wait_time_ms) AS resource_wait_time_ms
       
, SUM(dm_exec_session_wait_stats.signal_wait_time_ms) AS signal_wait_time_ms
       
, SUM(dm_exec_session_wait_stats.wait_time_ms) AS wait_time_ms
       
, MAX(dm_exec_session_wait_stats.max_wait_time_ms) AS max_wait_time_ms
       
, SUM(dm_exec_session_wait_stats.waiting_tasks_count) AS waiting_tasks_count
FROM sys.dm_exec_session_wait_stats AS dm_exec_session_wait_stats
       
INNER JOIN sys.dm_exec_sessions AS dm_exec_sessions
                ON dm_exec_session_wait_stats.session_id = dm_exec_sessions.session_id
WHERE dm_exec_sessions.program_name ='Sarpedon-WorkoadSimulation-InsertProcs'
GROUP BY dm_exec_session_wait_stats.wait_type
ORDER BY max_wait_time_ms DESC;

 WaitsDMV_withCMEMTHREAD

From this list, Latch-contention, blocking and some waiting for IO is to be expected with a very concurrent workload (50 threads trying to insert on the last page).
The wait-types marked red are the ones that caught my attention.
As Preemptive waits are a quite different beast, and I initially thought maybe there was some authentication issue with the client, I decided to take a look at the CMEMTHREAD.

Von dieser Liste sind Latch-contention, Blocking und einiges Warten auf IO bei einer sehr simultanen Workload zu erwarten (50 Threads, die versuchen, sich auf der letzten Page einzufügen).
Die rot-markierten Wait-Typen sind diejenigen, auf die ich aufmerksam wurde.
Da Preemptive Waits ein spezielles Biest sind, und ich anfangs dachte, dass es vielleicht ein Authentifikationsproblem beim Client gab, entschied ich mich, einen Blick auf den CMEMTHREAD zu werfen.  

The CMEMTHREAD is an indicator that many threads are contending simultaneously for a thread-safe memory object.
Now since we are running a highly concurrent workload, I could just leave it at that, but remember: “Never make quick decisions by confusing symptom with cause”. Keep looking for the real cause. (A good article by Paul Randal on exactly this issue: “Avoiding Knee-Jerk Performance Troubleshooting”)

So, the question is, which memory object is so special here.

Der CMEMTHREAD ist ein Indikator dafür, dass viele Threads gleichzeitig um ein thread-safe Memory-Object konkurrieren.
Nun, da wir eine höchst konkurrierende Workload laufen haben, könnte ich es einfach darauf beruhen lassen, doch denkt dran: „Niemals schnelle Entscheidungen treffen, indem man das Symptom mit der Ursache verwechselt.“ Sucht weiter nach der wahren Ursache. (Hier ein guter Artikel von Paul Randal zu genau diesem Thema: “Avoiding Knee-Jerk Performance Troubleshooting”)

Also ist die Frage, welches Memory-Object hier so besonders ist.  

Time for Extended Events

One of the niftiest features of extended events is, that you can get a full stack dump of just a single thread, using the Action “sqlserver.create_dump_single_thread”. ”. Similarly, using “package0.callstack” you can get a callstack of the last 16 frames.
 You can analyze the outcome with Windbg. – Or, using Trace Flag 3656, you can even query the Extended Event targets directly with XQuery from within Management Studio and the callstack will be materialized, provided you have loaded the correct symbols. (Nothing you should do on a production Server!)
(An example how to do that can be found here: Resolving DTC Related Waits and Tuning Scalability of DTC)

With the dump opened in Windbg the following function calls inside the SQL Server engine can be seen:

Zeit für Extended Events

Eine der raffiniertesten Features von Extended Events ist, dass man einen vollständigen Stack Dump von einem einzelnen Pfad erhalten kann, wenn man die Action “sqlserver.create_dump_single_thread” verwendet.
Auf ähnliche Weise kann man einen Callstack der letzten 16 Frames erhalten, wenn man “package0.callstack” verwendet.
Man kann das Ergebnis mit Windbg analysieren – oder, wenn man Trace Flag 3656 verwendet, kann man sogar die Extended Event Targets direkt mit XQuery aus Management Studio heraus abfragen und der Callstack wird materialisiert. (Nichts, das man auf einem Produktions-Server machen sollte!)

(Ein Beispiel dafür, wie das gemacht werden kann, ist hier: Resolving DTC Related Waits and Tuning Scalability of DTC)

Mit dem in Windbg  geöffneten Dump können die folgenden Funktions-Calls in der SQL Server Engine gesehen werden:

 Callstack_Windbg_CMEMTHREAD_GetNtGroupsViaAuthZ

 

The Stack dump rings a bell. Right before allocating the memory (CMemThread<CMemObj>::Alloc (red), several functions with very familiar names were called (orange):

Bei dem Stack Dump läuten doch einige Glocken. Kurz vor dem Zuteilen des Memory (CMemThread<CMemObj>::Alloc (rot) wurden mehrere Funktionen mit sehr bekannt klingenden Namen abgerufen (orange):

sqllang!CWindowsSecurityPrimaryInfo::GetNtGroupsViaAuthZ
sqllang!CreateLoginToken
sqllang!CreateLoginTokenForImpersonation

And those in turn must have been caused by the sqllang!CAutoExecuteAsContext::Set, sqllang!CMsqlExecContext::FExecute and so on (green).
At this point I pretty much knew where to look. But just to show you an example of how this can be done without even touching Windbg, here is an (accumulated and commented) result from the Extended Event callstack-Action:

Und diese wiederum müssen von sqllang!CAutoExecuteAsContext::Set, sqllang!CMsqlExecContext::FExecute verursacht worden sein, und so weiter (grün).
In diesem Moment wusste ich im Prinzip schon, wo ich suchen musste. Aber um euch ein Beispiel zu zeigen, wie man das auch machen kann, ganz ohne Windbg anzurühren, ist hier ein (gesammeltes und kommentiertes) Ergebnis der Extended Event Callstack-Action:  

 

Callstack_XEvent_PREEMPTIVE_OS_LOOKUPACCOUNTSID_edited_2ndstack 

 

This callstack results from the PREEMPTIVE_OS_LOOKUPACCOUNTSID Wait-Type, which I noticed would happen exactly once per procedure call. When analyzing different callstacks together I could draw the conclusion that the following function calls lead to the respective Wait-Types:

Dieser Callstack resultiert vom PREEMPTIVE_OS_LOOKUPACCOUNTSID Wait-Type, was, wie ich bemerkte, genau einmal pro Procedure Call geschehen würde. Beim Analysieren verschiedener Callstacks zusammen konnte ich die Schlussfolgerung ziehen, dass die folgenden Funktions-Calls zu den jeweiligen Wait-Types führen:

sqllang.dll!LookupAccountNameInternal -> PREEMPTIVE_OS_LOOKUPACCOUNTSID
sqllang.dll!CWindowsSecurityPrimaryInfo::GetNtGroupsViaAuthZ -> PREEMPTIVE_OS_AUTHZINITIALIZERESOURCEMANAGER
sqllang.dll!CWindowsSecurityPrimaryInfo::Init -> PREEMPTIVE_OS_AUTHORIZATIONOPS

The Preemptive Waits read from the Extended Events file-target can be ordered in their occurrence (from top to bottom), leading to the following picture:

Die Preemptive Waits, aus dem Extended Events Datei-Ziel gelesen, können nach ihrem Auftreten sortiert werden (von oben nach unten), was folgendes Bild ergibt:

 

WaitsPreemptive_XEvents_inOrder

The here unreadable callstack for the call to AUTHORIZATIONOPS (undocumented) on the bottom.

Der hier unlesbare Callstack für den Call AUTHORIZATIONOPS (undokumentiert) unten. 

 

First outcome:

From those calls one can deduct that not the client is authenticating at SQL Server, but actually the procedure call itself causes Windows API calls (LOOKUPACCOUNTSID, AUTHZINITIALIZERESOURCEMANAGER) for authentication purposes.
Now at the very latest it’s time to check the ownership-chain for the stored procedure.
So I checked the header of the proc, and indeed, just as I expected it contained a “WITH EXECUTE AS OWNER”.
That alone can’t be it, so the question is, who is the owner. I already suspected it and there it was: The database was owned by a LOCAL Windows account.
Now, that is rare, but it all makes sense now: For every execution of the procedure, the ownership had to be verified – and since it is a Windows-Account, it had to go out of SQL Server OS, using a Preemptive call to Windows, wait, and continue only after Windows returns control of this thread back to SQL Server.

Erstes Ergebnis:

Aus diesen Call lässt sich ableiten, dass es nicht der Client ist, der sich bei SQL Server authentifiziert, sondern dass es tatsächlich der Procedure Call selbst ist, der Windows API Calls (LOOKUPACCOUNTSID, AUTHZINITIALIZERESOURCEMANAGER) für Authentifizierungszwecke verursacht.
Spätestens jetzt ist es an der Zeit, die Besitzerkette für die Stored Procedure zu überprüfen. Also habe ich den Kopf der Prozedur geprüft, und tatsächlich, genau wie ich erwartet hatte, enthielt es ein “WITH EXECUTE AS OWNER”.
Das allein kann es nicht sein, also stellt sich die Frage, wer der Owner ist. Ich vermutete es bereits, und da war es: Die Datenbank gehörte zu einem LOKALEN Windows-Konto.
Nun, das ist eher selten, doch jetzt ergibt alles Sinn: Für jede Ausführung der Prozedur musste der Besitz verifiziert werden – und da es ein Windows-Konto ist, musste es raus aus SQL Server OS gehen, indem ein Preemptive Call an Windows verwendet wird, dann warten, und erst dann weitermachen, nachdem Windows die Kontrolle dieses Pfades an SQL Server zurückgegeben hatte.

 

Testing the improvement options:

Now, obviously, I did not stop here but rather made some more tests. Based on the fact that there are 3 possible types of owner for objects within SQL Server, I compared the execution times (and waits) between those 3 possible
Database owner-types:

1.       SQL Account

2.       Windows DomainAccount

3.       Local Windows Account

- I did also test with Admin vs. non-Admin and NTLM vs. Kerberos but saw no difference.

Testen der Verbesserungsmöglichkeiten:

Natürlich hörte ich nicht hier auf, sondern machte noch weitere Tests. Ausgehend von der Tatsache, dass es mögliche Typen von Owner für Objekte innerhalb SQL Server gibt, verglich ich die Ausführungszeiten (und Wartezeiten) zwischen diesen 3 möglichen Datenbank-Owner-Types:

  1. SQL Account
  2. Windows DomainAccount
  3. Local Windows Account

- Ich testete auch mit Admin vs. non-Admin und NTLM vs. Kerberos, stellte jedoch keinen Unterschied fest. 

So here is the result of the Performance-Comparison with different Database-/Procedure-Owners:

Hier ist also das Ergebnis des Performance-Vergleichs mit unterschiedlichen Datenbank-/Procedure-Owners:

 

 ProcedureStats_by_DBOwner

 

Interpretation:

1)      The obvious: Using a local Windows Account results in a huge performance-penalty for such a simple INSERT-statement

2)      Using a Domain Account I could notice that every 10 minutes the next execution would be a bit slower.

Interpretation:

  1. Das Offensichtliche: Die Verwendung eines lokalen Windows-Kontos führt zu eine riesigen Performance-Impakt für ein solch simples INSERT-Statement

  2. Beim Verwenden eines Domain-Kontos konnte ich sehen, dass die nächste Ausführung alle 10 Minuten etwas langsamer war. 

Further analysis

When checking the Wait-stats again, I could see that usually the Windows-Domain-Account had the following simple waits:

Weitere Analyse

Beim erneuten Überprüfen der Warte-Statistiken konnte ich feststellen, dass der Windows-Domain-Account meistens die folgenden einfachen Waits hatte: 

 

WaitsDMV_OwnerDomainAccount

 

Pretty ok and nothing to worry about. But the first call after 10 minutes would always result in the same wait-types as I observed for the local Windows Account, except that the wait-times are much much lower. (You can compare the below picture with the one from the beginning of the article.)

Ziemlich in Ordnung und nichts, worüber man sich Sorgen machen müsste. Doch der erste Call nach 10 Minuten ergab immer dieselben Wait-Typen, die ich für das lokale Windows-Konto beobachtete, außer dass die Wartezeiten um Vieles geringer sind. (Ihr könnt das Bild unten mit dem am Anfang des Artikels vergleichen.)

 

 WaitsDMV_OwnerDomainAccount

 

Behind the Scenes: Network Tracing

To explain the difference, I ran a Network Trace using Wireshark
And exactly every 10 minutes I saw some additional KERBEROS-traffic going to the Domain Controller. Below you see the opened data frame with the Request for a Ticket Granting Service Ticket (TGS-REQ).

Hinter den Kulissen: Network Tracing

Um den Unterschied zu erklären, führte ich ein Network Trace mit Wireshark aus.
Und genau alle 10 Minuten sah ich einigen zusätzlichen KERBEROS-Traffic zum Domain Controller fließen. Unten seht ihr den geöffneten Datenrahmen mit dem Request for a Ticket Granting Service Ticket (TGS-REQ).

 

 WireShark_TGS-REQ

 

Background infos:

As the User, on whose behalf the service requests the service ticket, is identified using the user name and user realm/domain (SQL\SQLDBOwner in my case), the padata type PA-FOR-USER is used, as you can see in the screenshot. You can even see the Checksum added for protection.

The S4U2self extension of the PA-DATA structure allows a service to obtain a service ticket to itself on behalf of a user. The user is identified to the KDC using the user's name and realm.

Hintergrundinformationen:

Wenn der Nutzer, für den der Service das Service-Ticket anfordert, unter Verwendung des Nutzernamens und user realm/Domäne (SQL\SQLDBOwner in meinem Fall) identifiziert wird, wird der Padata Type PA-FOR-USER verwendet, wie im Screenshot zu sehen. Man kann sogar die Checksum sehen, die zum Schutz hinzugefügt wurde.

Die S4U2self Extension der PA-DATA-Struktur erlaubt einem Service, ein Service-Ticket im Auftrag eines Nutzers für sich selbst zu beziehen. Der Nutzer wird gegenüber dem KCD identifiziert, unter Verwendung des Nutzernamen und Realm. 

Luckily the DC answers right away with a TGS-REP, containing the PA-DATA-structure with ticket for the service that was named in the TGS-REQ from above:

Zum Glück antwortet der DC sofort mit einem TGS-REP, der die PA-DATA-Struktur mit einem Ticket für den oben im TGS-REQ genannten Service enthält:

 

 WireShark_TGS-REP

 

There is plenty literature available if you want to learn more about the Kerberos Protocol-Elements. Here is a documentation from Microsoft:

Es gibt eine Menge an Literatur, falls ihr mehr über die Kerberos-Protokollelemente erfahren wollt. Hier ist eine Dokumentation von Microsoft:

The 10-minutes puzzle:

1) After doing some extensive testing, I conclude that the 10-minute interval after which a new Ticket Granting Service Ticket-Request is initiated must be a SQL Server specific enhancement for Domain Accounts to avoid making this round trip for every SID-validation. The 10 minutes are consistent and independent from other workload influencers. The cause behind is not documented.

2) The much faster validation for the AD-Account is very fast thanks to some caching in Windows. (Thank you, Jack Richins from formerly SQL Security Team, for reminding me of this)

3) For the local Windows Account, there is no such performance improvement via caching which results in much slower response times.

Das 10-Minuten-Puzzle:

  1. Nach ausgiebigem Testen schließe ich, dass das 10-Minuten-Intervall, nach dem ein neuer Ticket Granting Service Ticket Request eingeleitet wird, eine SQL-Server-spezifische Erweiterung für Domain-Accounts sein muss, um diese Rundreise nicht für jede SID-Validierung machen zu müssen. Die 10 Minuten sind konsistent und unabhängig von anderen Workload-Beeinflussern. Die Ursache dahinter ist nicht dokumentiert.

  2. Die viel schnellere Validierung für das AD-Konto ist so schnell dank einigem Caching in Windows. (Danke, Jack Richins vom ehemaligen SQL Security Team, der mich daran erinnert hat)

  3. Für das lokale Windows-Konto gibt es keine solche Performance-Verbesserung via Caching, was zu viel längeren Antwortzeiten führt.

 

Final results and learnings:

1)      Using local users for SQL Server can create noticeable impact for short transactions. Yet another reason to stay away from local accounts.

2)      For the SQL Account, naturally no Windows calls are necessary at all, but the performance-advantage compared to the AD-Account is marginal, even for high-speed scenarios.

3)      Network latency matters even between SQL Server and DC. Not just for the initial Login-phase but even for ongoing validations from within SQL-statements.

4)      I can stick to my recommendation for Database Ownerships (SQL Server Database Ownership: survey results & recommendations) :-)

Endergebnis und Gelerntes:

  1. Die Verwendung von lokalen Nutzern für SQL Server kann deutliche Auswirkungen für kurze Transaktionen verursachen. Noch ein weiterer Grund dafür, sich von lokalen Konten fernzuhalten.

  2. Für den SQL Account sind natürlich keine Windows-Calls notwendig, doch der Performance-Vorteil gegenüber dem AD-Account ist geringfügig, selbst für High-Speed-Scenarien.

  3. Netzwerk-Latenz spielt selbst zwischen SQL Server und DC eine Rolle. Nicht nur für die anfängliche Login-Phase, sondern sogar für fortlaufende Validierungen innerhalb von SQL-Statements.

  4. Ich kann meine Empfehlung für Datenbank-Ownerships aufrechterhalten (SQL Server Database Ownership: survey results & recommendations) :-)

I hope you found this an interesting read.

Ich hoffe, dies war eine interessante Lektüre für euch. 

 

Andreas

 

PS: for the geeks among you:

The Stack Dump in Text format together with some comments:

P.S. Für die Geeks unter euch:

Der Stack Dump im Textformat mitsamt einigen Kommentaren:

 

Child-SP          RetAddr           Call Site

000000e9`2f0b79d8 00007ffe`f9e51118 ntdll!NtWaitForSingleObject+0xa

-> this function is actually deprecated (Hello, Microsoft?)

000000e9`2f0b79e0 00007ff7`04999fef KERNELBASE!WaitForSingleObjectEx+0x94
000000e9`2f0b7a80 00007ff7`04999d7d sqlservr!CDmpDump::DumpInternal+0x22f
000000e9`2f0b7b20 00007ff7`049a15b8 sqlservr!CDmpDump::DumpFilter+0xcd
000000e9`2f0b7c10 00007ffe`ef0ef2cb sqlservr!memset+0x1819
000000e9`2f0b7c40 00007ffe`fc98398d msvcr120!__C_specific_handler+0x93 [f:\dd\vctools\crt\crtw32\misc\amd64\chandler.c @ 162]
000000e9`2f0b7cb0 00007ffe`fc9493a7 ntdll!RtlpExecuteHandlerForException+0xd
000000e9`2f0b7ce0 00007ffe`fc948fbd ntdll!RtlDispatchException+0x197
000000e9`2f0b83b0 00007ffe`f9e58b9c ntdll!RtlRaiseException+0x18d
000000e9`2f0b8b70 00007ff7`04999c9c KERNELBASE!RaiseException+0x68
000000e9`2f0b8c50 00007ffe`f05602c6 sqlservr!CDmpDump::Dump+0x4c
000000e9`2f0b8c90 00007ffe`f105c411 sqllang!SQLDumperLibraryInvoke+0x1f6
000000e9`2f0b8cd0 00007ffe`f105ce94 sqllang!SQLLangDumperLibraryInvoke+0x161
000000e9`2f0b8d80 00007ffe`f102cd0b sqllang!CImageHelper::DoMiniDump+0x475
000000e9`2f0b8f90 00007ffe`f072e9c4 sqllang!stackTrace+0x9db
000000e9`2f0ba9b0 00007ffe`f072f6ae sqllang!XeSqlPkg::IsSystem+0x174
000000e9`2f0bab90 00007ffe`ef2e779a sqllang!XeSqlPkg::CreateDumpSingleThreadActionInvoke+0x1e

-> you can clearly see how this stack dump was created: XEvents

000000e9`2f0babc0 00007ffe`ef2a1b8e sqldk!XeSosPkg::wait_info_external::Publish+0x1a9

-> Now we will see our wait-type when we ask the DMVs or XEvents

000000e9`2f0bac20 00007ffe`ef2a63a4 sqldk!SOS_Scheduler::UpdateWaitTimeStats+0x596
000000e9`2f0bb530 00007ffe`f0d94fac sqldk!SOS_Task::PopWait+0xba
000000e9`2f0bb590 00007ffe`f0d9481e sqllang!CWindowsSecurityPrimaryInfo::GetNtGroupsViaAuthZ+0x75c

-> this will lead to the Preemptive Wait: PREEMPTIVE_OS_AUTHZINITIALIZERESOURCEMANAGER

000000e9`2f0bb890 00007ffe`f0d956ed sqllang!CWindowsSecurityPrimaryInfo::Init+0x2ce
000000e9`2f0bba00 00007ffe`f041a88b sqllang!GetWindowsSecurityPrimaryInfo+0xbe
000000e9`2f0bba60 00007ffe`f041cadb sqllang!CreateLoginToken+0x2d7
000000e9`2f0bbc50 00007ffe`f041dca8 sqllang!CreateLoginTokenForImpersonation+0xcb2

-> an Impersonation is happening (EXECUTE AS)

000000e9`2f0bc400 00007ffe`f0358342 sqllang!CAutoExecuteAsContext::Set+0xe2d
000000e9`2f0bc680 00007ffe`f001245e sqllang!CSECExecAsRuntimeServices::_Set+0x424
000000e9`2f0bc900 00007ffe`f0012041 sqllang!CMsqlExecContext::FExecute+0x336
000000e9`2f0bcc30 00007ffe`f0d1d83d sqllang!CSQLSource::Execute+0x983
000000e9`2f0bcdd0 00007ffe`f0d1d241 sqllang!CStmtExecProc::XretLocalExec+0x26e
000000e9`2f0bce50 00007ffe`f0d19f98 sqllang!CStmtExecProc::XretExecExecute+0x481
000000e9`2f0bd600 00007ffe`f00119ca sqllang!CXStmtExecProc::XretExecute+0x38
000000e9`2f0bd640 00007ffe`f0012933 sqllang!CMsqlExecContext::ExecuteStmts<1,1>+0x40d
000000e9`2f0bd780 00007ffe`f0012041 sqllang!CMsqlExecContext::FExecute+0xa9e
000000e9`2f0bdab0 00007ffe`f0cd3f6d sqllang!CSQLSource::Execute+0x983
000000e9`2f0bdc50 00007ffe`f0ce0e6c sqllang!ExecuteSql+0x93d
000000e9`2f0be7d0 00007ffe`f0ce1549 sqllang!CSpecProc::ExecuteSpecial+0x15c
000000e9`2f0be8d0 00007ffe`f001a82b sqllang!CSpecProc::Execute+0x299
000000e9`2f0bea00 00007ffe`f0021542 sqllang!process_request+0xe61
000000e9`2f0befd0 00007ffe`f00210a3 sqllang!process_commands_internal+0x2df
000000e9`2f0bf050 00007ffe`ef2a5bfd sqllang!process_messages+0x253
000000e9`2f0bf260 00007ffe`ef2a58f5 sqldk!SOS_Task::Param::Execute+0x231
000000e9`2f0bf860 00007ffe`ef2a554d sqldk!SOS_Scheduler::RunTask+0xaa
000000e9`2f0bf8d0 00007ffe`ef2cd7c8 sqldk!SOS_Scheduler::ProcessTasks+0x3cd
000000e9`2f0bf9b0 00007ffe`ef2cdb10 sqldk!SchedulerManager::WorkerEntryPoint+0x2a1
000000e9`2f0bfa80 00007ffe`ef2cdcd7 sqldk!SystemThread::RunWorker+0x8f
000000e9`2f0bfab0 00007ffe`ef2cd9f8 sqldk!SystemThreadDispatcher::ProcessWorker+0x2de
000000e9`2f0bfb60 00007ffe`fc6e13d2 sqldk!SchedulerManager::ThreadEntryPoint+0x1d8
000000e9`2f0bfc10 00007ffe`fc9603c4 kernel32!BaseThreadInitThunk+0x22
000000e9`2f0bfc40 00000000`00000000 ntdll!RtlUserThreadStart+0x34

EOF

Sarpedon Quality Lab presenting SQL Server 2016 In-Memory and Security Features in Arabia at SQL Gulf 3


مرحبا


(“MARR-hah-bah”, Arabic: Hello)

This year takes me to yet another part of the world:

I have been invited to speak at THE SQL Server Conference in the Middle East: at SQL Gulf 3 taking place in Riyadh, Saudi Arabia on April 23rd.

I feel very much honored to be among the selected speakers: Denny Cherry, Victor Isakov, Peter Myers, Satya Shyam K and Shehap El-Nagar the organizer himself, who has done a great job pulling this off the third time!
For example about 7 TV stations are expected to cover this event!

(“MARR-hah-bah”, Arabisch: Hallo)

Dieses Jahr bringt mich an eine weitere Gegend dieser Welt:

Ich bin eingeladen worden auf DER SQL Server Konferenz im Nahen Osten: auf der SQL Gulf 3, die am 23. April in Riad Saudi Arabien stattfindet, als Sprecher aufzutreten.

Ich fühle mich sehr geehrt unter den ausgewählten Sprechern zu sein: Denny Cherry, Victor Isakov, Peter Myers, Satya Shyam K und Shehap El-Nagar dem Organisator selbst, der einen tollen Job gemacht hat, das zum dritten Mal zu leisten.
So werden zum Beispiel ca. 7 TV-Sender werden von diesem Event berichten!

 

 

I will be giving two presentations. The first one is on the new In-Memory capabilities of SQL Server 2016, which have been tremendously enhanced, and the second one is on the new Security features which represent one of the pillars of this release:

Ich werde zwei Vorträge halten. Einen über die neuen In-Memory Fähigkeiten von SQL Server 2016, die enorm verbessert worden sind und den zweiten zu den neuen Sicherheitsfeatures, die eine der drei Säulen dieses Releases darstellen: 

 

SQL Server 2016 – the evolution of In-Memory technologies

For SQL Server 2014 a completely new In-Memory Engine for memory optimized table & indexes was integrated into SQL Server with in fact very limited functionality.

For SQL Server 2016 the In-Memory engine is being extensively improved in terms of both scalability and T-SQL language support. Moreover, the ColumnStore index technology has been improved and can now even be combined with memory-optimized tables.

In this session I will provide an overview of the new possibilities and demonstrate where a particular technology may help – or where you cannot expect benefits. If you are planning to go on SQL Server 2016 any time soon, this session shows you two of the most important features that SQL Server 2016 brings.

 

SQL Server 2016 – the Security Release

In this session I will give insights into the most important security features of SQL Server 2016. In fact, this release will introduce completely new features that serve data security on different levels. The top 3 features are: Dynamic Data Masking, Row Level Security, and, as a highlight: Always Encrypted. Also, the new possibilities in Azure will not remain unmentioned either. The session will provide technical insights and demos but also hints to security traps. In the end a system is only as secure as its weakest spot. 
This session aims to assist Administrators as well as Developers in determining the right technologies for their needs.

 

I am looking forward to making many new contacts with people from this region of the world that is striving for modernization in many aspects and already reached the top in several.

Ich freue mich darauf, viele neue Kontakte mit Menschen aus dieser Region, die in vielerlei Hinsicht nach Modernisierung strebt und in einigen bereits Spitzenklasse erreicht hat, zu machen.

 

مع السلامة (Ma’a salama)

Andreas

Sessions submitted for major conferences 2016. Topics: Security – Performance – In-Memory

Vorträge für die großen Konferenzen 2016 eingereicht. Themen: Sicherheit - Performance - In-Memory

(DE)
Nach dem tollen Verlauf der deutschen SQLKonferenz im Februar, wo ich die Ehre hatte, zusammen mit Joachim Hammer, dem Program Manager der Security-Teams für die relationalen SQL Engines bei Microsoft in Redmond die neuen Sicherheitsfeatures des SQL Server 2016 vorzustellen (mehr Infos), habe ich nun endlich Zeit gefunden, die nächsten großen Konferenzen dieses Jahres anzugehen.

(EN)
After the great success of the German SQLKonferenz in February, where I had the honor of presenting the new security features of SQL Server 2016 together with Joachim Hammer, the Program Manager of the security teams of the relational SQL Engines at Microsoft in Redmond (more info), I finally found time to go about the next big conferences this year.

Für den PASS Summit 2016, der wieder in Seattle/USA stattfindet, und auch für den SQLServerGeeks Annual Summit 2016, der in Bangalore/Indien stattfindet habe ich insgesamt 6 Sessions aus den Themengebieten „Sicherheit“, „Performance Analyse“ und „In-Memory“ ausgearbeitet und eingereicht. Dazu kommen 2 ganztägige PreCons zum Thema „Sicherheit“ und „In-Memory“.
Wen es interessiert, zu sehen, was ich diesmal „in Petto“ habe, kann die Abstracts hier einsehen.

For the PASS Summit 2016 which is again taking place in Seattle/USA as well as for the SQLServerGeeks Annual Summit 2016 which is taking place in Bangalore/India, I worked out and submitted 6 sessions altogether from the subject areas “Security,” “Performance Analysis” and “In-Memory.” Added to that 2 full-day PreCons with the topics “Security” and “In-Memory.”
For whoever is interested to see what I have “up my sleeve” this time, can review the abstracts here.

 

Pre-Conferences:

SQL Server Security black belt – attack, protect and keep secure

Security Hardening is a subject which, sooner or later, every DBA will face. Microsoft SQL Server, according to the NIST vulnerability database the most secure RDBMS for years, contains many features that help keep the data secure on different layers. At the same time, ever-new applications which use databases on your servers, support-personnel, deployment-processes, auditors, and other processes and real people are constantly demanding access to your Server.

At this full-day pre-conference you will see how external and internal attackers can gain access to sensitive data. You will then learn how to secure the different attack surfaces of a typical SQL Server, and protect not only Data at Rest but also Data in Use and Data in Transit and learn best practices to prevent common vulnerabilities.

In the second part you will get to know fundamental security principles such as

  • Least Privilege;
  • Segregation of Duties;
  • Reconstruction of Events;
  • Delegation of Authority;

and you will learn how to use built-in functionalities of SQL Server (some limited to v2016) to build your own security frameworks to secure Deployment and Monitoring, separate Job-permissions; how to implement time-based permissions and which techniques can help reconstruct security-relevant events.

If you are in charge of creating or implementing security concepts or need a full picture of attack surface protection and concepts, this session is exactly right for you.

 

In-Memory in SQL Server 2016 – from 0 to Operational Analytics Hero

The Columnstore Index technology came with SQL Server 2012 in the form of Nonclustered Columnstore, and SQL Server 2014 brought us updatable Clustered Columnstore Indexes and a completely new In-Memory Engine for memory optimized table & indexes.

SQL Server 2016 is adding the updatable Nonclustered Columnstore Indexes that can both operate on row store as well as on memory-optimized tables, called In-Memory Operational Analytics. With the In-Memory engine being extensively improved in terms of both scalability and T-SQL language support, In-Memory will become a viable option in many projects.

On this training day, attendees will be given a complete picture on the current state of technology and how and where to use either In-Memory OLTP or ColumnStore or both for efficient queries and data store.

 

General sessions:

Extended Events – The Top Features for efficient Traces

Extended Events, which entered the product in SQL Server 2008, are replacing the old SQL Trace & Profiler - and there are many good reasons for that. In this session you will see a selection of the most fascinating possibilities using this Tracing Framework. If you want to find out how to trace in a flexible and lightweight way, how to do advanced analysis directly inside the GUI, how to audit Database and Table-access without Auditing, how to analyze deadlocks without old-fashioned TraceFlags based on the built-in system_health session, this session is just for you. You will also learn how to use the GUI in an effective way for top-down-analysis and what is possible with some XQuery scripting.

 

Performance Analyzing SQL Server workloads with DMVs and XEvents

This session you will be lead you through an example performance-analysis using mainly DMVs and Extended Events. You will see how a top-down analysis using built-in tools can be conducted. This will include wait statistics on different scopes to identify performance problems and bottlenecks up to identifying query plan changes – with & without using the Query Store of SQL Server 2016. If you are new to performance analyzing this session will give you a practical insight into how to methodically approach performance troubleshooting.

 

SQL Server 2016 – the evolution of In-Memory technologies

For SQL Server 2014 a completely new In-Memory Engine for memory optimized table & indexes was integrated into SQL Server with in fact very limited functionality.

For SQL Server 2016 the In-Memory engine is being extensively improved in terms of both scalability as well as T-SQL language support. Moreover the ColumnStore index technology has been improved and can now even be combined with memory-optimized tables.

This session will provide an overview of the new possibilities and demonstrate where a particular technology may help – or where you cannot expect benefits. If you are planning to go on SQL Server 2016 any time soon, this session shows you two of the most important features that SQL Server 2016 brings.

 

 

SQL Server Security black belt series: Securing Data

You have installed SQL Server and have heard about several “best practices,” maybe renamed the sa account, but now what?

In this session you will see demos of several methods how an attacker can get access to data in Use & in Transit and see which available built-in technologies provide help in mitigating such attacks. You will be given guidance on how to systematically identify possible threats and ne given best practices at hand.

Among the technologies that can be seen are Network sniffing, a Threat Modeling Tool, TDE and the new Always Encrypted technology of SQL Server 2016. This session is mainly targeting Administrators but many concepts and samples should be valuable knowledge for developers as well.

 

SQL Server Security black belt series: Securing Operations

You got SQL Server up and running and thought you could easily secure it by completely denying all access to everybody else except you and your co-admin, but you realize that there are many more individuals demanding access for daily or weekly operations. You have heard about “Segregation of Duties” and “Least Privilege” and are looking into how you can properly implement it with SQL Server.

In this session you will learn about techniques and approaches on how to implement secure processes in order to ensure both “Least Privilege” and “Segregation of Duties” and at the same time “Reconstruction of Events.” Among the techniques shown are “time based-permissions” and custom server roles for performance analysis and job-monitoring.

 

“SQL Attack…ed” – SQL Server under attack via SQL Injection

One of the most frequently attacked targets is the data that resides in a database server. SQL Server is considered “secure by default,” but this is only relevant until the first databases and configurations have been changed. This is why most of the exploited weaknesses are due to misconfiguration or weak coding practices as opposed to security bugs in SQL Server itself, of which we had only a few in the last 10 years.

In this purely demo-based session you will see samples of several real-life attacks, from mere reading up to disrupting service availability via various types of manual and automated SQL Injection, including a broadly unknown elevation of privileges attack for a non-sa account.

If you have a database-server which is accessible by processes beyond your direct control or which even can be reached by some kind of frontend applications, and you are unsure what the possible security implications to watch out for, this session is meant for you.

 

Ich werde natürlich posten, wenn meine Vorträge für 2016 feststehen. Vielleicht sieht man sich ja auf der einen oder anderen Konferenz. :-)

Of course I will post when my presentations for 2016 are fixed. Maybe you can meet me at one or another conference. :-)

 

Andreas

Schema-design for SQL Server: recommendations for Schema-design with security in mind

Schema-Design für SQL Server: Empfehlungen für Schema-Design mit Sicherheit im Blick

(DE)
Einleitung

In diesem Artikel greife ich ein Thema auf, welches ich schon seit vielen Jahren immer wieder in Seminaren, bei Konferenzen und auch in Foren versuche zu vermitteln: Schema-Design. Mit Schema ist hierbei nicht das Datenbankschema mit seinem Tabellen-Design gemeint, sondern die „Datenbank-Objekt-Schemas“, die auch als Namensraum beschrieben werden.

Seit dem Release von SQL Server 2005, nun immerhin über 10 Jahre her, liegt es mir besonders am Herzen, Nutzer darin zu schulen, wie man Schemas richtig verwendet.
Eigentlich ist das auch gar nicht besonders schwierig. So, wie ein Entwickler/Architekt, sich für das ER-Diagramm und spätere Tabellendesign mit den Geschäftsprozessen auseinandersetzen muss, muss man sich für das Schema-Design mit den Datenbank-Zugriffs-Prozessen auseinandersetzen.
Leider jedoch sehe ich auch heute noch jede Woche Datenbanken, die nur das „dbo“ Schema kennen.

(EN)
Introduction

This article picks up a topic I have been teaching time and again in seminars, at conferences and in forums for many years: Schema-Design. Here, schema does not mean the database schema with its tabular design but rather the “database object schemas,” also described as Namespace

Since the release of SQL Server 2005, in fact more than 10 years ago, it is of particular importance to me to train users in correctly using schemas. As a matter of fact, it is not really that difficult. In the same way that a developer/architect has to deal with business processes for the ER-diagram and later tabular design, one has to deal with database access processes for the schema design.

Unfortunately, however, I still see databases every week which only seem to know the “dbo” schema.

Ich gebe zu, der Umfang an Informationen zu diesem Thema ist nicht so umfangreich wie zu den Dauerrennern „Indexing“ und „Performance“. Sicherheits-Härtung ist ein eher lästig empfundener Aufwand, und selten sind Entwickler in solchen Dingen geschult, um die wichtigen Entscheidungen gleich zur Design-Phase zu fällen. Mit diesem Blog-Post, der zugegeben lange überfällig ist, hoffe ich eine gute Referenz schaffen zu können. Denn, zur Entlastung aller Autodidakten, ausgerechnet die bekannte „AdventureWorks“-Datenbank ist alles andere als ein Vorbild in Sachen Schema-Design. Diese Datenbank ist prinzipiell geschaffen, um die neuen Features der SQL Server Versionen seit 2005 demonstrieren zu können, aber nicht immer werden die Konzepte dabei nach Best Practices entwickelt. Zumal das Konzept der Schema-User-Trennung damals noch recht neu war.

I admit that the amount of information on this topic is not as extensive as on the regulars “Indexing” and “Performance.” Hardening Security is an effort perceived as rather annoying, and developers are rarely trained in such areas in order to make the important decisions right at the design stage. With this – admittedly long due – blog post I hope to provide a good reference. Because, to relieve all autodidacts, ironically, the well-known “AdventureWorks” database is anything but exemplary when it comes to schema-design. This database is generally supposed to demonstrate the new features of the SQL Server Versions since 2005, but the concepts are not always being developed according to best practices. Particularly as the concept of the Schema-User separation was a relatively new thing back then.

Hintergrund-Informationen

Bis SQL Server 2000 waren User und Schemas voneinander abhängig, und man hatte nur 2 Möglichkeiten:

1)       alle Entwickler legten alle Objekte in das dbo-Schema

2)       Objekte liegen im Schema mit dem Namen des Entwicklers, also z.B.: „Andreas.Warenkorb“

Der 2. Ansatz war natürlich völlig impraktikabel, abgesehen von Ein-Mann-Entwicklungen. Entwickler waren also mit db_owner(!)-Rechten ausgestattet und dazu angehalten, Objekte bei allen DDL-Befehlen immer mit dbo.Objektname, also als „Two-Part-Name“ zu spezifizieren. Wurde das vergessen, stand plötzlich der Name des Entwicklers vor den Objekten und Kreuz-Referenzen funktionierten dann meist nicht. – Immerhin war der Verursacher dann klar :-)

Wer das alles nicht beachtete, hatte das Problem, dass er Konten von ehemaligen Entwicklern nicht vom Server löschen konnte, da ihnen ja noch Objekte zugeordnet waren, die dann am Ende fest in der Applikation verankert waren.

Und deshalb hat das Security-Team für den SQL Server 2005 das Schema-Konzept komplett überarbeitet, mit dem Ziel, die Delegierung von Rechten zu vereinfachen.
Das dbo-Schema ist im Wesentlichen ein Relikt aus der pre-2005 Welt, welches aus Rückwärts-Kompatibilitätsgründen noch da ist und gleichzeitig als Default-Schema bei der Namensauflösung verwendet wird (ebenfalls wie vorher).

Background Information

Up to SQL Server 2000, users and schemas were independent of each other, and there were only 2 options:

1)       All developers put all objects into the dbo-schema.

2)       Objects are in the schema under the developer’s name, e.g.: “Andreas.Shoppingcart”

The second approach was of course entirely impracticable, putting aside one-man-developments. Developers were thus equipped with db_owner(!)-rights and instructed to always specify objects in all DDL commands with dbo.objectname, hence as “Two-Part Name.” If this was forgotten, the developer’s name would suddenly be in front of the objects, and cross-references would in most cases not work. – At least the causer would be obvious :-)

The person not paying attention to any of it would face the problem that he could not delete accounts of former developers from the server as there were still objects assigned to them and that were in the end firmly anchored in the application.

This is why the Security Team for SQL Server 2005 completely revised the schema concept, with the aim to facilitate the delegation of rights.

The dbo-Schema is essentially a relic from the pre-2005 world, which still exists due to backward compatibility reasons, and which is at the same time used as default schema for name resolution (also like before).

Sinn und Zweck von Datenbank-Schemas

Ich zitiere an dieser Stelle ein Mitglied des Security-Teams: „das Ziel der Trennung von Schemas von Usern war, die Sicherheit zu verbessern – durch die Ermöglichung von Delegierung etc.”

Oder, um das passende Whitepaper „SQL Server Best Practices – Implementation of Database Object Schemas” zu zitieren:

"Ein Schema ist ein eindeutiger Namensraum um die Trennung, Verwaltung und den Besitz von Datenbankobjekten zu erleichtern. Es entfernt die enge Kopplung von Datenbankobjekten und Eigentümern um die Sicherheitsverwaltung von Datenbankobjekten zu verbessern. Database-Objekt-Schemas bieten Funktionalitäten um Anwendungsobjekte innerhalb einer Datenbankumgebung zu kontrollieren und helfen sie zu sichern..."

Soweit zu dem hauptsächlichen Zweck.

Natürlich kann man Schemas auch als Ordnungselement verwenden. Ich möchte sogar einladen dazu, das zu tun. Aber bitte erst an zweiter Stelle, wenn die Sicherheitsgrenzen feststehen.

Im Bild ein Beispiel, in dem mehrere Datenbankprinzipale Objekte in einem gemeinsamen Schema verwenden können:

Aims and benefits of database schemas

At this point, let me quote a member of the Security Team: “the intent with separating Schema from Users was to increase security – to allow more controlled delegation, etc.

Or, to quote the relevant whitepaper, “SQL Server Best Practices – Implementation of Database Object Schemas:”

“A schema is a distinct namespace to facilitate the separation, management, and ownership of database objects. It removed the tight coupling of database objects and owners to improve the security administration of database objects. Database object schemas offer functionality to control and help secure application objects within a database environment...”

So much for the main purpose. Of course, one may also use schemas as organization element. I will even invite you to do that. But please only in second place, once the security borders are set.

The image below illustrates how several database principals can use objects in a common schema:

 

 

Source: On The Horizon: Improved Data Security In SQL Server 2005

Negativ-Beispiel

Sehen wir uns einmal die eingangs angesprochenen Schemas in der AdventureWorks-Datenbank an:

Negative example

Let us now look at the initially mentioned schemas in the AdventureWorks database:

  

 

Auf den ersten Blick mag das schön „ordentlich“ aussehen. Wenn man jedoch genauer hinsieht und überlegt, wie man dort nun Berechtigungen vergeben soll, sieht es eher chaotisch aus.

In allen Schemas gibt es Tabellen und entweder auch Sichten oder Prozeduren oder beides.
Wenn man sich jetzt eine Frontend-Applikation dazudenkt, wo soll diese nun Berechtigungen erhalten?

- Dass db_datareader, db_datawriter und eine „selbsterstellte „db_executor“ o.ä. nicht der Maßstab für diesen Artikel sind, ist sicherlich klar. - Das ist aber durchaus ein valider Ansatz für kleinere Datenbanken, mit wenigen Objekten, oder für Datenbanken, deren Objekte alle wirklich gleichermaßen verwendet werden sollen.

At first sight, this might seem “neat and tidy.” However, once you look closer and start pondering how to assign authorizations, it looks rather chaotic.

In all schemas, there are tables and either also views or procedures or both. Now if one imagines a frontend application in addition, where is it supposed to obtain permissions?

- It is certainly clear that db_datareader, db_datawriter and a “self-created” “db_executor” or the like do not serve as a benchmark for this article. – Yet it is by all means a valid approach for smaller databases with few objects, or for databases whose objects are meant to be used truly equally. 

KISS-Prinzip: „Keep it simple, stupid”

Für Sichten gibt es die SELECT- Berechtigung, bei Ad-Hoc-CRUD-Abfragen auch INSERT, UPDATE und DELETE.
Für Prozeduren genügt die EXECUTE Berechtigung.
Berechtigungen können auf Datenbank- Schema- und Objektebene vergeben werden.
Und natürlich ist die Schema-Ebene die Ebene, die sich anbieten würde, wenn man verhindern möchte, dass man Berechtigungen auf alle Objekte ohne Einschränkung vergibt, aber auch, wenn man sich nicht mit Einzel-Objekt-Berechtigungen herumschlagen möchte.

Hier ist ein Beispiel aus dem offiziellen MOC-Kurs:

KISS-Principle: “Keep it simple and stupid“

For views, there is the SELECT permission, in ad-hoc-CRUD queries there is also INSERT, UPDATE and DELETE.
The EXECUTE permission suffices for all procedures.
Permissions can be assigned on a database-schema and object level. And of course, the schema level is the level that would present itself if the intention is to grant authorizations to all objects without restriction, but also if one does not want to grapple with single-object permissions.

Here is an example from the official MOC course:

  

In diesem Beispiel wird das SELECT-Recht auf alle Objekte in dem Schema „Knowledgebase“ vergeben.
Angewandt auf unser AdventureWorks-Schema-Design, würde das bedeuten, dass wir SELECT-Rechte auf alle Schemas vergeben müssen, in denen Sichten oder Tabellen liegen – mit Ausnahme derer, wo neben Tabellen lediglich Prozeduren liegen, die auch wirklich alle benötigten Operationen auf die im selben Schema enthaltenen Tabellen durchführen – was hier auch nicht der Fall ist.

Im Endeffekt wird man SELECT, INSERT, UPDATE, DELETE-Rechte auf alle Schemas vergeben müssen, zuzüglich einiger EXECUTE-Berechtigungen  auf das dbo-, HumanResources-, Production- und Sales-Schema.

Viel gewonnen hat man damit nicht.

Ein Anwender kann damit auch an den Prozeduren vorbei auf den Tabellen arbeiten, wenn er eine Direkt-Verbindung zur Datenbank aufgebaut hat.

In this sample, the SELECT permission is granted to all objects in the schema “KnowledgeBase.”
Applied to our AdventureWorks schema design, this would imply that we have to grant SELECT rights to all schemas in which there are views or tables – with the exception of those where, besides tables, there are merely procedures that actually execute all required operations against the tables contained in the same schema – which is not the case here either.

At the end of the day, one will have to grant SELECT, INSERT, UPDATE, DELETE rights to all schemas, and, in addition, a couple of EXECUTE permissions to the dbo-, HumanResources-, Production- and Sales-schema.

This does not get us very far.

A user can also work on the tables, past the procedures, after establishing a direct connection to the database.

Schema-Design richtig gemacht

Wie sähe es aus, wenn man es aus der Sicherheitsperspektive richtig macht?

Das ist nicht weiter schwer vorzustellen. SQL Server kennt ja so etwas wie “Objekt-Besitzerverkettung” (“Object-Ownership-Chaining”). Schemas haben einen Besitzer und sind Teil der Kette. Das heißt, solange die beteiligten Schemas den selben Besitzer haben, kann man in einem Schema, „Zugriffobjekte“ (Sichten, Prozeduren, Funktionen) halten, und in einem anderen Schema Objekte (Tabellen), auf die man keinen direkten Zugriff erlauben möchte.

Schema-Design done right

What would it look like if one does it correctly from a security-point-of-view?

It is not that difficult to imagine. In fact, SQL Server knows a thing called “Object Ownership Chaining.” Schemas have an owner and are part of the chain. That means, as long as the schemas involved have the same owner, one can keep “access-object” (views, procedures, functions) in one schema, and, in another schema, objects (tables) to which one does not want to allow direct access.

Das Prinzip hatte ich 2009 auf dem PASS Summit in Seattle im Vortrag „Securing SQL Server from Inside-Attacks“ „Schema-Ownership-Chaining“ getauft.

In my presentation “Securing SQl Server from Inside-Attacks” at the 2009 PASS Summit in Seattle, I dubbed this principleschema-ownership-chaining.”

  

Schema-Ownership-Chaining

Man sieht hier in der (leicht modifizierten) Slide, dass ein User keinen direkten Zugriff auf die Tabellen in dem Schema „Data“ hat, sondern nur über Sichten in dem Schema „Access“ (=„Zugriff“ – daher „Zugriffsschema“). Das funktioniert, weil die Schemas und die enthaltenen Objekte denselben Besitzer haben. Hier „dbo“.

Schema-Ownership-Chaining

In the (slightly modified) slide above, one can see that a user does not have direct access to the tables in the schema “Data” but only via views in the schema “Access” (hence “Access-Schema”). This works because the schemas and the contained objects have the same owner – “dbo” in this case.

Zur SQLRally Nordic, 2012 in Kopenhagen für den Vortrag „SQL Server 2012 Security for Developers“ hatte ich das Konzept noch etwas verfeinert:

For my presentation “SQL Server 2012 Security for Developers” at the 2012 SQLRally Nordic in Copenhagen, I refined this concept a bit more:

 

Best Practices für Schema-Design

Aus dieser Grafik geht noch besser hervor, dass in dem Schema „App1“ keine Tabellen liegen, sondern nur Zugriffscode in Form von Prozeduren, ggf. Sichten. Daher genügt ein EXECUTE-Recht auf dieses Schema, und was immer die Prozeduren durchführen (SELECT, INSERT, UPDATE, DELETE), erfordert keine weiteren Rechte – schon gar nicht auf den Tabellen, hier im Schema „Sales“, selber.

Und noch ein zweiter Ansatz wird hier ersichtlich: Das Denken an „Prozesse“ bzw. hier Applikationen.
In vielen Datenbanken muss eine Applikation nicht wirklich auf alle Tabellen zugreifen.
Spätestens sobald mehrere Applikationen mit einer Datenbank arbeiten, wird ersichtlich, dass das „Ordnungs-Konzept“ einem im Wege steht. Idealerweise erschafft man also für jede Applikation ein eigenes Schema, das genau die Prozeduren enthält, die diese verwenden soll. Für Ad-Hoc Zugriffe, die leider oft für Code-generatoren benötigt werden, kann man das auch mit Sichten machen.
Und es hindert einen niemand daran, ein „gemeinsames Schema“ zu erschaffen, in dem Code (Prozeduren und Sichten) liegt, der von beiden verwendet wird.

Im Endeffekt kommt man so mit wirklich einer Handvoll Rechten aus, und genügt dennoch den Prinzipien „Least Privilege“ (Geringstmögliche Rechte) sowie „Separation of Duties“ (Funktionstrennung).

Best Practices for Schema-Design

The above graphic illustrates even better that there are no tables in the schema “App1” but only access code in the form of procedures, and, where applicable, views. As a result, one EXECUTE permission is enough for this schema, and whatever the procedures are executing (SELECT, INSERT, UPDATE, DELETE) does not require any further rights – especially not on the tables themselves, here in the schema “Sales.”

A second approach becomes evident here: Considering “processes” or, in this case, applications.

In many databases, an application must not really access all tables. At the latest when several applications are working with one database it becomes apparent that the “order-concept” represents an obstacle. Ideally, then, for each application one schema is created that contains exactly those procedures the former is supposed to use. For ad-hoc-accesses, unfortunately often needed for code generators, it is also possible to do this with views.
In fact, no one keeps you from creating a “common schema” in which there is the code (procedures and views) that is used by both.

Ultimately, one manages with just a handful of rights in this way and still fulfills the principles of “Least Privilege” and “Separation of Duties.”

Hinweis zu Objektbesitzer und durchbrochene Besitzverkettung

Achtung: die Besitzverkettung kann auf allen Ebenen, d.h. Schema, Prozedur, Sicht oder Tabelle durchbrochen werden. Das passiert auch, wenn man den Besitzer einer Tabelle ändert, wie im Folgenden dargestellt.

So kann man die Besitzer der beteiligten Schemas und Tabellen abfragen:

Note on Object Owner and broken Ownership Chains

Attention: the ownership-chains can be broken on all levels, i.e. schema, procedure, view or table. This also occurs when the owner changes a table, as demonstrated below.

This is how you can prompt the owners of the schemas and tables involved:

 

SELECT schema_id, name, principal_id FROM sys.schema
    WHERE name = 'Person'

SELECT name, principal_id, schema_id FROM sys.tables
    WHERE name = 'Address'

 

 

Eine Principal_id von NULL bedeutet, dass die Tabelle dem Schema_owner gehört. Das Schema „Person“ gehört dem dbo (principal_id=1)

Ändern des Besitzers der Tabelle:

A Principal_id of NULL means that the table is owned by the Schema_owner. The schema “Person” is owned by the dbo (principal_id=1)

Altering the owner of the table:

 

ALTER AUTHORIZATION ON [Person].[Address] TO db_owner

An dieser Stelle ist die Besitzverkettung unterbrochen.

Und nicht nur das: auch sämtliche Schema-Berechtigungen werden dadurch zurückgesetzt!

Und so setzt man sie auf den Schema-Besitzer zurück – das ist besser, als den Besitzer explizit auf dbo (oder einen anderen Prinzipal) zu setzen:

At this point, the ownership chain is broken.

Not only that: also, as a result all schema permissions are reset!

And this is how to reset them to the schema owner – which is better than explicitly assigning the owner to dbo (or another principal):

 

Ergebnis:

Result:

 

AdventureWorks-Schema korrigierte/sicherheits-optimierte Fassung

Nachdem das Konzept nun klar ist, versuchen wir es mal mit der AdventureWorks-Datenbank. Im Folgenden mein Vorschlag:

AdventureWorks-Schema corrected/security-optimized version

Now that the concept is clear, let’s give the AdventureWorks-database a shot.
My suggestion is as follows:

 

Die Sichten liegen nicht mehr zusammen mit Tabellen, damit ein SELECT-Recht auch wirklich nur Sichten betrifft. Neu ist das Schema „WebShop“, was beispielhaft für eine Applikation gedacht ist, die eben alle Prozeduren aufrufen und Sichten verwenden darf, die dafür programmiert worden sind.

Das dbo-Schema ist jetzt leer und bestimmte Objekte, Log-Tabellen z.B. liegen im Admin-Schema. Man kann diese auch im dbo-Schema belassen, aber man muss bedenken, dass dieses immer als Default-Schema bei der Namesauflösung verwendet wird.

The views are not together with tables anymore so that a SELECT right is sure to concern views only. What is new is the schema “WebShop,” which is meant exemplary for an application that is permitted to call all procedures and use views programmed for this purpose.

The dbo-Schema is now empty, and particular objects, e.g. log-tables are in the Admin-Schema. It is also possible to leave them in the dbo-Schema, but it is important to consider that this is always used as a default-schema for name resolution.

Ausführungskontext und durchbrochene Besitzverkettung

In manchen Szenarien kann eine durchbrochene Besitzverkettung auch gewollt sein. Um dennoch bestimmten Modulen Zugriff auf Daten im Zielschema zu gewähren, ohne das Zielschema selber mit Berechtigungen freizugeben, kann man mit der „EXECUTE AS-Klausel“ arbeiten.
Die Umsetzung kann dann schematisch so aussehen:

Execution-Context and broken ownership chains

In some scenarios, a broken ownership-chain can be intentional. In order to still grant particular modules access to data in the target schema without opening the target schema itself with permissions, one can work with the „EXECUTE AS-Clause.“

The implementation can look like this schema:

  

Empfehlungen für Schema-Design

Im Folgenden einige Ansätze, wie man bei der Entscheidung für Schema-Design vorgehen kann.

Das Idealmaß wäre eine Struktur nach Prozess oder Anwendung (Unter Sicherheitsfachleuten auch „Rolle“)

Beispiel:

Recommendations for Schema-Design

In the following, I will present a few approaches to the decision-making for schema-design.

The ideal measure would be a structure according to process or application (also known as “role” among security experts).

Example:

 Process1.Objects, Process2.Objects, Data(1-n)

Weitere Beispiele für bestimmte Szenarien, wie sie Sarpedon Quality Lab® teilweise so auch seit Jahren in Kundenprojekten implementiert und in Schulungen gezeigt:

Standard OLTP

Further examples for certain scenarios, as also partly implemented in customer projects and shown in seminars by Sarpedon Quality Lab® for years:

Standard OLTP

 

  • Administration (Log-Tables etc.)
  • DataPublic
  • DataInternalOnly (if DB is used by different Apps, some public, some only for internal staff)
  • (Web)App(1)
  • (Web)App(2)
  • Reporting (prefer own code-only DB!)

Data Processing (Cleansing etc.):

Data Processing (Cleansing etc.):

 

  • Import (raw imported data)
  • Dev (unfinalized code)
  • Data (final, cleaned data)
  • Access (Views/Procs for accessing the data)

DataWarehouses (Source for OLAP-Cubes)

DataWarehouses (Source for OLAP-Cubes)

 

  • DimData (saves the old-fashion prefix „dim“/“fact“)
  • FactData (…)
  • vDim (for denormalized, star-schema-Dimension views)
  • vFact (for the MeasureGroups)
  • … other Housekeeping, Reporting, ETL -Schemas

Für lediglich interne Datenbanken kann man auch folgenden Ansatz verwenden:

For merely internal databases it is also possible to use the following approach:

 

  • By Owner:
    • DeveloperA.Objects
    • DeveloperB.Objects
    • By Structure:
      • Subproject1.Objects
      • Subproject2.Objects

Weitere Tipps:

  • Anstelle “dbo” empfehle ich einen expliziten “User without Login“ anzulegen und als dedizierten Schema-Besitzer zu verwenden.
  • Die folgenden Schemas sind „backward compatibility schemas“, die man getrost löschen kann. Man kann sie nur löschen, wenn sie nicht verwendet werden, von daher gibt es da kein Risiko – und sie anzufangen zu verwende, davon rate ich natürlich auch ab.:

 Further hints:

  • In place of „dbo,” I recommend setting up an explicit “User without Login” and using it as dedicated schema-owner.
  • The following schemas are “backward compatibility schemas” which one can safely delete. They can only be deleted if they are no longer used, which is why there is no risk – and of course, I advise against starting to use them.:

 

  • db_owner
  • db_accessadmin
  • db_securityadmin
  • db_ddladmin
  • db_backupoperator
  • db_datareader
  • db_datawriter
  • db_denydatareader
  • db_denydatawriter

Hier noch einige Links zu Artikeln zum Weiterlesen:

Here some links for further reading:

 SQL Server Best Practices – Implementation of Database Object Schemas

Ownership and User-Schema Separation in SQL Server

On The Horizon: Improved Data Security In SQL Server 2005

Schema-Based Access Control for SQL Server Databases

Aufruf an Entwickler

An dieser Stelle vielen Dank fürs Lesen. Der erste Schritt ist damit getan: sich mit der Thematik Schema-Design überhaupt auseinanderzusetzen.

Ich würde mir wünschen, dass das auch zu einem Umdenken führt, und ich mehr durchdachte Schema-Verwendungen sehe. Und dazu kann auch eine sinnvolle logische Aufteilung aus Ordnungszwecken gehören – idealerweise in Kombination mit einem Zugriffsschema. Aber alles ist besser als sämtliche Programme direkt in C:\Programme\ abzulegen – Unterordner sind dort ja auch Usus.

Call to developers

First of all, thank you very much for reading. The first step has thus been taken: to generally engage with the issue of Schema-Design.

It is my hope that this also leads to a rethinking and that I am going to see more well-designed schema-uses. And a practical, logical partitioning for the purpose of order can be part of this – ideally combined with an access-schema. But anything is better than saving all programs directly in C:\Program Files\ – there, subfolders are also common practice.

 

Happy Schema-Designing

Andreas

Acknowledgement

Special thanks to Jack Richins and Steven Gott from the Security Team in Redmond for reminding me of some aspects to add and allowing me to quote them in my article.

SQLKonferenz in Darmstadt: Vorstellung der Security Features von SQL Server 2016 mit dem Leiter des Security-Teams aus Redmond

 SQLKonferenz in Darmstadt: Presenting the Security Features of SQL Server 2016 with the program manager of the security-teams from Redmond

(DE)
Dieses Jahr findet vom 23.-25. Februar die 3. SQLKonferenz in Darmstadt statt.

Und dieses Jahr ist für mich gleich in zweierlei Hinsicht ein besonderer Höhepunkt:

Zum einen ist meine Firma, Sarpedon Quality Lab®, erstmalig Silber-Sponsor der Konferenz. Damit unterstütze ich die PASS Deutschland e.V., der deutschen SQL Server Community, der ich seit vielen Jahren sehr verbunden bin, und in welcher ich auch seit einigen Jahren als einer der 3 Regionalgruppenverantwortlicher der Region Rheinland engagiert bin.

Und das Highlight schlechthin, seit ich 2009 das erste Mal in Seattle auf dem PASS Summit vor großem Publikum Wege zeigte, SQL Server sicherheitstechnisch auszuhebeln, ist:

(EN)
This year, from 23 to 25 February, the 3rd ”SQLKonferenz” takes place in Darmstadt.

And this year represents a particular peak for me in two regards:

For one, my company, Sarpedon Quality Lab®, is for the first time Silver Sponsor of the conference. In this way, I am supporting PASS Deutschland e.V., the German SQL Server Community, with which I have been closely connected for many years, and in which I have also been involved as one of the 3 regional chapter leaders of the Rheinland Region for a couple of years.

And the highlight per se – ever since I had for the first time demonstrated to a large audience at the PASS Summit in Seattle in 2009 how to break through SQL Server in terms of security – is: 

Joachim Hammer, der Leiter der Security für die SQL Engines bei Microsoft in Redmond (also SQL Server und auch der In-Memory Engine XTP, Azure, APS,…) wird mit mir zusammen die neuen Sicherheitsfeatures des bald erscheinenden SQL Server 2016 vorstellen!

Das ist eine große Ehre für mich. Ich freue mich, dass ich ihn dafür gewinnen konnte, unsere Konferenz in Deutschland zu besuchen und sich in einer gemeinsamen Session dem kritischen deutschen Publikum zu stellen.

Die Vortrags-Agenda:

Joachim Hammer, the Head of Security for the SQL Engines at Microsoft in Redmond (i.e. SQ Server as well as the In-Memory Engine XTP, Azure, APS, …) will be introducing with me the new security features of SQL Server 2016, which will be released soon!

This is a great honor for me. I am happy that I was able to convince him to attend our conference in Germany and to be available to the tough German audience in a joint session.
The sessions agenda:

 

SQL Server 2016 – The security release – News from Redmond

Joachim Hammer, Leiter des Security-Teams für die relationalen SQL Engines in Redmond, und Andreas Wolter, Microsoft Certified Solutions Master der Data Platform, geben in dieser Session Einblicke in die wichtigsten Sicherheits-Features von SQL Server 2016. Denn dieses Release wird komplett neue Features einführen, die auf verschiedenen Ebenen der Datensicherheit dienen. Die Top 3: Dynamic Data Masking, Row Level Security und das Highlight: Always Encrypted. In dieser Session gibt es technische Einblicke und Demos, aber auch Hinweise auf Sicherheits-Fallstricke sowie die einmalige Möglichkeit, Fragen zu Implementierungsdetails und dem Entwicklungsprozess direkt an den Entwicklungschef zu stellen. Die Session soll Administratoren als auch Entwickler bei der Entscheidungsfindung für die richtige Technologie unterstützen.

Der Vortrag wird in englischer Sprache stattfinden, Fragen können aber problemlos live übersetzt werden. Nutzen Sie diese einmalige Möglichkeit den Program Manager der Security Teams direkt Fragen stellen zu können.

 

In this session, Joachim Hammer, Program Manager of the Security-Team for the relational SQL Engines in Redmond, and Andreas Wolter, Microsoft Certified Solutions Master for the Data Platform, will give insights into the most important security features of SQL Server 2016. In fact, this release will introduce completely new features that serve data security on different levels. The top 3 features are: Dynamic Data Masking, Row Level Security, and, as a highlight: Always Encrypted. The session will provide technical insights and demos but also hints to security traps as well as the unique opportunity to address questions regarding implementation details and the development process directly to the head of development.
This session aims to assist Administrators as well as Developers in determining the right technologies for their needs.

 

 

Zeitpunkt des Vortrages: 24.2. 13:30-14:30

Wer es irgendwie einrichten kann: lasst es Euch nicht entgehen, Fragen direkt an den Program Manager für SQL Security zu stellen!

Zusätzlich wird es am 25.2. noch eine spezielle Panel Discussion zum Thema Security in SQL Server geben, die ich moderieren werde. Auch hier wird Joachim Hammer sicherlich der Star-Gast sein. Die anderen Gäste verrate ich noch nicht, da es teilweise eine Überraschung sein soll. :-)

Time of the presentation: 24 Feb., 1:30-2:30 pm.

For those who can make it work somehow: don’t miss out on the chance to pose questions directly to the Program Manager for SQL Security!

In addition, on 25 Feb. there will be a special Panel Discussion on Security in SQL Server, which I will be chairing. Here, too, Joachim Hammer will certainly be the star guest. I won’t tell who the other guests are, as it’s kind of supposed to be a surprise.  :-)

Das Thema In-Memory wird dieses Jahr übrigens von meinem geschätzten Kollegen Guido Jacobs, ebenfalls Microsoft, übernommen werden. In seinem Vortrag, der sich direkt meinem anschließt, geht es um „Operational Analytics“, das der Begriff für die im SQL Server 2016 mögliche Kombination aus In-Memory OLTP mit ColumnStore Technologie ist. Mein Tipp: es lohnt sich, sich damit nun zu befassen. Die XTP-Engine ist im SQL Server 2016 wirklich wesentlich ausgereifter und für viel mehr Workloads und Datenbankdesigns verwendbar.

Hier geht’s zur kompletten Agenda der SQLKonferenz:

This year, the topic In-Memory will be taken over by my distinguished colleague Guido Jacobs, also from Microsoft. His presentation, which will follow right after mine, is on “Operational Analytics:” the term for the combination of In-Memory OLTP with ColumnStore Technology that is possible in SQL Server 2016. My advice: it is worth dealing with it now. The XTP-Engine is far more sophisticated in SQL Server 2016 and applicable for many more workloads and database designs.   

Here you can find the complete agenda of the SQL Conference:

 

sqlkonferenz.de/agenda.aspx

Ich freue mich auf interessante Gespräche. Und diesmal wird es leichter sein, mich zu finden: wenn ich nicht gerade in einem Vortrag bin, sollte ich am Stand von Sarpedon Quality Lab® zu finden sein :-).

I am looking forward to interesting discussions. This time, I will be easier to find: unless I am sitting in on a presentation, or presenting myself, you should find me at the Sarpedon Quality Lab® booth :-).

 

 

1 3 4