Cross-database Queries by Contained Users

http://www.necromaticmedia.com/2007/11/containment/

I presented to the Pacific Northwest SQL Server User Group / PASS Chapter (PNWSQL) last night on the subject of contained databases. The slide deck and demo code from my presentation, Implementing Contained Databases, can be downloaded below or will be uploaded to the User Group site in the near future, if not already. the presentation showed the basic implementation and administration tasks as well as covering the pitfalls and gotchas that you need to watch out for.

There was a question brought up last night my good friend Meher (@MeherSQL) for a scenario I had not yet tested. Since we know that contained users have no permissions external to the database, what would happen if the same contained user exists in more than 1 database on the server? Would they be able to access multiple databases without re-logging in? I postulated that the only way that would work is if the security identifiers (SIDs) were the same. If they’re not the same, it definitely won’t work. If they are the same, it may work.

I set out to test that theory today. I created 2 contained databases on one of my SQL Server 2012 instances and used the same Create user statement to create a contained user (user with password) in both databases.

-- Create contained database #1
Create Database CDTest
    Containment = Partial;
Go
-- Create contained database #2
Create Database CDTest2
    Containment = Partial;
Go
-- Create contained user in database #1
Use CDTest;
Create User TestCDUser
    With Password = N'12345',
    Default_Schema = dbo;
Go
-- Create contained user in database #2
Use CDTest2;
Create User TestCDUser
    With Password = N'12345',
    Default_Schema = dbo;
Go

Then I open a query window and log in to database CDTest as the contained user. The first thing I notice is that I can only see the database I logged into plus tempdb and master in the dropdown list. Then I run just a basic query against the other database, and I get an error stating that I cannot access the other database. In another query window, I log in to the other contained database and not that exact same behavior from the second attempt though this time, the error reported a different SID for the account.

The error statement:

Msg 916, Level 14, State 1, Line 1
The server principal "S-1-9-3-3229113722-1181156168-1455050630-361674451." is not able to access the database "CDTest2" under the current security context.

Another friend, Nic Cain (blog|@SirSQL), suggested creating the second user with the same SID as the first. I dropped the User in one of the databases and then recreated it using the same SID as the other. Note that to do this, i don’t use the SID specified in the error message. I use the binary SID stored in sys.database_principals.

Create User TestCDUser
    With Password = N'12345',
    Default_Schema = dbo,
    SID = 0x0105000000000009030000007A5D78C048036746864FBA56D3B68E15;
Go

I run the same test as before and get the same results except this time the error messages report the same SID. No luck. On a hunch, I try enabling the TRUSTWORTHY property on both databases. I refresh my connection in both query windows. I note that the database selection dropdown still shows only the current database plus master and tempdb. However, when I attempt to query the other database in each window, I get vastly different results. With the TRUSTWORTHY option set, I am able to run cross-database queries as the same user.

I dropped the second user again and recreated it without specifying the SID. Now that the SIDs no longer matched, attempts to run cross-database queries fail with the same error as before. If you want to use contained users but need to be able to perform cross-database queries, this method will allow you to accomplish it.

As a side-note, I must remind you that using TRUSTWORTHY opens your database up to security holes, and it is recommend to not use it unless you have no other option.

Session Files From My Presentation

Implementing Contained Databases: ImplementingContainedDatabases.zip (494 KB)