将 Windows SID 存储在数据库中以供查找
我有一个 ASP.NET MVC 应用程序，我需要允许客户根据其环境配置 MembershipProviders，但仍然能够将该 MembershipUser 映射到我们数据库中的具体用户模型。
Membership.GetUser() 将使我能够访问已登录用户的 Membership.ProviderUserKey。我可以用它来关联用户记录。我们的自定义 SQL 提供程序将仅返回 User.Id，但 AD 则不同。在这种情况下，ProviderUserKey 是一个 IdentityReference。
正如您可以想象的那样，这些查找将非常频繁地发生（尽管缓存可以帮助减少数据库级别的查找）。
我无法决定哪条路线更好：将 SID 存储为 varbinary 或 varchar 列。该列不是主键，也没有聚集索引。我知道我可以很好地索引字符串，并且以字符串格式读取 SID 肯定比二进制更好。有谁愿意分享他们是如何解决这种情况的吗？
更新
我不知道我是如何错过的 这个SO问题当我在发布之前进行搜索时，但很明显ActiveDirectoryMembershipProvider和ActiveDirectoryMembershipUser不太适合手头的任务，正如它们今天所存在的那样。
该问题的答案链接了以下文章 ，其中规定了以下内容：
a的相对标识符部分 SID相对于域是唯一的， 所以如果域发生变化，相对的 标识符也会改变。
因此，当用户对象从一个 域到另一个域，必须有一个新的 SID 为用户帐户生成并 存储在 Object-SID 属性中。
但是，每个组和用户都有一个对象 GUID，即使帐户移动，它也永远不会改变。因此，我应该在我的 User 类中使用 Object-GUID，而不是 Object-SID。否则，如果某人的用户记录被移动，那么他们的用户记录将被放弃，从而破坏其主体与其创建的数据之间的关系。
不幸的是，ActiveDirectoryMembershipUser 不允许我获取 Object-GUID。因此，我要么必须在 ActiveDirectoryMembershipUser 完成其工作后将 SID 转换为 GUID，要么创建我自己的 MembershipProvider 来完成我当场所需的一切。不幸的是，这意味着我可能必须重复 ActiveDirectoryMembershipProvider 已经为我完成的工作。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
Microsoft 在
sys.server_principals中将 SID 存储为 varbinary(85)这也是一个唯一的列，所以它必须有一个索引......
Microsoft stores SIDs as varbinary(85) in
sys.server_principalsThis is also a unique column, so it must have an index...
用户名是您要索引的最后一个内容。
仅当您将用户从一个域更改到另一个域时，AD 中的 SID 才会发生变化。 RID 分为 2 组 - 内置 (< 1000) 和用户 RID。预定义用户（例如管理员、访客等）始终具有相同的 RID。
如果您想处理用户等的移动，那么 GUID 是最佳选择。
用户名可以在用户和组管理中随时更改。
这与对象名称不同，对象名称是不变的，但我不认为在整个森林中强制要求唯一。您可以拥有任意数量的 John Smith 用户。
我会研究 ADSI 对象。这些是可以从 ASP 访问的 COM 对象。 MSDN 解释得很好。 ADSearch 对象可用于从 GUID 返回用户属性（例如，包括 DN）。
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.
听起来你让这件事变得比需要的要困难得多。您需要 SID 或 GUID 有何用途？您已拥有在 ActiveDirectory 中维护的用户帐户的唯一且完全可读的标识符。
它被称为“用户名”。希望它与存储在应用程序“用户”表中的用户名相同。
您的应用程序只需要知道该用户名是否成功通过 ActiveDirectory 进行身份验证。因此，如果他们成功登录 - 您只需将他们已通过身份验证的事实存储在您的会话变量中。
如果他们配置为使用 db 用户登录，如果成功设置相同的会话变量，表明他们已成功登录。
没有花哨的 GUID 或 SID ...简单。
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.