Storing a Windows SID in a Database for Lookup

Question

I have an ASP.NET MVC application where I need to allow to customers configure MembershipProviders based on their environment, but still be able to map that MembershipUser to a concrete User model in our database.

Membership.GetUser() will give me access to the logged-in user's Membership.ProviderUserKey. I can use this to relate to a User record. Our custom SQL provider will just return the User.Id, but AD is a different story. In that case, ProviderUserKey is an IdentityReference.

These lookups will happen very frequently, as you can imagine (although caching can assist in reducing the lookups at the database level).

I can't decide which route is better to go: Storing the SID as a varbinary or varchar column. This column would not be a primary key and would not have a clustered index. Knowing that I can index strings pretty well, and reading a SID in string format is certainly nicer than binary. Anyone willing to share how they solved such a situation?

---

Update

I don't know how I missed this SO question when I was searching before I posted, but it seems pretty clear that ActiveDirectoryMembershipProvider and ActiveDirectoryMembershipUser are not quite cut out for the task at hand, as they exist today.

An answer in that SO question linked the following article, where the following was stated:

The relative identifier portion of a SID is unique relative to the domain, so if the domain changes, the relative identifier also changes.

Thus when a User object moves from one domain to another, a new SID must be generated for the user account and stored in the Object-SID property.

However, each group and user has an Object-GUID, which will never change, even if the account is moved. Therefore, it would behoove me to use Object-GUID in my User class, and not Object-SID. Otherwise, someone's User record will be abandoned if they are moved and therefore breaking the relationship between their principal and the data they created.

Unfortunately, ActiveDirectoryMembershipUser doesn't let me get at Object-GUID. So, I'll either have to translate the SID to a GUID after ActiveDirectoryMembershipUser does its work, or create my own MembershipProvider that does everything I need on the spot. Unfortunately, this means I might have to duplicate effort already done for me by ActiveDirectoryMembershipProvider.

Answer 1

Microsoft stores SIDs as varbinary(85) in sys.server_principals

This is also a unique column, so it must have an index...

Answer 2

Sounds like you're making this a lot more difficult than it needs to be. What do you need a SID or GUID for? You already have a unique, perfectly readable identifier for the users account maintained in ActiveDirectory.

It's called "the username". Hopefully it's the same username as stored in your apps "user" table.

Your app just needs to know if that username successfully authenticated with ActiveDirectory. So if they successfully log in - you just store the fact that they are authenticated in your Session variables.

If they are configured to use the db user login, if successful set the same Session variable indicating that they successfully logged in.

No fancy GUIDs or SIDs ... simple.

Ron