We have an existing ASP.NET web app that is using Microsoft.AspNet.Identity framework. The previous developer wrote the code for this and unfortunately I don’t have much experience with it. It currently allows users to create an account on our app and that gets saved to the AspNetUsers table. I’m assuming that’s the default way accounts are stored with this framework.
This has been working well so far, but we want to expand our functionality in a way that we think would be better if accounts were stored in Active Directory. Ideally users would have a single login that would allow the following:
- Login to our web app.
- Ability to change password via the web app.
- Log into SQL Server Reporting Services portal.
- Log into SSRS server when building reports in Report Builder.
- Provide database access as follows:
- Only be able to see and SELECT from a handful of views.
- These views would be able to filter data based on the user.
- There would be groups that the user belongs to. Each group has ownership of a schema in the database. Members of the group would have full access to that schema.
With the current AspNetUsers table implementation, users can create accounts, login and reset password. For the SSRS functionality we’ve been creating a separate user in AD manually. So at this point, the user has two accounts to deal with, though they could use the same username/password so that it seems like one.
On the database access it is a little complicated, but seems to work:
First, our users belong to one or more “Organizations”. And really that’s pretty much like a group, but it doesn’t use any kind of built in group functionality. We basically have an Organizations table in the database and then an OrganizationUser table that links AspNetUsers to Organizations.
Each Organization has a Data Source in SSRS. Depending on what Organization the user is writing reports for, they will choose the appropriate Data Source. Organizations have corresponding local DB logins and that is the login used by the SSRS Data Source.
On the database itself, the Organization login has ownership of it’s own schema. The schema is there so that users can store and retrieve tables of their own design. This is mainly for pre-computing of data for use in reports. The login also has access to a few views in the dbo schema. Those views utilize the DB login to determine what Organization it’s dealing with. That’s used to filter out any data that is “owned” by an AspNetUser entry that isn’t linked to the Organization.
As you can see, we also end up with a third Organization login on the database which is not really ideal either. Plus, we’re also seeing a need to have a user-level login because we also want to add a database view that only shows the users data rather than the data for the entire Organization.
I should also mention that I’d really like if, when a user creates an account, it gets created in AD rather than the database. I haven’t been able to find an example of being able to do this. There seems to be a lot of examples on how to login to AD, but not to create the account in the first place. I suppose I could keep the existing AspNetUsers implementation and write some AD code alongside all the existing endpoint code, however that seems like a waste if there was some way to just do it all in AD.
I was going to post in StackOverflow to see if anyone could help me on getting Microsoft.AspNet.Indentity to create users in AD, but I decided it might be a good idea to get some feedback on this design before I go down that route as I’m wondering whether it’s a good idea or not.
I know one of the concerns my co-worker brought up was getting too many accounts in AD. I don’t think it’s a big issue. This isn’t the type of application that would have a lot of users. His other concern was getting tied too close to a Microsoft stack, but I don’t think that’s a big problem either.