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!
