Category: "Security"
Dieser Blog ist umgezogen // This Blog has moved: http://andreas-wolter.com/blog/
Sep 20th
http://andreas-wolter.com/blog/
|
Liebe Leser |
Dear Readers |
|
Die aufwändige Mehrsprachigkeit (Deutsch und Englisch professionell manuell übersetzt) wird beibehalten – aber Layout-technisch anders gelöst. Damit dürfte ich immer noch den einzigen mehrsprachigen IT-Blog weltweit betreiben. |
The complex multilingualism (German and English professionally manually translated) is being continued – but solved differently in terms of layout. With that I most likely still operate the only multilingual IT-Blog worldwide. |
|
Mein aktueller Artikel, der erstmalig ausschließlich auf der neuen Website zu finden ist, lautet: Optimieren von Workflows mit In-Memory und nativ kompilierten Objekten - oder wie es nicht funktioniert |
My currently last article, which is exclusively available at the new website for the first time, is Optimizing workflows with In-Memory and Natively Compiled Objects - or how it does not work |
Cu at my new Blog
Andreas
One day of „In-Memory Technologies in SQL Server – From 0 to Operational Analytics Master“ at Data Platform Summit 2017 in Bangalore/India and further conferences this summer
Jul 11th
Ein Tag “In-Memory Technologien im SQL-Server – von 0 zum Operational Analytics Master” beim Data Platform Summit 2017 in Bangalore/Indien und auf weiteren Konferenzen diesen Sommer
|
(EN) I am personally very honored and happy to be asked again to present this PreCon - especially after last year’s drop out due to “Delhi Belly” – which only sounds cute, but was zero fun at all, I can assure my fellow readers. The Indian community has embraced me with very warm welcoming from the very first year of “SQL Server Geeks Summit” on (as it was then called), and I know this event will be a joy as before for sure. |
(DE) Es ist längst kein Geheimnis, dass ich in den letzten Jahren sehr aktiv im asiatischen Raum war – hauptsächlich in Singapur, Malaysia und Indien. So wird es wohl auch nicht überraschen, dass ich diesen Sommer einen kompletten Tag PreCon auf der inzwischen in „Data Platform Summit“ umbenannten Konferenz in Indiens Zentrum für Informationstechnologie geben werde: Bangalore, auch bekannt als das Silikon Valley von Indien. Ich fühle mich sehr geehrt und bin glücklich, dass ich wieder gebeten wurde, diese PreCon zu präsentieren – besonders nach meinem Ausfall im letzten Jahr aufgrund von „Delhi Belly“ – was zwar niedlich klingt, aber keinerlei Spaß machte, kann ich meinen Lesern versichern. Die indische Community hat mich seit dem ersten Jahr des „SQL Server Geeks Summit“ (wie es damals noch hieß) mit einem warmen Empfang willkommen geheißen, und ich weiß, dass auch dieses Event wieder viel Freude machen wird. |
|
And what can my audience look forward to? A full day of diving into the latest trend in IT Technology, specifically data storage: In-Memory optimization (storage and computation). If you are still thinking traditional row-store indexes, it is time to level up. Here is your chance for a very low price to learn from the first steps unto the pitfalls of reality: |
Und worauf kann sich mein Publikum diesmal freuen? Auf einen ganzen Tag, an dem in die neuesten Trends in der IT-Technologie eingetaucht wird, spezifisch in Sachen Datenspeicherung: In-Memory-Optimierung (Speicherung und Berechnung). Wenn ihr noch traditionell in Row-basierten Indexen denkt, ist es Zeit, aufzuleveln. Hier ist eure Chance, zu einem sehr günstigen Preis von den ersten Schritten bis zu den Fallstricken der Realität zu lernen: |
|
“In-Memory Technologies in SQL Server – From 0 to Operational Analytics Master” The abstract goes as follows: |
“In-Memory Technologies im SQL-Server – Von 0 zum Operational Analytics Master” Die Beschreibung lautet wie folgt: |
|
Since SQL Server 2016 Service Pack 1, most programming features have been available in all editions, including the two In-Memory technologies: Columnstore Indexes and In-Memory OLTP. Columnstore indexes, which have been existing since SQL Server 2012 (actually PDW v2008), are mainly optimized for big amounts of data (millions of rows) and offer blazingly fast OLAP-style queries, which is made possible by their special structure (columnar storage), sophisticated compression, and batch-mode processing for much more efficient CPU-usage than traditional row-store-queries. The In-Memory OLTP engine, which will be the second topic of this full day, came into the product with SQL Server 2014 and has since then been extensively improved in terms of both scalability and T-SQL language support, taking away many of the relevant limitations for adaption of version 1 in a similar way as the Columnstore technology over the course of its development. Thirdly, the so-called In-Memory Operational Analytics are supported by the possibility to create Columnstore Indexes on memory optimized tables! All those improvements will make In-Memory technologies a viable option in many projects. For Datawarehouses, many say that Columnstore will become the default storage type for all objects in the near future. And it can be foreseen that over the years the same will happen for OLTP-tables that have to support highly concurrent workloads, which will all be based on memory optimized tables. It’s time to extend your skills to embrace those technologies, and learn how to implement and support those new types of storage that are coming to our databases, addressing the challenge of ever more data being stored and queried and performance demands and (real time) analytic requirements going up. During this full-day training session, Microsoft Certified Master for the Data Platform Andreas Wolter, familiar with SQL Server’s In-Memory technologies from the early bits on, will give a complete picture on the current state of technology. Attendees will learn how and where to use either In-Memory OLTP or Columnstore or even both for efficient queries and data storing, but also which problems still exist in real-world projects that sometimes make it hard to find the right solution design to profit from those technologies, and cover the important bits and pieces both from a developer’s and administrator’s perspective. |
Seit SQL Server 2016 Service Pack 1 sind die meisten Programmierfunktionen in allen Editionen verfügbar, einschließlich der zwei In-Memory-Technologien: Columnstore Indexe und In-Memory OLTP. Columnstore Indexe, die es seit SQL Server 2012 (eigentlich PDW v2008) gibt, sind hauptsächlich für große Datenmengen (Millionen von Zeilen) optimiert und bieten blitzschnelle Abfragen im OLAP-Stil, was durch ihre spezielle Struktur (spaltenweise Speicherung), raffinierte Komprimierung und Bearbeitung im Batch-Mode für eine weitaus effizientere CPU-Auslastung als traditionelle Row-basierende Abfragen ermöglicht wird. Die In-Memory OLTP-Maschine, die das zweite Thema dieser Ganztags-PreCon sein wird, kam mit SQL Server 2014 in das Produkt und wurde seitdem umfassend sowohl hinsichtlich Skalierbarkeit als auch T-SQL-Sprachunterstützung verbessert, wobei viele der relevanten Einschränkungen bei der Anpassung von Version 1 auf ähnliche Weise wegfielen wie bei der Columnstore-Technologie im Verlauf ihrer Entwicklung. Drittens werden die sogenannten In-Memory Operational Analytics durch die Möglichkeit unterstützt, Columnstore Indexe auf Memory-optimierten Tabellen zu erstellen! All diese Verbesserungen werden In-Memory-Technologien zu einer praktikablen Option bei vielen Projekten machen. Was Data-Warehouses anbetrifft, sagen viele, dass Columnstore in der nahen Zukunft zur Standardspeicherart für alle Objekte werden wird. Und es lässt sich vorhersehen, dass mit den Jahren dasselbe für OLTP-Tabellen eintreten wird, die hohe gleichzeitige Workloads unterstützen müssen, die dann alle auf Memory-optimierten Tabellen basiert sein werden. Es ist Zeit, deine Fähigkeiten zu erweitern und dir diese Technologien zu eigen zu machen: zu lernen, wie man diese neuen Speichertypen, die in unsere Datenbanken kommen, implementiert und unterstützt, und sich der Herausforderung immer mehr gespeicherter und abgefragter Daten sowie Performance-Anforderungen und steigenden analytischen Anforderungen (in Echtzeit) zu stellen. Während dieses ganztägigen Trainings wird Microsoft Certified Master for the Data Platform Andreas Wolter, der mit den In-Memory-Technologien von SQL-Server von den frühen Anfängen an vertraut ist, einen kompletten Überblick über den derzeitigen Technologiestand geben. Teilnehmer werden lernen, wie und wo man entweder In-Memory OLTP oder Columnstore oder sogar beides für effiziente Queries und Datenspeicherung verwendet. Sie werden auch lernen, welche Probleme noch in realen Projekten existieren, die es manchmal erschweren, das richtige Lösungskonzept zu finden, um von diesen Technologien zu profitieren. Dabei werden auch wichtige Aspekte und Details sowohl aus der Perspektive des Entwicklers als auch der des Administrators behandelt. |
|
Modules/Topics
|
Module/Themen
|
|
Key Takeaways
|
Kernpunkte
|
|
Demos
|
Demos
|
|
Sign-up here as long as seats are available! http://dataplatformgeeks.com/dps2017/pre-conference-seminars/ |
Hier anmelden, solange es noch freie Plätze gibt! http://dataplatformgeeks.com/dps2017/pre-conference-seminars/ |
|
After the PreCon I will give 2 more sessions at DPS 2017: Troubleshooting Availability Groups |
Nach der PreCon werde ich 2 weitere Sessions bei der DPS 2017 geben: Troubleshooting Verfügbarkeitsgruppen und |
|
Also, I have been asked by KD, the founder of KDSSUG (Knowledged Dedicated SQL Server User Group) to present at their Event, “KDSSG MSSQL Tech Unite 2017” on August 20. Session topic TBD. |
Außerdem wurde ich von KD, dem Gründer von KDSSUG (Knowledged Dedicated SQL Server User Group) gebeten, auf ihrem Event „KDSSG MSSQL Tech Unite 2017“ am 20. August zu sprechen. Das Thema wird noch festgelegt. |
|
If Singapore is easier for you: The weekend after, August 26 at SQLSaturday Singapore I will have a session on the new SQL Server 2017 which is due sometime this year: SQL Server 2017: Better HA & DR |
Falls Singapur für euch einfacher sein sollte: Am Wochenende darauf, am 26. August, werde ich auf der SQLSaturday Singapore eine Session zum neuen SQL-Server 2017, der irgendwann dieses Jahr erscheint, halten: SQL Server 2017: Better HA & DR |
|
Next stops after that: SQLSaturday Denmark in Copenhagen October 7 with another full day PreCon Oct. 6th: “Practical Performance Monitoring & Troubleshooting”. Save the date and register soon as my previous events on that subject have quickly filled up. |
Die nächsten Stationen, die folgen werden: SQLSaturday Denmark in Kopenhagen am 7. Oktober mit einer weiteren Ganztags-PreCon am 6. Okt.: “Practical Performance Monitoring & Troubleshooting”. Merkt euch den Termin vor und meldet euch bald an, da meinen vorangegangenen Events zu diesem Thema schnell voll waren. |
The choice is yours ;-)
CU around
Andreas
Where is that Preemptive Wait coming from? Database Ownership and Performance: a journey through SQL Server internals with XEvents, Windbg and Wireshark
Feb 3rd
|
(EN) |
(DE) |
|
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. |
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. |
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) |
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) |
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;
|
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). |
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). |
|
The CMEMTHREAD is an indicator that many threads are contending simultaneously for a thread-safe memory object. 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. 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. 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. (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: |
|
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). |
Und diese wiederum müssen von sqllang!CAutoExecuteAsContext::Set, sqllang!CMsqlExecContext::FExecute verursacht worden sein, und so weiter (grün). |
|
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: |
|
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. |
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. |
|
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 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:
- 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: |
|
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:
|
|
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: |
|
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.) |
|
Behind the Scenes: Network Tracing To explain the difference, I ran a Network Trace using Wireshark |
Hinter den Kulissen: Network Tracing Um den Unterschied zu erklären, führte ich ein Network Trace mit Wireshark aus. |
|
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: |
|
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:
|
|
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:
|
|
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
Apr 15th
مرحبا
|
(“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
Mar 2nd
Vorträge für die großen Konferenzen 2016 eingereicht. Themen: Sicherheit - Performance - In-Memory
|
(DE) |
(EN) |
|
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“. |
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.” |
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
