Today’s blog post comes from a Twitter thread on which @SQLSoldier, @sqlstudent144 and @SQLTaiob were discussing
the internals of contained database users. Unless you have been living
under a rock, you’ve heard about
the concept of contained users within a SQL Server database (hit
the link if you have not). In this article I’d like to show you that you can, indeed, script out contained database users and recreate them on another database, as either contained users or as good old fashioned logins/server principals as well. Why would this be useful? Well, because you would not need to know
the password for
the user in order to recreate it on another instance. I know there is a limited number of scenarios where this would be necessary, but nonetheless I figured I’d throw this blog post to show how it can be done. A more obscure use case: with
the password hash (which I’m about to show you how to obtain) you could also crack
the password using a utility like hashcat, as highlighted on this SQLServerCentral article.
The Investigation SQL Server uses System Base Tables to save
the password hashes of logins and contained database users. For logins it uses sys.sysxlgns, whereas for contained database users it leverages sys.sysowners. I’ll show you what I do to figure this stuff out: I create a login/contained user, and then I immediately browse
the transaction log with, for example, fn_dblog. It’s pretty obvious that only two base tables touched by
the operation are sys.sysxlgns, and also sys.sysprivs –
the latter is used to track permissions. If I connect to
the DAC on my instance, I can query for
the password hash of this login I’ve just created. A few interesting things about this hash. This was taken on my laptop, and I happen to be running SQL Server 2014 RTM CU2, which is
the latest public build of SQL Server 2014 as of time of writing. In 2008 R2 and prior versions (back to 2000),
the password hashes would start with 0x0100.
The reason why this changed is because starting with SQL Server 2012 password hashes are kept using a SHA512 algorithm, as opposed to SHA-1 (used since 2000) or Snefru (used in 6.5 and 7.0). SHA-1 is nowadays deemed unsafe and is very easy to crack. For regular SQL logins, this information is exposed through
the sys.sql_logins catalog view, so there is really no need to connect to
the DAC to grab an SID/password hash pair. For contained database users, there is (currently) no method of obtaining SID or password hashes without connecting to
the DAC. If we create a contained database user, this is what we get from
the transaction log: Note that
the System Base Table used in this case is sys.sysowners. sys.sysprivs is used as well, and again this is to track permissions. To query sys.sysowners, you would have to connect to
the DAC, as I mentioned previously. And this is what you would get: There are other ways to figure out what SQL Server uses
under the hood to store contained database user password hashes, like looking at
the execution plan for a query to sys.dm_db_uncontained_entities (Thanks, Robert Davis!) SIDs, Logins, Contained Users, and Why You Care…Or Not. One of
the reasons behind
the existence of Contained Users was
the concept of portability of databases: it is really painful to maintain Server Principals (Logins) synced across most shared-nothing SQL Server HA/DR technologies (Mirroring, Availability Groups, and Log Shipping). Often times you would need
the Security Identifier (SID) of these logins to match across instances, and that meant that you had to fetch whatever SID was assigned to
the login on
the principal instance so you could recreate it on a secondary. With contained users you normally wouldn’t care about SIDs, as
the users are always available (and synced, as long as synchronization takes place) across instances. Now you might be presented some particular requirement that might specify that SIDs synced between logins on certain instances and contained database users on other databases. How would you go about creating a contained database user with a specific SID?
The answer is that you can’t do it directly, but there’s a little trick that would allow you to do it. Create a login with a specified SID and password hash, create a user for that server principal on a partially contained database, then migrate that user to contained using
the system stored procedure sp_user_migrate_to_contained, then drop
the login. CREATE LOGIN <login_name> WITH PASSWORD = <password_hash> HASHED, SID = <sid> ; GO USE <partially_contained_db>; GO CREATE USER <user_name> FROM LOGIN <login_name>; GO EXEC sp_migrate_user_to_contained @username = <user_name>, @rename = N’keep_name’, @disablelogin = N‘disable_login’; GO DROP LOGIN <login_name>; GO Here’s how this skeleton would look like in action: And now I have a contained user with a specified SID and password hash. In my example above, I renamed
the user after migrated it to contained so that it is, hopefully, easier to understand. Enjoy!
