SQL Server & BI Blog by Andreas Wolter

(DE)
Kaum von den SQLBits XV in Liverpool zurück, auf der ich die Security-Features des SQL Server 2016 vorstellen durfte (SQL Server 2016 - Security Obscurity and Encryption), bin ich im Moment mit der Planung der nächsten SQL Server Master-Class zu Hochverfügbarkeit mit SQL Server (SHA) beschäftigt, die vom 20.-22.6. bei Frankfurt a.M. stattfinden wird.

Hinweis: in den 3 Wochen seit Freischaltung der Anmeldung sind bereits 70% der Plätze vergeben worden. Wer sich noch einen Platz sichern möchte aber nicht weiß, wie schnell seine Einkaufsabteilung es schafft, kann sich gern an mich direkt wenden.
- Mitglieder der PASS Deutschland e.V. erhalten auch diesmal 15% Rabatt.

(EN)
It feels like I have just been back from the SQLBits XV in Liverpool, where I introduced the Security Features of the SQL Server 2016 (SQL Server 2016 - Security Obscurity and Encryption), and next thing I know, I am already in the middle of planning the next SQL Server Master-Class on High Availability with SQL Server (SHA) that is going to take place from 20-22 June near Frankfurt a.M.

Note: in the 2 weeks since activation of registration already 70% of the places have already been filled. If you want to secure a spot but don’t know how you’re your purchase department will make it, you are welcome to contact me directly.
- Members of PASS Deutschland e.V. will once again receive a 15% discount.

Davor noch aber findet, wie die beiden Jahre zuvor, der deutsche SQLSaturday „Rheinland“ in der Hochschule St. Augustin bei Bonn statt.
Diesen unterstützt meine Firma, Sarpedon Quality Lab®, wie auch das Jahr zuvor als Sponsor.

But before, the German SQLSaturday „Rheinland“ is taking place, like the previous two years.
My company, Sarpedon Quality Lab®, will be a sponsor again like last year.

Ich selber werde auch mit einem Vortrag vertreten sein. Diesmal mit einem Beitrag zu ganz klassischer Performance Tracing. Allerdings nicht, wie man immer noch hin und wieder „in the wild“ sieht mit SQL Profiler, sondern natürlich mit aktuellen Technologien wie Extended Events und sogar der neuen Query Store von SQL Server 2016:

I will also be presenting. This time, it is going to be on the entirely classical topic of Performance Tracing. However, not with SQL Profiler as can be seen once in a while “in the wild,” but of course with current technologies such as Extended Events and even the new Query Store by SQL Server 2016:

Analysieren von SQL Server Workloads mit DMVs und XEvents

Diese Session führt durch eine beispielhafte Performance-Analyse unter der Verwendung von DMVs und Extended Events. Wir sehen, wie man eine Top-Down Analyse mit eingebauten Tools durchführen kann und wie man eine feingradige Analyse selbst mittels den „Wait Statistics“ durchführen kann, um Performance-Problemen und Bottlenecks auf die Schliche zu kommen. Zur Identifizierung von Plan-Änderungen wird auch die neue Query Data Store von SQL Server 2016 zum Einsatz kommen. Wer auf dem Feld der Performance-Analysen noch neu ist oder sehen möchte, wie man an die Herausforderung herangehen kann, wird hier praktische Einblicke erhalten, wie man eine Workload analysieren kann. – Die Session ist selbstverständlich „Profiler-free“ ;-)

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.

Asien

Im August bin ich wieder in Asien unterwegs und das zweite Mal in Folge auf der größten SQL Server Konferenz Asiens: dem SQL Server Geeks Summit in Bangalore, Indien.

Asia

In August, I will be traveling Asia again and attending the largest SQL Server Conference in Asia for the second time in a row: the SQL Server Geeks Summit in Bangalore, India.

Dort werde ich dieses Jahr eine ganztägige Pre-Con geben. Thema: Die In-Memory Storage Engines von SQL Server, die mit dem SQL Server 2016 umfangreich verbessert worden. Das heißt es geht um ColumnStore, Memory Optimized Tabellen, Memory Optimized Indexe, und die Kobinationsmöglichkeiten mit traditioneller Row-Store oder auch ColumnStore für Mixed OLAP als auch OLTP workloads.
Es wird super spannend und sehr technisch. Wer sich damit noch nicht beschäftigt hat: es wird höchste Zeit. Die Art und Weise, wie man Datenbanken konzipiert und designt ändert sich zurzeit rapide. Ich bleibe bei meinen Vorhersagen, dass ColumnStore bald der Standard für Datawarehousing sein wird, und In-Memory OLTP der standard für hochperformante OLTP-Szenarien. Hier zeige ich die technischen Hintergründe und Machbarkeiten.

This time, I will be giving a full-day Pre-Con. Topic: The In-Memory Storage Engine of SQL Server that have been extensively improved with SQL Server 2016. That is, it will be about ColumnStore, Memory Optimized Tables, Memory Optimized Indexes, and the combination possibilities with traditional Row-Store or also ColumnStore for Mixed OLAP as well as OLTP workloads.
It will definitely be super exciting and very technical. If you haven’t dealt with this yet: it is about time! The way of conceptualizing and designing databases is changing rapidly at the moment. I am sticking to my predictions that ColumnStore will soon be the standard for Datawarehousing, and In-Memory OLTP the standard for high performing OLTP scenarios.
At this Pre-Con, I will be demonstrating the technical background and feasibility.

Obendrein werde ich noch zwei normalere Sessions auf der Hauptkonferenz geben. Die Themen stehen noch nicht fest.

Added to that I will give two more normal sessions at the main conference. The topics are not final yet.

Nach Indien werde ich auf der SQLSaturday Singapore präsentieren. Diese Konferenz wird bei Microsoft Singapore Operations Pte Ltd, One Marina Boulevard stattfinden – inmitten der berühmtesten Sehenswürdigkeiten Singapurs.

Hier gebe ich möglicherweise auch eine PreCon, aber die Planung ist noch nicht abgeschlossen, also mal schauen, was es sein wird. Auch auf dieses Event freue ich mich sehr.

After India I will be presenting at SQLSaturday Singapore. This event will be held at Microsoft Singapore Operations Pte Ltd, One Marina Boulevard – right in the center of the most famous sights of Singapore.

Also here I might give a PreCon, but the planning is not finalized yet, so let’s see what it will be. I am very much looking forward to this event as well.

(“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 23^rd.

(“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.

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: 

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.

(DE)
Meine letzten Veröffentlichungen und Vorträge zu Reporting Services sind schon Jahre her. Kein Wunder, denn seit dem 2008 R2-Release ist dort nichts passiert. Nun mit SQL Server 2016 erfahren die Reporting Services endlich wieder Zuwendung, wie ich im Mai letzten Jahres bereits am Rande vorangekündigt hatte („SQL Server 2016 – ein Sicherheits- und Performance-Release“). Wird es genug sein für ein Revival? Das kann jeder für sich entscheiden. In diesem Artikel stelle ich vor, was es Neues gibt.

 (EN)
It has been years since my last publications and presentations on Reporting Services. This is no surprise, because since the 2008 R2-release nothing has happened there. With SQL Server 2016, the Reporting Services finally get attention again, as I had preannounced in passing already last May („SQL Server 2016 – the Security & Performance Release“). Will it be enough for a revival? You can decide for yourselves. In this article I will introduce what’s new.

Report-Typen

 Report types

One of the most important innovations is the integration of the DataZen technology. By means of this technology, optimized reports, i.e. “Mobile Reports,” can be developed for mobile devices such as smartphones and tablets. For this purpose, a particular development tool is available, the “Mobile Report Publisher,” which, similar to the Report Builder (in which, aside from the optic, basically nothing has changed, but it performs much better than the old Click-Once application), can store reports locally as well as on the Report Server.

Reporting Services Web Portal

Die offensichtlichste Neuerung bei SSRS ist das neue „Reporting Services Web Portal“, das den alten Report Manager ablöst und auf Html5 basiert. Es unterstützt nun auch Chrome und Firefox vollständig und skaliert automatisch auf allen Bildschirmgrößen. So sieht das neue Portal aus:

 Reporting Services Web Portal

The most obvious innovation in SSRS is the new “Reporting Services Web Portal,” which replaces the old Report Manager and is based on Html5. It now also completely supports Chrome and Firefox and automatically scales to all screen sizes. This is what the new portal looks like:

Hier sieht man auch, dass, zusätzlich zu Ordnern (oben), KPIs und Mobile Reports von den nun sogenannten „Paginated Reports“ (im deutschen „seitenbasierte Berichte) getrennt aufgeführt werden.

Here you can see that in addition to folders (above), KPIs and Mobile Reports are separately listed by the now so-called “Paginated Reports” (i.e. page-based reports).

Das neue Portal unterstützt außerdem nun auch die Möglichkeit, das Design leicht anzupassen. Das nennt sich Custom Branding.
Technisch basiert das Ganze auf einer xml-Datei, die wiederum auf eine json-Datei mit den Farbangaben und optional auf ein logo verweist. Diese 3 Dateien werden als zip-Datei verpackt und dann als „brand package“ im Portal hochgeladen werden. Dabei wird jeweils die vorherige Version einfach ersetzt.

Furthermore, the new portal now also supports the option to slightly adjust the design. This is called Custom Branding.

Technically, it is all based on an xml-file which in turn refers to a json-file containing the color specifications, and, optionally, to a logo. These 3 files are packed as a zip-file and can then be uploaded as a “brand package” in the portal. The corresponding former version is thus simply replaced.

Das Ergebnis im Vergleich zum Original-Design oben:

The result in comparison to the original design above:

So praktisch das ist, und wenngleich es Reporting Services auch ohne Sharepoint-Integration attraktiver macht, so enttäuschend finde ich persönlich, dass das auch alles ist, was in Sachen Design-Standardisierung möglich ist: Berichte sind davon nämlich nicht betroffen, wie ursprünglich erhofft. Dort ist in der Richtung nichts Neues gekommen. :-(

Vielleicht finde ich also doch noch Gelegenheit, meine damals begonnene Blog-Reihe, wie man Berichts-Layout + Design am besten standardisieren und zentralisieren kann, zu vervollständigen. Die hier (Standardizing and Centralizing Report Design (or: creating style sheets for reports) Part 1: The possibilities) von mir vorgestellte Technik ist also tragischerweise immer noch „State of the Art“ für seitenbasierte Berichte.

As practical as this may be, and while it makes Reporting Services more attractive even without Sharepoint integration, I personally find it quite disappointing that this is all that’s possible in terms of design standardization: In fact, unlike initially anticipated, reports are not affected by it. This area has not seen anything new. :-(

Perhaps I will get an opportunity after all to complete my blog series on how best to standardize and centralize report layouts and designs, which I had started back then. Tragically, the technique I presented here (Standardizing and Centralizing Report Design (or: creating style sheets for reports) Part 1: The possibilities) is thus still “state of the art” for paginated reports. 

Mobile Reports have an advantage here: they are covered by these templates – they are considerably more simple and not as variable to begin with, which made it easier to implement.

Weitere Neuerungen im Portal sind die Möglichkeit jegliche Form von Bericht als Favorit markieren und in einem gesonderten Ordner „Favoriten“ wiederzufinden, Export nach Powerpoint, sowie die Abkehr von dem problematischen Active-X Control für den Ausdruck hin zu einem Druck nach pdf:

Further innovations in the portal include the possibility to mark any report form as favorite and find them in a specific “Favorites” folder; Export to Powerpoint; and the moving away from the problematic Active-X Control for printing towards Print to PDF:

Die Abonnements haben einige kleinere Verbesserungen erfahren und lassen sich nun zB. leicht via Knopfdruck deaktivieren. Der Besitzer lässt sich ändern und für die Auslieferung in Dateifreigeben lassen sich nun „Shared Credentials“ verwenden.

The subscriptions have seen some smaller improvements and now can, for example, be easily deactivated at the push of a button. The owner can be changed, and for delivery to fileshares “Shared Credentials” can now be used.

Neuerungen für seitenbasierte Berichte

-           An diese Formulierung zur Abgrenzung von Mobile Reports werden wir uns wohl gewöhnen müssen…

Viel Neues hat sich in der rdl nicht getan.
Aber das Thema „Parameter-Bereich“ wurde angegangen und ist nun deutlich flexibler. Genauer gesagt, die Anordnung der Parameter kann nun freier bestimmt werden, indem ein System an Spalten und Zeilen implementiert wurde, in denen man die Parameter frei anordnen kann.

-           Früher waren ja immer 2 Parameter nebeneinander und einer nach dem anderen ohne Lücke automatisch angeordnet und nur die Reihenfolge bestimmbar.

Nothing much has happened in the rdl.

However, the topic “Parameter area” was tackled and is now considerably more flexible.

More precisely, the arrangement of the parameters can now be freely determined. This was made possible by implementing a system of columns and lines in which the parameters can be freely arranged.

-           Previously, 2 parameters were always arranged next to each other and one after the other without a gap, and only the order could be determined.

With the new system, it is now possible to leave space between individual parameters and arrange them in up to 8 horizontal columns and up to 45 vertical columns – or you can leave a gap of a corresponding number of free fields if you wish. This is what the parameter grid looks like:

Das Grid ist jedoch nicht flexibel. Die Breite der einzelnen Spalten und Höhe der Zeilen ist fix und wird nur bei Bedarf (zu langem Text) erweitert. Auch die Farbe kann man wie zuvor nicht beeinflussen.

However, the grid is not flexible. The width of the individual columns and the height of the cells is fixed and is only increased if necessary (in case of a too long text). As before, the color cannot be changed either.

Daneben gibt es zwei neue Diagramm-Typen: „Tree Map“ und „Sunburst“. Mit ersterem lassen sich Zahlen gut und nach Hichert-Regeln ins Verhältnis setzen. Auch „Heat Maps“ sollten damit deutlich leichter zu implementieren sein. Bisher hat man sich mit spatial Daten und den entsprechenden Karten-Diagrammen beholfen. So kann eine Tree Map aussehen, die Umsätze je Land nach Kategorien verteilt darstellt:

Besides this, there are two new types of diagrams: “Tree Map” and “Sunburst.” With the former, numbers can be put in relation easily and according to Few’s rules. It should also be much more easy to implement “Heat Maps” with it. Prior to this, one had to make do with spatial data and the corresponding map diagrams. This is what a Tree Map that illustrates sales per country, and placed in categories, can look like:

Und hier ein Beispiel für ein „Sunburst“-Diagramm, mit dem sich besonders schöne psychedelische Effekte erzielen lassen. Man sagt ja, dass Visualisierungen großen Einfluss auf Entscheidungen haben können. Mit etwas Knowhow lässt sich das sicher ausbauen… ;-)

Next follows an example of a „Sunburst“-diagram with which especially beautiful psychedelic effects can be achieved. It is said that visualizations may have a great influence on decisions. With a little knowhow this could surely be enhanced… ;-)

Kleiner Spaß…

Der Einsatz ist für Hierarchien geeignet, speziell auch für „unausgeglichene“. Hier ein Standard-Beispiel mit einer unausgeglichenen Hierarchie mit einem Sunburst-Diagramm dargestellt:

Just kidding…

Its application is suitable for hierarchies, and, in particular, also for „ragged“ hierarchies. Below, a standard example of a ragged hierarchy is illustrated with a sunburst-diagram:

Das war’s zu den Neuerungen auch fast schon.

Eines bleibt noch zu erwähnen: Report-Elemente wie Diagramme, Tachos, Karten oder Bilder lassen sich nun auch in Power BI Dashboards integrieren.

Well, that is about all there is on innovations.

One more thing: Report elements such as diagrams, speedometers, maps or images can now also be integrated in Power BI Dashboards.

Zum Abschluss noch einige Links zum Weiterlesen:

In closing, here are a couple of links for further reference:

(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.

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. :-)

(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.

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.

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:

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.

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 principle “schema-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.

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.

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.

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:

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:

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.

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.“

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:

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

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

Standard OLTP

Data Processing (Cleansing etc.):

Data Processing (Cleansing etc.):

DataWarehouses (Source for OLAP-Cubes)

DataWarehouses (Source for OLAP-Cubes)

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

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

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.:
  

Hier noch einige Links zu Artikeln zum Weiterlesen:

Here some links for further reading:

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.

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.