Using Active Directory ObjectSid as Primary Key in SQL Server for Data Access via ASP.NET MVC

Question

I am designing an Intranet employee reward web app using ASP.NET MVC C#/MS SQL Server. This will only be accessed from within the network.

Employees and managers will be able to give a reward to other employees for a job well done or for specific employee appreciate days. About 1000 employees total.

I'd like to not require a separate user/pw, so I'm using Windows authentication.

In order to work with/store data about the reward transactions, I'll need to have a separate SQL db.

My plan is to import all employees from Active Directory to SQL Server and use the ObjectSid as the primary key for the Employee on the SQL side. It is my understanding that this ObjectSid will not change unless the employee moves to a new domain (which is highly unlikely at this point). It will look something like -

Employee: 
ObjectSID - PK
username - from Windows AD
firstName
lastName
etc. 

I'll track the reward transactions, employee balances, etc in other tables.

When they access the app, I'm able to grab their Windows username in my view via

@HttpContext.Current.User.Identity.Name. 

I can then use that username to match against the Employee.username, return the ObjectSID (the PK), and proceed to use that as the unique ID to store their activities in the app.

Are there any major hangups that jump out to you with that plan?

Ultimately, I will have minor data maintenance to do when an employee has a name change (marriage), but that is infrequent enough that I don't see it as a major issue.

Any thoughts or ideas on how to approach this differently are appreciated.

Thanks.

• John Zabroski: You should not store just the SID but the AD forest it belongs to. This will prevent potential issues if your company ever merges with another company blogs.msdn.microsoft.com/aaron_margosis/2009/11/05/…

Answer 1

It looks like your question was previously answered here: https://stackoverflow.com/questions/1628595/storing-a-windows-sid-in-a-database-for-lookup

That said, I disagree with the advice in that thread: companies merge, whether it be a takeunder or takeover.

I recommend storing the forest info and the SID, as that will guarantee uniqueness assuming your forest is properly scoped: https://social.technet.microsoft.com/wiki/contents/articles/34981.active-directory-best-practices-for-internal-domain-and-network-names.aspx