SQL Server & BI Blog by Andreas Wolter

(DE)
Ziemlich genau ein Jahr, nachdem die seit 5 Jahren ersten Sicherheits-Bugs in SQL Server gefixt werden mussten, gibt es nun, seit 14.7.2015, wieder einen Grund, seine Security-Patching-Policy zu testen.

- Wenn man denn eine solche für SQL Server hat.

Und damit möchte ich jetzt niemanden anprangern. Denn: die letzten Jahre waren wir aufgrund des Mangels an Sicherheit bedingten Fixen in SQL Server schlicht verwöhnt. Sicherheitslecks (zumindest solche, die bekannt geworden sind) sind seit SQL Server 2000 so gut wie nicht mehr aufgetreten.

- Dazu gibt es diverse Statistiken und Aufsätze, wie diesem von dem amerikanischen Sicherheitsexperten David Litchfield (www.davidlitchfield.com/security.htm) oder dem ITIC-Report von 2010: SQL Server Most Secure Database; Oracle Least Secure Database Since 2002 oder auch die Statistik vom NIST (National Institute of Standards and Technology) von 2013, auf der die folgende Grafik basiert:

(EN)
With 14 July 2015, quite precisely one year after the first security bugs in 5 years had to be fixed, we have been given a new reason to test one’s security patching policy.

- Provided that you have such a policy for SQL Server.

And it is not my intention to point at anybody. Because: we have simply been spoiled due to the lack of security-related fixes in SQL Server. Security bugs (at least those that leaked out) have basically not occurred since SQL Server 2000.

- There is a range of statistics and papers, such as that by the American security expert David Litchfield (www.davidlitchfield.com/security.htm), the ITIC-Report from 2010, SQL Server Most Secure Database; Oracle Least Secure Database Since 2002, and the statistics by the NIST  (National Institute of Standards and Technology) from 2013, on which the following graph is based:

Dadurch dass er auf Basis der NIST-Daten 5 Jahre in Folge als sicherstes Datenbanksystem galt, mag der SQL Server in Sachen Sicherheitspatching durchaus aus dem Fokus der Administratoren gewandert sein.

Der Security Bulletin vom Juli 2015:

Due to the fact that, based on the NIST data, the SQL Server has been the most secure database system 5 years in a row, it may indeed have moved away from the focus of administrators in terms of security patching.

The Security Bulletin from July 2015:

Beschrieben werden 3 Sicherheitslecks:

3 security vulnerabilities are described here:

Welche SQL Server Versionen sind betroffen?

Die Liste, beginnend mit SQL Server 2008 Service Pack 3 und endend mit SQL Server 2014 findet sich im Bulletin.
Genauer kann man anhand seiner jeweiligen SQL Engine Nummer in diesem Blog-Artikel von Microsoft suchen:

Which SQL Server versions are affected?

The list, starting with SQL Server 2008 Service Pack 3 and ending with SQL Server 2014, can be found in the bulletin.

A more detailed search is possible with the respective SQL Engine number in this blog article by Microsoft:

Unter welchen Umständen kann ein System durch diese Lücken angegriffen werden?

Die Voraussetzungen sind für die 3 Sicherheitslecks natürlich unterschiedlich und auch (absichtlich) nicht bis ins letzte Detail beschrieben.
WAS man jedoch herauslesen kann, ist, dass diejenigen, die Rechte nur sehr feingradig und dediziert gesetzt haben, wesentlich weniger angreifbar sind, da teilweise Schemaänderungsrechte im Datenbank-Bereich Voraussetzungen sind.
Daher erinnere ich an dieser Stelle gern daran, dass man weise beraten ist, wirklich nur benötigte Rechte zu vergeben, und Schemas als Sicherheitsbereiche zu verstehen. Die Verwendung von db_owner beispielsweise sollte Tabu für Applikations-Benutzer sein.
Wer solche Regeln bisher beherzigt hat, kann sich auch weiterhin einigermaßen sicher fühlen, da die Möglichkeit, diese Sicherheitslecks auszunutzen somit stark verringert ist.

Deswegen sollte man Best Practices für Sicherheit immer beherzigen.
Eine vulnerability ist eben noch kein exploit.

Under what circumstances can a system be attacked due to these bugs?

The preconditions for the three security bugs differ of course and are (on purpose) not described down to the last detail.

Yet WHAT is possible to read from it is that those who have set up rights only to a fine degree and dedicatedly are significantly less vulnerable since in part schema alteration rights in the database area are a prerequisite.

Therefore, I would like to recall that one is well-advised to really assign only necessary rights and to understand schemas as security areas. The use of db_owner, for example, should be off-limits for application users.

Those who have been following such rules so far may continue to feel fairly secure since the possibility to abuse these security vulnerabilities is thus greatly reduced.

For this reason, one should always take security best practices to heart.

A vulnerability does not yet make an exploit. 

Hier noch ein paar Links in Sachen Security in SQL Server:

Here are a couple of links related to security matters in SQL Server:

(DE)
Seit Anfang Mai ist es offiziell: Satya Nadella hat auf der Microsoft Ignite-Konferenz in Chicago den SQL Server 2016 vorgestellt.
Und ich kann jetzt schon sagen, dass der SQL Server 2016 eines der in meinen Augen spannendsten Releases werden wird. Und zwar weil diesmal Sicherheit ganz klar mit im Fokus steht. Sicherheitsfeatures gehören neben Performance-Features zu den „Key Capabilities“ des kommenden SQL Server, so dass ich den SQL Server 2016 gern ein „Sicherheit & Performance-Release“ nenne.

(EN)
The news broke in early May: That’s when Satya Nadella presented SQL Server 2016 at the Microsoft Ignite-Conference in Chicago.
I can already say that SQL Server 2016 will be one of the most exciting releases in recent years. And that’s because this time it’s clearly focused on security. Next to performance features, security features are among the “Key Capabilities” of the upcoming SQL Server. I like to call SQL Server 2016 a “Security & Performance-Release.”

Die Schlüsselfeatures sind demnach:

Always Encrypted

Mit diesem Feature ist es endlich möglich, eine Datenverschlüsselung anzubieten, bei der SQL Server selber keinen Zugriff auf die Daten im unverschlüsselten Zustand hat – und auch ein Datenbankadministrator Daten nicht entschlüsseln kann. Der Schlüssel wird hierbei in der Applikation bereitgehalten, wo die Verschlüsselung und Entschlüsselung stattfindet. Das war bisher nur mit eigenen Entwicklungen möglich.

Its key features are:

Always Encrypted

With this feature it is finally possible to offer encrypted data where SQL Server itself does not have access to the data in unencrypted form and where the database administrator is incapable of decrypting the data. The key is held within the very application where the encryption and decryption takes place. Up to now encryption/decryption was only possible if users developed solutions on their own.

Stretch Database

Stretched Tables bieten die Möglichkeit, innerhalb einer Tabelle Daten sowohl On-Premise als auch in der Cloud zu halten. Ähnlich wie bei Partitionierung wird hier transparent für die Applikation ein Teil der Daten nach Microsoft Azure ausgelagert, und nur dann geladen, wenn nötig. – Hier sollte man also sicherstellen, mit ungünstigen Abfragen keine Index-Scans in die Cloud zu verursachen.

Stretch Database

Stretched Tables offer the option to store data in a given table both On-Premise and in the Cloud. Similar to partitioning, part of the data will be swapped to Microsoft Azure, transparently to the application, and only accessed when necessary. Here it might be advisable to avoid causing index-scans to the cloud with badly designed queries.

Um die Daten zu schützen funktionieren Stretched Tables auch im Zusammenspiel mit Always Encrypted. Es bleibt abzuwarten, wie dieses Feature die Hemmungen, Daten in die Cloud zu geben, beeinflusst. Microsoft selber hat jedenfalls sicherlich kein Interesse daran, den Zugang zu seinen Systemen für Geheimdienste zu vereinfachen, und Always Encrypted dürfte ein klares Signal sein.

To protect the data, Stretched Tables also work together with Always Encrypted. It remains to be seen, how this feature affects the inhibition to give data to the cloud. Microsoft itself surely has no interest to make access to its systems simple for intelligence, and Always Encrypted should be a clear signal.

Real-time Operational Analytics & In-Memory OLTP

Unter diesem Feature verbirgt sich die Verheiratung der beiden „Performance-Killer“ Features Columnstore Indexe – für OLAP-Szenarien – und Memory Optimized Tabellen für In-Memory OLTP. Das heißt damit sind performante analytische Abfragen auf den frischen OLTP Daten in In-Memory möglich. In meinen Augen ist dieses Feature der Killer überhaupt, da Indexe auf den Tabellen direkt synchron sein müssen, und nicht, wie bei manch anderem Anbieter von In-Memory Datenbanken, erst mit Verzögerung die Daten erhalten. Auf dem PASS Summit 2014 war dieses Feature bereits kurz zu sehen, jedoch erst jetzt ist es offiziell und hat einen Namen.

Real-time Operational Analytics & In-Memory OLTP

Under this feature lies the marriage of the two „performance-killer“ features Columnstore Indexes – for OLAP-Scenarios – and Memory Optimized Tables for In-Memory OLTP. This enables for fast analytical queries on the fresh OLTP data in In-Memory. In my eyes this feature is the ultimate killer, since indexes on the tables have to be synchronous and not, like at some other vendors of In-Memory databases, only receive the data after a certain delay. At PASS Summit 2014 this features was already biefly presented, but only now it is official and has a name.

Das waren die Highlights, jedoch ist die Liste der weiteren neuen Features noch lang, und auch diese haben es in sich. Ich versuche, die wichtigsten einmal aufzulisten:

Those were the highlights, but the list of further new features is still long and highly potential. I try to list the most important ones:

Verbesserungen zu Columnstore Indexes

• Nicht-parallelisierte Abfragen können den Batch-Mode verwenden
• AlwaysOn Readable Secondary unterstützen beschreibbare Columnstore Indexe
• Columnstore Indexe auf einer Memory-Optimized Table möglich - nur im InterOP Mode verwendbar
• Filtered Nonclustered Columnstore Indexes
• Primary und Foreign Keys-Unterstützung

Improvements to Columnstore Indexes

• Single-threaded queries can use batch mode
• AlwaysOn readable secondary support updateable columnstore indexes
• Columnstore indexes possible on memory-optimized table – only works in InterOP mode
• Filtered Nonclustered Columnstore Indexes
• Support for primary and foreign keys

Verbesserungen für In-Memory OLTP

• ALTER TABLE und ALTER Procedure Operationen
  • Z.B. zum Ändern des Bucket counts von Hash Indexen
  • Multithreaded Writes und Merge-Prozess
  • Parallele Pläne
  • Fremdschlüssel auf In-Memory Tabellen
  • Viele weitere Verbesserungen Hinsichtlich T-SQL Support

Improvements for In-Memory OLTP

• ALTER and ALTER Procedure Operations
  • i.e. to change the bucket counts of hash indexes
  • Multithreaded Writes and Merge-process
  • parallel plans
  • foreign keys on In-Memory tables
  • many more improvements in terms of T-SQL support

Und weitere neue Security-Features:

Dynamic Data Masking

Damit lässt sich, basierend auf Policies, definieren, dass je nach User bestimmte Daten nur maskiert angezeigt werden.

And further new Security-Features:

Dynamic Data Masking

Based on Policies allow to define that depending on the user certain data is only shown maked.

Row-Level Security

Row-Level Security (RLS) ist um genau zu sein ein Feature um die Entwicklung von Zeilenbasierten Zugriffsmechanismen wesentlich zu vereinfachen und aus der Applikation in die Datenbank zu verlagern. In der jetzigen Implementation ist es noch recht einfach, über Side-Channel Attacken dennoch an die Daten heranzukommen. Daher muss man, wie eigentlich immer bei Sicherheits-Design, alle Bestandteile der Applikation im Blick haben.

Warum eine wirklich sichere Zeilenbasierte Zugriffskontrolle nicht so trivial ist, kann man unter anderem in diesem Blog-Post von mir nachlesen:

Row-Level Security

Row-Level Security (RLS) is, to be exact, a feature to greatly simplify the development of row-based access-control, and move from the application into the database. In the current implementation it is still quite easy, to get via Side-Channel Attacks to the data. Therefore, as always when designing for security, one has to have an eye on all parts of the application.

Why a truly secure row-based access control is far from trivial, one can find out among others in this blog-post of me:

Weitere Features in verschiedenen Bereichen:

PolyBase

Abfragen von relationalen und Nicht-relationalen Daten in Hadoop-Clustern mit T-SQL über sogenannte External Tables.

Further features in different areas:

PolyBase

Querying of relational and non-relational data in Hadoop-Clusters with T-SQL via so-called External Tables.

Query Store

Dieses Feature wurde schon mehrere Male von Conor Cunningham auf verschiedenen Konferenzen angedeutet. Jetzt kommt es wirklich.
Wenn eingestellt (pro Datenbank), werden im Hintergrund automatisch Abfragen und deren Pläne sowie Ausführungsstatistiken gesammelt, so dass man diese später nachvollziehen kann. Zusätzlich dazu kann man sehr leicht einen identifizierten Abfrageplan wiederverwenden.

Query Store

This feature has been presented several times at different conferences by Conor Cunningham. Now it is finally coming into the product.
Once activated, it automatically captures queries together with their plans and runtime statistics, thus allowing for later review of them. Added to that one can easily re-use an identified query plan.

Live Query Statistics

Im SQL Server 2016 erwecken Ausführungspläne „zum Leben“. Das lässt sich am besten mit einem Bild/Video veranschaulichen:

Live Query Statistics

In SQL Server 2016 query plans „come to live“. This is illustrated best with a picture/video:

Temporal Tables

Mit Temporal Tables werden Daten automatisch versioniert und stehen später für Abfragen nach Zeitraum der Gültigkeit zur Verfügung. Ähnlich, wie man es für Slowly Changing Dimensions/SCDs (Langsam veränderliche Dimensionen) in Datawarehouses selber oft entwirft.

Temporal Tables

With Temporal Tables data is automatically versioned and available for later querying based on time-span of validity. Similar to how one often designs oneself manually for Slowly Changing Dimensions/SCDs in Datawarehouses.

Trace Flag 4199, welches seit SQL Server 2005 SP3 CU 6 für alle SQL Server Installationen relevant ist, da es diverse Fixe des Query Prozessors erst aktiviert soll mit dem SQL Server 2016 kaum noch nötig sein. Warten wir auf Details, um das beurteilen zu können.

Trace Flag 4199, which is relevant for all SQL Server Installations since SQL Server 2005 SP3 CU 6, since it enables certain fixes of the query processor is supposedly mainly unnecessary for SQL Server 2016. Let’s wait for details for a final judgement.

Multiple Tempdb Datendateien

Bei der Installation wird direkt nachgefragt, wie viele TempDB Datendateien angelegt werden sollen. Standardmäßig wird hier die Anzahl der Cores mit einem Maximum von 8 verwendet. Damit werden auch weniger versierte Administratoren auf die Thematik von Latch-Contention in der Tempdb aufmerksam, und eine wichtige Best Practice wird damit standardmäßig angewandt.

Multiple Tempdb Data Files

Already at Setup one has to specify how many TempDB data files will be created. By default the number of cores with a maximum of 8 will be used. BY that that also less experienced administrators will become aware of  Latch-Contention in Tempdb, and an important Best Practice is applied by default.

Hochverfügbarkeit

• 3 Synchrone Replicas
• Unterstützung für Group Managed Service Accounts für AlwaysOn Failover Cluster
• AlwaysOn Availability Groups unterstützen Distributed Transactions und DTC auf Windows Server 2016
• AlwaysOn Availability Group Failover bei Database Offline
• ReadOnly Replikas können im Round-Robin-Verfahren angesprochen werden

High Availability

• 3 synchronous replicas
• Support for Group Managed Service Accounts for AlwaysOn Failover Cluster
• AlwaysOn Availability Groups support Distributed Transactions and DTC on Windows Server 2016
• AlwaysOn Availability Group Failover on Database Offline
• ReadOnly Replicas can be addressed in Round-Robin-manner

T-SQL

• Formatieren von Query Resultsets als JSON – ähnlich wie XML. Und möglicherweise wird es ein ähnliches Nischendasein erleiden. Hier sollte man auch an die, im Verhältnis zu einem Applikation-Server, teuren CPU-Lizenzen denken.

T-SQL

• Formatting of Query Resultsets as JSON – similar to XML. And maybe it will stay niched in a similar manner. Here one should think of the, expensive CPU-licences compared to an Applikation-Server

(EN)
This time, we are dealing with the „MDW“, short for Management Data Warehouse,(msdn.microsoft.com/en-us/library/bb677306.aspx), which I like to recommend as a minimal performance logging-approach.

From time to time, and most recently in the context of my PASS Essential „SQL Server Analysis tools & Techniques for Performance und general Monitoring“, the question arises as to whether the MDW operates together with the High Availability technologies Database Mirroring and AlwaysOn Availability Groups, and if so, how so.

(DE)
Diesmal geht es um das Management Data Warehouse, kurz „MDW“ (msdn.microsoft.com/de-de/library/bb677306.aspx), welches ich gerne als minimalen Performance-Protokollierungs-Ansatz empfehle.

Hin und wieder, zuletzt im Zusammenhang mit meinem PASS Essential „SQL Server Analysetools & Techniken für Performance und allg. Monitoring“ kommt die Frage auf, ob das MDW mit den Hochverfügbarkeitstechnologien Datenbankspiegelung und AlwaysOn Hochverfügbarkeitsgruppen zusammenspielt, und wenn, wie.

The short answer is: Yes, it does.
The MDW operates both with Database Mirroring as well as with AlwaysOn Availability Groups.

The following graph illustrates a possible setup using the latter:

Die kurze Antwort lautet: Ja.
Das MDW funktioniert sowohl mit Datenbankspiegelung als auch mit den AlwaysOn Hochverfügbarkeitsgruppen.

Das folgende Schaubild zeigt ein mögliches Setup unter Verwendung der letzteren Variante:

The server (0) holding the MDW database is located outside of the high availability nodes. The databases to be monitored are located in the AlwaysOn Availability Groups in the servers 1-3.
Keeping the MDW highly available is not the objective. It is simply about being able to see the performance data of all databases, no matter in which server they are active at the moment.

Der Server (0), der die MDW-Datenbank vorhält, liegt außerhalb der Hochverfügbarkeitsknoten. Die zu überwachenden Datenbanken liegen in AlwaysOn Hochverfügbarkeitsgruppen auf den Servern 1-3.
Das MDW hochverfügbar zu halten ist nicht das Ziel. Es geht nur darum, die Performance-Daten aller Datenbanken einsehen zu können, gleich auf welchem Server sie gerade aktiv sind.

Part 1: Databases in secondary role

If you set up the MDW as standard you will realize that after a failover, the data of the respective databases disappear from the “Disc Usage” reports of the server, while these were previously still present in the primary role.

The background to this is that after a failover, the respective databases now are present in a different server in the primary role, and now are no longer readable in the secondary, in the standard setting.

In this moment, the System Data Collection Set “Disc Usage”, or the underlying job “collection_set_1_noncached_collect_and_upload” cannot collect data for this database. In contrast on the new primary node these database will now reappear as long as they are active in the primary role there. In principle, this behavior is comprehensible: The Data Collector can no longer find any information on this database and assumes that the latter is no longer relevant – as if it was deleted. One may certainly wish for a possibility of intervention here; however, the MDW is currently not flexible in this regard.

The new report “Transaction Performance Analysis Overview” which is enriched through the newly existent “Transaction Performance Collection Set” in SQL Server 2014 also displays data for no longer active databases.

Having clarified this background, the possible solution is self-evident: The databases must remain readable.

With AlwaysOn High Availability Groups, this is in principal easily done:

Part 1: Datenbanken in Secondary-Rolle

Wenn man nun das MDW standardmäßig einrichtet, wird man feststellen, dass nach einem Failover die Daten der jeweiligen Datenbanken aus den „Disk Usage“-Berichten des Servers verschwinden, wo diese bis zuvor noch in der Primary-Role vorlagen.

Hintergrund ist, dass nach einem Failover die jeweiligen Datenbanken nun auf einem anderen Server in der Primary-Role vorliegen, und auf dem nun Secondary, in der Standardeinstellung nicht lesbar sind.

In diesem Moment kann das System Data Collection Set „Disk Usage“, bzw. der dahinterliegende Job „collection_set_1_noncached_collect_and_upload“ zu dieser Datenbank keine Daten auslesen. Auf dem neuen Primary-Knoten hingegen werden diese Datenbanken nun neu erscheinen, solange sie dort in der primären Rolle aktiv sind.

Prinzipiell ist dieses Verhalten nachvollziehbar: Der Data Collector kann keine Informationen zu dieser Datenbank mehr finden und geht davon aus, dass diese nicht mehr relevant ist – als ob sie gelöscht sei. Sicherlich kann man sich hier eine Eingriffsmöglichkeit wünschen, derzeit ist das MDW aber in dieser Hinsicht nicht flexibel.
- Der neue Bericht „Transaction Performance Analysis Overview“, der über das neu im SQL Server 2014 existierende „Transaction Performance Collection Set“ angereichert wird, zeigt auch Daten für bereits nicht mehr aktive Datenbanken an.

Wenn nun dieser Hintergrund klar ist, liegt die mögliche Lösung nahe: Die Datenbanken müssen lesbar bleiben.
Mit AlwaysOn Hochverfügbarkeitsgruppen (Availability Groups) ist das prinzipiell auch leicht gemacht:

However, one needs to be aware of the fact that these databases are now released for all reading access – which should be taken into consideration in respect to application architecture, performance as well as in terms of license. Hence, for the purpose of data collection for performance evaluation alone I CANNOT recommend it.

If however the business applications are supposed to maintain reading access to the secondary point anyway, the data collector is covered with this as well.

One more advice: The setting “Read-Intent only” unfortunately does not work with the MDW since one cannot manually adapt the Connection String accordingly.
Database Mirroring does not support this option at all.

Jedoch muss man sich hierüber im Klaren sein, dass diese Datenbanken nun für sämtliche Lesezugriffe freigegeben sind, was sowohl hinsichtlich Applikationsarchitektur, Performance als auch Lizenztechnisch genau bedacht werden sollte. Allein zum Zweck der Datensammlung zu Performance-Auswertung kann ich das also NICHT empfehlen.
Wenn die Geschäftsanwendungen aber ohnehin Lesezugriffe auf den Sekundärknoten erhalten sollen, dann ist damit der Datensammler ebenfalls abgedeckt.
Noch ein Hinweis: Die Einstellung „Read-Intent only“ funktioniert mit dem MDW bisher leider nicht, da man den Connection String nicht entsprechend manuell anpassen kann.
Datenbankspiegelung unterstützt diese Option gar nicht.

Part 2: Configuration of the MDW-Clients

Since the databases run on a different node after a failover, the MDW reports must be set up in all servers in which the Availability Group is running. Here, one needs to ensure that access to the central MDW-Server is possible from all servers.

To do this (before SQL Server 2014) the SQL Server Agent Account of the client-instance must be included in the mdw_writer role on the MDW-Server (mdw_admin is not necessary) when configuring the MDW through the “Configure Management Data Warehouse Wizard:

Part 2: Konfiguration der MDW-Clients

Da die Datenbanken nach einem Failover auf einem anderen Knoten laufen, müssen die MDW-Berichte auf allen Servern, auf denen die Availability Group läuft, eingerichtet werden.

Dabei muss sichergestellt werden, dass von allen Servern auf den zentralen MDW-Server zugegriffen werden kann.

Dazu muss (vor SQL Server 2014) bei der Konfiguration des MDW über den „Configure Management Data Warehouse Wizard“ der SQL Server Agent Account der Client-Instanz auf dem MDW-Server in die mdw_writer-Rolle aufgenommen werden (mdw_admin ist nicht notwendig):

From SQL Server 2014, at the configuration of the data collection in the client, it is possible to provide a SQL Server Agent Proxy of the type “Operating System (CmdExec)” as account for the access to the central MDW-Server:

Ab SQL Server 2014 kann man bei der Konfiguration der Data Collection auf dem Client einen SQL Server Agent Proxy vom Typ „Operating System (CmdExec)“ als Konto für den Zugriff auf den zentralen MDW-Server hinterlegen:

In this case, it is of course required to authorize the underlying Windows account in the server, instead of the agent itself, as „mdw-writer“.

In diesem Fall muss auf dem Server natürlich der dahinterstehende Windows-Account anstelle des Agents selber als „mdw_writer“ berechtigt werden.

As soon as all clients are authorized accordingly, one can read the data of all SQL Server AG nodes in the central management server. Depending on which server a database is currently present in in the primary role, it will then appear in the according subreport.
- This proceeding also works with mirrored databases in Database Mirroring scenarios – there one can only have one “partner instance” though.

Sobald alle Clients entsprechend berechtigt sind, kann man auf dem zentralen Management Server die Daten aller SQL Server Knoten der AG auslesen. Je nachdem auf welchem Server eine Datenbank gerade in der Primary-Rolle vorliegt, erscheint diese dann in dem entsprechenden Subreport.
- Diese Vorgehensweise funktioniert auch mit gespiegelten Datenbanken bei Datenbankspiegelungs-Szenarien – dort gibt es jedoch maximal eine „Partner-Instanz“.

(en)
You may remember the survey on database ownership which I launched several months ago.

In the following, I am now presenting the results and giving my official recommendation for a best practice for security in terms of database ownership.
First, if you still need the script:

(de)
Ihr erinnert Euch vielleicht an die Umfrage zu Datenbankbesitz, die ich vor einigen Monaten gestartet habe.
Hier präsentiere ich nun die Ergebnisse und gebe meine offiziellen Empfehlungen für Sicherheits-Best Practice hinsichtlich Datenbankbesitz.
Zunächst, wer noch den Script benötigt:

Now first the results.
I received data from 58 different servers and 905 databases altogether.
That’s not bad, and sufficient for my purpose of giving you, my readers, the opportunity to find out how others configure their servers.

Many thanks to all those who submitted!
You may still share results but I can’t promise how soon I can include them. (Here is the survey plus the script for collection)
So now to the details. I put the most interesting data in charts.
The most obvious issue is that of the external owner’s account, which is most often and not very surprisingly sa:

Nun zuerst einmal die Ergebnisse.

Ich habe insgesamt von 58 verschiedenen Servern und 905 Datenbanken Daten erhalten.

Das ist nicht schlecht. Und es ist ausreichend für meine Zwecke – Euch, meinen Lesern, die Gelegenheit zu gegeben herauszufinden, wie andere ihre Server konfigurieren.

Vielen Dank an alle, die ihre Daten eingereicht haben!

Ihr könnt eure Ergebnisse immer noch mit mir teilen, aber ich kann euch nicht versprechen, wie bald ich sie mit einschließen kann. (Hier sind die Umfrage sowie das Skript für die Datenerfassung.)

Nun zu den Details. Ich habe die interessantesten Daten in Diagramme gesetzt.

Die offensichtlichste Frage ist die des Kontos des externen Besitzers, das am häufigsten und nicht überraschenderweise sa ist:

57% of all databases belong to sa himself. Actually, this is better than expected. But let’s dive further – what’s the server role behind the remaining 42%?

57% aller Datenbanken gehören sa selbst. Dies ist sogar besser als erwartet. Aber schauen wir mal genau hin – was ist die Server-Rolle hinter den verbleibenden 42%?

Ok, that changes the picture quite a bit. Almost 80% of all Database owners are sysadmin. So that is by no means any better than sa.
Then some other accounts follow, which means those have low privileges (“excellent”), and then comes dbcreator, securityadmin, that are later followed by some other high privileged server roles, though with much less power.

So in other words: only 7% of all those databases have been looked at with security in mind by only using low privileged accounts as owners.

If you are interested in the plain numbers, here they go:

Ok, das verändert das Bild schon entscheidend. Fast 80% aller Datenbankenbesitzer sind sysadmin. Das ist also keineswegs besser als sa.
Es folgen einige andere Konten, das bedeutet, dass diese niedrige Privilegien (“hervorragend”) haben, und dann folgen dbcreator, securityadmin, auf die später einige andere hochprivilegierten Serverrollen folgen, doch mit weitaus weniger Privilegien.

Mit anderen Worten: nur 7% aller dieser Datenbanken wurden im Hinblick auf Sicherheit betrachtet, indem von Besitzern nur niedrigprivilegierte Konten verwendet wurden.

Wenn euch die genauen Zahlen interessieren, hier sind sie:

I did include some of the security-wise critical database- & server configurations:

1. Is the database set to be “Trustworthy”?
2. Is the database set to have “Database chaining on”?
3. Is the Server set to have “cross database chaining on”?

Those are actually the even more important results.

Since the system databases need to have a different setting by default, I am excluding them, making it a total of 847 User databases.
Of which 30 have the trustworthy bit set to on, and 35 have the database chaining.
What you can’t see in this graph, but what I can tell from the raw data, is that those 30 “trustworthy” databases all are owned by a sysadmin.
And THIS now is the biggest security-hole in this area!
Here a graph on that:

Ich habe einige der sicherheitstechnisch kritischen Datenbank- und Serverkonfigurationen mit eingeschlossen:

1. Ist die Datenbank auf „Trustworthy“ eingestellt?
2. Ist in der Datenbank „Datenbank-Verkettung an“ eingestellt?
3. Ist im Server „cross database chaining on“ eingestellt?

Diese sind eigentlich die wichtigeren Ergebnisse.

Da die Systemdatenbanken standardmäßig eine andere Einstellung haben müssen, schließe ich sie in meiner Bewertung aus, so dass ich auf insgesamt 847 Nutzer-Datenbanken komme.
30 von ihnen haben das Trustworth-Bit eingestellt, und 35 haben die Datenbanken-Verkettung eingeschaltet.
Was ihr in dieser Grafik nicht sehen könnt, aber was ich aus den Rohdaten erkennen kann, ist, dass diese 30 „vertrauenswürdigen“ Datenbanken alle im Besitz von einem sysadmin sind.
Und DAS ist das größte Sicherheitsloch in diesem Bereich!
Hier ein Diagramm dazu:

In the interest of time I will focus this post on recommendation rather than explaining all the risks involved. At the end though I will provide some links for further reading.

Aus Zeitgründen werde ich diesen Eintrag auf Empfehlungen beschränken, als alle Risiken zu erklären. Am Ende werde ich jedoch einige Links für weiterführende Lektüre angeben.

Possibilities

So what are the general variations of database ownership?
Let me start with the most common and actually WORST possibilities (Yes, I mean it exactly as I say ;-) ):

1. SA-Account
2. Some other SQL-Account with sysadmin privileges
3. Windows Login with sysadmin privileges

A first improvement(? – really?):

      4. Any of the above with Status = Disabled

 And then:

     5.   A ”shared” account without any special server role or permissions (aka “1 Account per Server”)

     6.   1 Account per Database

     7.    1 Account per Application

     8.   1 Account per Group of databases

 + all of them not only Disabled but with a Denied Connect-Permission

Möglichkeiten

Was sind also die allgemeinen Variationen von Datenbanken-Besitztum?

Fangen wir mit den häufigsten und eigentlich SCHLECHTESTEN Möglichkeiten an (Ja, das meine ich genau so, wie es hier steht ;-) ):

1. SA-Konto
2. Irgendein anderes SQL-Konto mit sysadmin-Privilegien
3. Windows Login mit sysadmin-Privilegien

 Eine erste Verbesserung(? – wirklich?):

     4.   Alle der oben angegebenen mit Status = Deaktiviert

 Und dann:

     5.     Ein „geteiltes“ Konto ohne eine spezielle Serverrolle oder Rechte (Alias „1 Konto pro Server“)

     6.   1 Konto pro Datenbank

     7.   1 Konto pro Anwendung

     8.   1 Konto pro Datenbank-Gruppe

 + alle davon nicht nur Deaktiviert sondern mit einer verweigerten Verbindungs-Berechtigung 

My Recommendation:

Depending on your environment: Any of 5, 6, 7 or 8:

Create a specific Login without any extra permissions + Deny Connect.

The most simple approach and yet better than sa is: one database owner per server.
Example for (5):

• Database1 owned by DBOwner
• Database2 owned by DBOwner
• Database3 owned by DBOwner

 Simple and self-explanatory.

The other extreme and most secure is: per database.
Example for (6):

• Database1 owned by DBOwner_Database1
• Database2 owned by DBOwner_Database2
• Database3 owned by DBOwner_Database3
• Database4 owned by  DBOwner_Database4

 Some applications use a number of different databases. For them it’s perfectly fine to use the same database owner account. So create an account per application.

Example for (7):

• App1Database1 owned by DBOwner_App1
• App1Database2 owned by DBOwner_App1
• App2Database1 owned by DBOwner_App2
• App2Database owned by  DBOwner_App2

 Another approach is kind of a compromise between 1 Database-Owner Account per Server and One per database: Define the level of security needed per database. Then create a dedicated account for the most critical Databases. And for the others use a shared owner/account, possibly divided in 2 or more groups.

Example for (8):

• CriticalDatabase1 owned by DBOwner_Level1Dedicated1
• CriticalDatabase2 owned by DBOwner_ Level1Dedicated2
• Level2Database1 owned by DBOwner_Level2
• Level2Database2 owned by DBOwner_Level2

 I hope my samples give you an idea. :-)

So why this effort?
Let me put it this way: ”Why not sa?”.
First: If you think about it, it actually makes little sense that the highest privileged account in SQL Server is being recommended by so many, even professionals + in Whitepapers (!) – when security is the focus. It is really wrong, as wrong as it could possibly get.
I mean, as you can see, there are other options out there.
The top reason why SA keeps getting recommended is administration itself: It eases the setup for failover and regular database restores, since SA is always available at any server and hence a broken database owner can be avoided with almost no extra work.
But that’s “only” from a perspective of maintenance.
With regard to security it is totally on contrary to the Principle of least privilege.

It may not matter a lot, if everything else is tightened, but that’s hardly a thing to rely on especially in bigger environments where things change and many people have access and permissions to.
Especially in the context of the trustworthy-setting for a database, this completely opens the system for privilege escalation attacks from inside. It is then a piece of cake to gain system level permissions once you are for example in the db_owner database group – like many applications are, if they are not sysadmin already.
- Remember: the owner of a database cannot be denied anything inside and with his database. So he can change structure, create backups, break log-backup-chain and also drop it completely.

And since the attack starts from inside, it really doesn’t matter whether the sa/sysadmin account is disabled as you may now realize.

Having a dedicated account with zero special permissions as database owner prevents database principals from gaining system level permissions as a sysadmin has, even in the case of the database being trustworthy.
And trustworthy is one of the dirty little shortcuts for developers implementing CLR code inside the database and avoiding the hassle of having to use certificates under certain conditions. The same is often done for code that needs to get server-level data from inside the database.

Meine Empfehlung:

Abhängig von eurer Umgebung: eine von 5, 6, 7 oder 8:

Ein spezifisches Login errichten ohne extra  Rechte + Deny Connect.

Die einfachste Herangehensweise und doch besser als sa ist: ein Datenbankbesitzer pro Server.

Beispiel für (5):

• Datenbank1 im Besitz von DBOwner
• Datenbank2 im Besitz von DBOwner
• Datenbank3 im Besitz von DBOwner

 Einfach und selbsterklärend.

 Das andere Extrem und dabei die sicherste ist: pro Datenbank.

Beispiel für (6):

• Datenbank1 in Besitz von DBOwner_Database1
• Datenbank2 in Besitz von DBOwner_Database2
• Datenbank3 in Besitz von DBOwner_Database3
• Datenbank4 in Besitz von DBOwner_Database4

Einige Anwendungen verwenden eine Reihe von unterschiedlichen Datenbanken. Für sie ist es völlig ausreichend, das gleiche Datenbankbesitzerkonto zu verwenden. Erstellt also ein Konto pro Anwendung.

Beispiel für (7):

• App1Database1 in Besitz von DBOwner_App1
• App1Database2 in Besitz von DBOwner_App1
• App2Database1 in Besitz von DBOwner_App2
• App2Database in Besitz von DBOwner_App2

Eine andere Herangehensweise ist eine Art Kompromiss zwischen 1 Datenbankenbesitzerkonto pro Server und einem pro Datenbank: Definiere das Sicherheitslevel, das je Datenbank gebraucht wird. Dann erstelle ein spezielles Konto für die kritischsten Datenbanken. Und für die anderen Besitzer einen gemeinsamen Besitzer-/Konto verwenden, möglicherweise in 2 oder mehr Gruppen geteilt.

Beispiel für (8):

•  CriticalDatabase1 in Besitz von DBOwner_Level1Dedicated1
• CriticalDatabase2 in Besitz von DBOwner_ Level1Dedicated2
• Level2Database1 in Besitz von DBOwner_Level2
• Level2Database2 in Besitz von DBOwner_Level2

 Ich hoffe, meine Beispiele geben euch eine Vorstellung. :-)

Aber warum diese Mühe?
Lasst es mich so ausdrücken: “Warum nicht sa?”
Zuallererst: Denkt man darüber nach, ergibt es eigentlich wenig Sinn, dass das höchstprivilegierte Konto beim SQL Server von so vielen empfohlen wird, selbst von Profis + in Whitepapers (!) – wenn Sicherheit im Fokus steht. Es ist wirklich falsch, so falsch wie es nur irgend sein kann.

Schließlich gibt es da draußen, wie ihr sehen könnt, noch andere Optionen.

Die Grund Nr. 1, warum SA immer wieder empfohlen wird, ist die Administration selbst: Es erleichtert das Einrichten für Failover und regelmäßige Datenbankenwiederherstellungen, da SA immer auf jedem Server verfügbar ist und damit ein kaputter Datenbankbesitzer mit wenig zusätzlichem Aufwand verhindert werden kann.
Aber das ist „nur“ aus Sicht der Wartung.
Was die Sicherheit angeht, steht es völlig im Gegensatz zum Prinzip des geringsten Privilegs.

Es mag nicht viel ausmachen, wenn alles andere straff sitzt, aber darauf sollte man sich nicht verlassen, besonders in größeren Umgebungen, wo sich Dinge ändern und viele Leute Zugriff und Befugnisse haben.

Besonders im Kontext der Trustworthy-Einstellung für eine Datenbank öffnet dies das System komplett für privilege escalation-Angriffe von innen. Dann ist es ein Kinderspiel, Systemlevel-Befugnisse zu erlangen, wenn man einmal z.B. in der db_owner Datenbankengruppe ist – wie es viele Anwendungen sind, wenn sie nicht bereits sysadmin sind.

Denkt dran: dem Datenbankenbesitzer kann weder innerhalb noch mit seiner Datenbank etwas verweigert werden. Er kann also die Struktur verändern, Backups erstellen, Log-Backup-Chain brechen und sie auch komplett löschen.

Und da der Angriff von innen anfängt, ist es wirklich egal, ob das sa/sysadmin Konto deaktiviert ist, wie ihr jetzt realisiert haben werdet.
Ein spezielles Konto mit Null speziellen Befugnissen als Datenbankbesitzer zu haben hindert Datenbank-Prinzipale daran, System-Level-Befugnisse zu erlangen, wie sie ein sysadmin hat, selbst in dem Fall, dass die Datenbank vertrauenswürdig ist.
Und „trustworthy“ ist eine der unsauberen kleinen Abkürzungen für Entwickler, die CLR-Code im Innern der Datenbank ausführen und sich dabei die Umstände sparen, unter bestimmten Bedingungen Zertifikate benutzen zu müssen. Dasselbe wird oft für Code gemacht, der Server-Level-Daten aus dem Innern der Datenbank erreichen muss.

Call for actions:

Check your databases. You can find my script here: Security-Check-Script & Survey: SQL Server Security - Database-Owners, critical Permissions and role membership
Now when you start with securing your databases from database-ownership standpoint, you have to make sure that the very account does exist at any sever where this database gets restored/failed over. Usually you will have a technique in place already to synchronize your server-level principals to your other servers. So this is just one or several more of them.

Also make sure you fully understand your environment and possibly application needs before you just change the owner of your databases. You can start by reading through the links at the bottom.

Vote for an improvement in SQL Server:
I have created a suggestion as Connect Item which tackles this problem. My idea is having Microsoft include a special “DBOwner” Account at server level by default, which not only pre-exists and has not permissions, but also never compares to another. I think this would make it much easier to get rid of the habit of “sa” everywhere by also making it simple to maintain.
Please vote here: Providing a special Server principal for Database Ownership

Handlungsaufruf:

Überprüft eure Datenbanken. Ihr könnt meinen Skript hier finden: Sicherheitsprüfungs-Script & Umfrage: SQL Server Datenbankbesitzer, kritische Rechte und Rollenmitgliedschaft

Wenn ihr jetzt anfangt, eure Datenbanken aus der Perspektive von Datenbanken-Besitz zu sichern, müsst ihr dabei sicherstellen, dass dasselbe Konto auf jedem Server existiert, wo diese Datenbank wiederhergestellt/failed over wird. Normalerweise werdet ihr bereits eine Technik haben, wie ihr eure Server-Level-Prinzipale mit euren anderen Servern synchronisiert. Das sind also nur eine oder einige mehr davon.

Stellt außerdem sicher, dass ihr eure Umgebung und möglicherweise Anwendungsbedürfnisse vollständig versteht, bevor ihr den Besitzer eurer Datenbanken einfach ändert. Ihr könnt damit anfangen, indem ihr euch unten aufgelisteten Links durchlest.

Abstimmen für eine Verbesserung im SQL Server:
Ich habe einen Vorschlag als Connect Item erstellt, der dieses Problem behandelt. Meine Vorstellung ist es, Microsoft dazu zu bringen, standardmäßig ein spezielles „DBOwner“ Konto auf Server-Level auszuliefern, das nicht nur bereits immer vorab existiert und keine Rechte hat, sondern auch nie mit anderen vergleichbar ist. Ich denke, dass dies es viel einfacher machen würde, die allgegenwärtige Gewohnheit des „sa“ loszuwerden und es gleichzeitig auch einfach Wartbar machen würde.
Bitte hier Eure Stimme abgeben: Providing a special Server principal for Database Ownership

I hope this was helpful.

If you have any questions feel free to comment.
Let me finish up with some links for further readings:

Ich hoffe, das war hilfreich.

Wenn ihr noch Fragen habt, kommentiert gern.
Zum Abschluss einige Links für weiterführende Lektüre:

(DE)
Nachdem ich letztes Jahr das erste Mal als Teilnehmer auf der SQLBits-Konferenz in England war, darf ich dieses Jahr nicht nur eine Session halten, sondern sogar auch einen der begehrten „Training Days“ im PreCon-Stil!

(EN)
After having attended the SQLBits Conference in the UK last year for the first time, I became not only one of the lucky people to hold a session, but also to give one of the much sought after “Training days” in PreCon-style!

Niko Neugebauer und ich geben am 17. Juli die ganztägige Veranstaltung „In-Memory Technologies in SQL Server 2014: CCI & XTP“.

Niko wird seine Erfahrung mit den neuen Clustered ColumnStore Indexen teilen und ich werde die neue In-Memory OLTP Engine XTP detailliert vorstellen.

Niko Neugebauer and I will be delivering the full day presentation „In-Memory Technologies in SQL Server 2014: CCI & XTP“ on July 17^th.

Niko will share his experience with the new Clusterd ColumnStore Indexes and I will present the all new In-Memory Engine XTP in detail.

- Diesen Ganztages-Vortrag haben wir bereits in ähnlicher Form auf dem Deutschen Launch Event für den SQL Server 2014 gehalten.

Das ist eine überaus große Ehre für uns, zumal dort seit Jahren einige echte Gurus ihres Fachs auftreten. So befinden wir uns dieses Jahr in der Illustren Gesellschaft von Brent Ozar, Brian Knight, Jennifer Stirrup, Dejan Sarka, Marco Russo, Adam Jorgensen und John Welch, Itzik Ben-Gan, Allan Hirt, Dave Ballantyne und David Morrison und Simon Sabin!

Am Freitag, den 18., halte ich dann meine bekannte Security Session: „“SQL Attack…ed” – SQL Server under attack: SQL Injection“.

Der Samstag ist übrigens „Community day“ mit kostenloser Teilnahme – die Plätze werden wie für alle Tage aber schnell weg sein. Also nicht zu lange warten.
Hier geht’s zur vollständigen Agenda.

- We presented this full day session already in similar shape at the German Launch Event for SQL Server 2014.

This is a huge honor for us, especially since some of the real Gurus of their subject have been presenting there for years. So this year we are finding ourselves in the illustrious company of Brent Ozar, Brian Knight, Jennifer Stirrup, Dejan Sarka, Marco Russo, Adam Jorgensen and John Welch, Itzik Ben-Gan, Allan Hirt, Dave Ballantyne and David Morrison and Simon Sabin!

On Friday the 18^th, I will then give my well-known Security Session: „“SQL Attack…ed” – SQL Server under attack: SQL Injection”.

Saturday, by the way, is “Community day” with attendance free of charge– the seats are going to be taken quickly though, as for all days, so do not wait too long.
Here you can find the complete agenda.