If I have an app that will authenticate users using ActiveDirectoy and will Authorize them and link data with them that are stored in Sql DB.

Which one is best practice ?

  1. To import data repeatedly from AD and store in DB as (Id, UserName) and each time I'll authenticate I connect to AD and make sure its password is OK and them check the role from DB ?.

  2. Keep authenticating from AD but get the GUID of the user and set as foreign key in DB tables ?

  3. Both previous are not correct approach and there's another better solution ?

And what if the system should have the ability to have registered users from AD and registered users in users table in DB ?


The simplest way, and one I've used before, is to store all your user data in the DB but if the user login is managed via AD then leave a black password field so the application knows to authenticate against AD instead of the DB (or use a flag).

You will need to store the AD identifier, which can be domain and sAMaccountname (for older systems) or UserPrincipalName (if you're running new ones).

This also means you can also login against users that are not stored in AD, such a a local application administrator, which is very useful when it comes to installation and testing.


Don't mistake authentication with authorization. Authentication is verifying the user is who they say they are. Authorization is granting or denying access based on the permissions of said person. Verifying that the person is who they say they are through Active Directory is very much authentication.

As a general rule of thumb, you should never take shortcuts for authentication. I assume that we're not talking about a performance issue with accessing Active Directory here, so don't be afraid to check every single time. This also implies that should there be problems accessing Active Directory, nobody can use your program. This is perfectly acceptable!

Therefore, you should really save any information in the database regarding a user that is application-specific such as permissions, but only once the user is authenticated.

  • Actually I'm not mistaken Authentication and Authorization, I know the deference between them and that's why I wrote in my question "authenticate users using ActiveDirectoy" and then "and will Authorize them and link data with them that are stored in Sql DB." because I need to authenticate using AD and the authorization data will be stored in DB (roles and permissions) .. But the link between AD data and DB data is the questionable thing, should it be username or GUID of AD object ? I think it should be username
    – Dabbas
    Jan 15 '18 at 15:22
  • @Dabbas Username may potentially change but the GUID shouldn't. At the same time, GUID isn't readable. You could simply save both; username for readability and GUID for identification.
    – Neil
    Jan 16 '18 at 7:34
  • I think ... GUID will be used just as a foreign key in tables to link with specific user, username saving depends if you want to have hybrid system between AD auth and db auth. if your system depends only on AD for auth, I think it's enough to save GUID as foreign key, username can come from AD after authing.
    – Dabbas
    Jan 16 '18 at 8:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.