7

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.

Community
  • 1
  • 1
moribvndvs
  • 42,191
  • 11
  • 135
  • 149

3 Answers3

7

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

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

gbn
  • 422,506
  • 82
  • 585
  • 676
  • This is a good answer for my original post, so I'll give you credit. Unfortunately after researching this more, it looks like I don't want SID at all (see Update). Thanks. – moribvndvs Oct 27 '09 at 05:41
1

username is the LAST thing you want to index on.

SIDs only change in an AD when you change a user from one domain to another. RIDs are split into 2 groups - inbuilt (< 1000) and user RIDs. Pre-defined users such as Administrator, Guest etc always have the same RID.

If you want to handle movement of users etc, then GUID is the way to go.

username can be changed at any time in Users and Groups management.

this is different to the object name, which is invariant, but I don't believe is mandated unique across a forest. You can have any number of John Smith users.

I'd look into the ADSI objects. These are COM objects which should be accessible from ASP. MSDN explains pretty well. an ADSearch object can be used to return user attributes (e.g. including DN) from a GUID.

Adrien
  • 1,061
  • 8
  • 11
  • Thanks for the comment. I'm definitely interested in the Object-GUID, I was just bummed that it's not available to me using the built-in AD membership providers. I ended up creating a derivative provider that swaps the SID for the GUID. – moribvndvs Dec 14 '10 at 19:59
-4

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 Savage
  • 10,923
  • 4
  • 26
  • 35
  • 5
    Usernames can and do change (think about a user's last name changing after they get married). – moribvndvs Oct 27 '09 at 06:27
  • Yes they do, and depending how they handle that in Active Directory your SID and/or GUID would change as well. Don't avoid the simple solution simply because there is some maintenance involved - because there always is some. The simpler your solution - the simpler the maintenance. – Ron Savage Oct 27 '09 at 14:23
  • 2
    Hence my update. Object-SID can change, and therefore I won't be using it. Object-GUID will not change, unless an administrator changes it or recreates the account. I'm fine with leaving it to a maintenance task for that particular situation, since it will be considerably more seldom then a renamed account. However, if I use the name, it changes, and they log in the system will create a new User record and now I've got duplicated/orphaned data. We can't afford to fix or clean up data every time this happens, if we can avoid it up front. Believe me, I'm not avoiding simple purposely. – moribvndvs Oct 27 '09 at 15:21
  • 1
    @RonSavage The GUID isn't ever supposed to change, and the SID only changes when accounts are moved between domains. https://technet.microsoft.com/en-us/library/cc961625.aspx Usernames not only change, but new hires sometimes get usernames that once belonged to previous employees. If they inherit old employee history because of that, that's a real problem. – brianary Jan 22 '15 at 22:15
  • 1
    The GUID will never change, so it is the best choice. You should also save the username in a separate place (column or table). Because when the user-account is deleted in ActiveDirectory, you won't be able to identify the user anymore. It's also more convenient and faster to be able to get an human-readable description directly with your SQL Server. Just keep-in mind that this description could be not uptodate. – Marco Guignard Dec 01 '15 at 13:12