my answer is probably dependent upon the answer to this question: Is this an Enterprise application which lives within a network with Active Directory?
IF the answer is yes, then these are the steps I would provide:
1) Create Global Groups for your application, in my case, I had a APPUSER group and an APPADMIN group.
2) Have your SQL Server be able to be accessed in MIXED AUTHENTICATION mode, and then assign your APPUSER group(s) as the SQL SERVER LOGIN to your database with the appropriate CRUD rights to your DB(s), and ensure that you access the SQL SERVER with Trusted Connection = True in your connection string.
At this point, your AD store will be responsible for authentication. Since, you're accessing the application via a TRUSTED CONNECTION, it will pass the identity of whatever account is running the application to the SQL Server.
Now, for AUTHORIZATION (i.e. telling your application what the logged in user is allowed to do) it's a simple matter of querying AD for a list of groups which the logged in user is a member of. Then check for the appropriate group names and build your UI based upon membership this way.
The way my applications work are thus:
- Launching the application, credentials are based upon the logged-in user, this is the primary aspect of authentication (i.e. they can log in therefore they exist)
- I Get all Groups For the Windows Identity in question
- I check for the Standard USER Group -- if this group does not exist for the Windows Identity in question, then that's an authentication FAIL
- I check for ADMIN User Group -- With this existing in the user's groups, I modify the UI to allow access to administration components
- Display the UI
I then have either a PRINCIPLE object with the determined rights/etc on it, or I utilize GLOBAL variables that I can access to determine the appropriate UI while building my forms (i.e. if my user is not a member of the ADMIN group, then I'd hide all the DELETE buttons).
Why do I suggest this?
It's a matter of deployment.
It has been my experience that most Enterprise Applications are deployed by Network Engineers rather than programmers--therefore, having Authentication/Authorization to be the responsibility of AD makes sense, as that is where the Network guys go when you discuss Authentication/Authorization.
Additionally, during the creation of new users for the network, a Network Engineer (or whoever is responsible for creating new network users) is more apt to remember to perform group assignments while they are IN AD than the fact that they have to go into a dozen applications to parse out assignments of authorization.
Doing this helps with the maze of permissions and rights that new hires need to be granted or those leaving the company need to be denied and it maintains authentication and authorization in the central repository where it belongs (i.e. in AD @ the Domain Controller level).