Okay first off, apologies for my ignorance - I have no experience with Active Directory/LDAP.
We currrently run a web application using ASP.NET 4 & SQL Server 2005. The application manages skill levels for users. We host it, and all customers access the same application (so SQL stores all users in a single table seperated with CustomerID field)
via a single URL. Some big potential clients are now saying, "We'll buy your product if you can integrate AD/LDAP/Single sign on', but I just cannot figure how to go about this, if it is even possible.
I understand the single sign-on authentication part, e.g. using a connection string to link back to the customers LDAP URL. Its the next part that gets me... Our SQL database has a User table, where all data relating to a user is stored, (cue oversimplified
example):
UserID -- the crucial part
firstName
surname
middleName
DateOfBirth
Country
Site
Department
Gender
HomeAddress
etc etc etc
Many of the above are used to provide detailed reporting capability. I looked at the
property fields that AD provides, but I don't get how to synchronise them with our SQL database (if that's even possible) to actual perform reports...
So my newbie questions to begin with are as follows:
Is integration with my application even possible?
Is it possible without adding users/roles to web.config?
Would the client need to host the application on their intranet?
How would reporting be done when the app would need to query potentially thousands of userIDs across the database then query them back to AD? e.g. Find all people in SiteX who have 100% level in SkillY who's FirstName starts with 'D', then graph the results
Would I need to somehow synchronize the AD data with own our SQL data to achieve the above, e.g. create a webmethod to mirror CRUD user changes into SQL?
Any help would be gratefully appreciated. At least it will give me a starting point on how to proceed.
Many of the above are used to provide detailed reporting capability. I looked at the
property fields that AD provides, but I don't get how to synchronise them with our SQL database (if that's even possible) to actual perform reports...
Best way to do that is to use the samaccountname for your UserID in your database. Just think of Active Directory as another database. You should be able to integrate it with your current application and use it for authentication. Much of the other information
is already stored in AD such as firstname, surename, country, department, etc as part of their user account. And just about everything else can be added using extension attributes. So instead of pointing to your SQL table for users, you can write functions
that look up the same information in AD if they choose the AD integration option. The site does not have to be hosted internally as long as the AD functions pass credtials that give it access to their AD. Using roles is easy as well. You can easily use roles
with AD as well. You can use a current roles setup in a SQL database or create roles based on group membership in AD. See DirectoryServices.AccountManagement for more information.
Single SignOn is a bit different since it allows you access to multiple sites when you authenticate with one. Not done any work with that, so cant help there but im sure there are resources for it.
There should be plenty of resources and books on Active Directory authentication. Check the FAQs thread above the forum or search for information on DirectoryServices.AccountManagement.
Thanks for your detailed answer - its much appreciated. Unfortunately I need some more clarification please on the points you've made...
gww
Much of the other information is already stored in AD such as firstname, surename...
Okay great.
gww
..., country, department, etc as part of their user account and just about everything else can be added using extension attributes
Interesting - I was not aware of extension attributes.
Firstname and surname etc are generally just 'flat' properties. In our existing system, department/site/country are hierarchical representations, e.g. place a person into a new department and the site/country aspect gets handled as part of the database
structure. We have created some very powerful reporting based on the relationships defined across the SQL tables.
Can AD handle the country/site/department relationship in isolation? What if a department exists at multiple sites/countries - how does that work? Can data relationships be established between extension attributes to simulate other software schemas?
gww
Single SignOn is a bit different since it allows you access to multiple sites when you authenticate with one. Not done any work with that, so cant help there but im sure there are resources for it.
Single Sign On seems relatively straightforward as long as the company using AD has a URL that can be accessed externally.
Can AD be 'extended' so that events take place, some automated functionality occurs? For example, when a user account is created/updated/deleted in AD, that change automatically launches a web method in another system? I'm not too bothered about the web
method launching process, but more on what AD can achieve with or without modification.
Thanks again for your help - I'm slowly learning...!
tname and surname etc are generally just 'flat' properties. In our existing system, department/site/country are hierarchical representations, e.g. place a person into a new department and the site/country aspect gets handled as part of the database structure.
We have created some very powerful reporting based on the relationships defined across the SQL tables.
Can AD handle the country/site/department relationship in isolation? What if a department exists at multiple sites/countries - how does that work? Can data relationships be established between extension attributes to simulate other software schemas?
In AD there are a number of extensionattributes listed for each object. You cant change the name but you can assign values to them and record what info you place there. IE: extensionAttribute5 = site
Not sure if I got what you are asking but you can run queries on Active Directory much like you can in SQL. So you can run a report that pulls all users that are listed as being in department no matter where their user object is stored in AD.
Normally objects in AD are stored in OUs. Think of them like folders on a file system. They are used for organizing objects. Example there is a computer OU where are computers are placed. It can then have sub OUs broken down into departments or whatever
you choose. Typically helpful for applying group policies to computers. You can also setup security groups to add objects to and then run actions based on membership to those groups.
Rouchie
Can AD be 'extended' so that events take place, some automated functionality occurs? For example, when a user account is created/updated/deleted in AD, that change automatically launches a web method in another system? I'm not too bothered about the web
method launching process, but more on what AD can achieve with or without modification.
Thanks again for your help - I'm slowly learning...!
AD cant be extended like that. You would probably need to setup some software that ran scripts or setup a windows service to run automated functions.
Fantastic response. I think I'm getting somewhere fast...
gww
In AD there are a number of extensionattributes listed for each object. You cant change the name but you can assign values to them and record what info you place there. IE: extensionAttribute5 = site
That's very interesting that you can't rename them. Can the value be any data type (e.g. string/integer/double etc)? I probably wouldn't need to use these based on the rest of your reply.
gww
Not sure if I got what you are asking but you can run queries on Active Directory much like you can in SQL. So you can run a report that pulls all users that are listed as being in department no matter where their user object is stored in AD.
That's also an interesting point. From your reply though I feel that AD serves the purposes of defining network access abilities perfectly, but isn't particularly well-suited to personnel data management. From our larger customers (e.g. 6000+ users) I
imagine organisational structure & hierarchy can be very different from network structure...!
Does AD have an employeeID field (or related)? If so, is it possible to do something like this in a report (passing in an array of input parameter values?
SELECT users names/surnames/departments WHERE employeeID IN (employeeID1, employeeID2, employeeID3... etc)
Can AD be 'extended' so that events take place, some automated functionality occurs?
gww
AD cant be extended like that. You would probably need to setup some software that ran scripts or setup a windows service to run automated functions.
Yesterday while doing some more research I saw this on Wikipedia:
Active Directory replication is 'pull' rather than 'push', meaning that replicas pull changes from the server where the change was effected.
This obviously fits with your observation and recommendation. Strangely though, wouldn't that mean that my application would then become the central point for managing usernames/passwords/names/surnames, which then pushed the change into AD? When considering
the single sign-on aspect, this almost contradicts the approach needed...?!?
That's very interesting that you can't rename them. Can the value be any data type (e.g. string/integer/double etc)? I probably wouldn't need to use these based on the rest of your reply.
You should be able to enter any type of data in those fields. I am not sure of a limit as to the amount of characters.
Rouchie
That's also an interesting point. From your reply though I feel that AD serves the purposes of defining network access abilities perfectly, but isn't particularly well-suited to personnel data management. From our larger customers (e.g. 6000+ users) I
imagine organisational structure & hierarchy can be very different from network structure...!
It can be used for both. If looking at a user object in ADUC there are specific tabs for oganization, phones, address and the general tab where you enter organizational information.
Rouchie
Does AD have an employeeID field (or related)? If so, is it possible to do something like this in a report (passing in an array of input parameter values?
There is an employeeID attribute. And you can run a query but they look a bit different.
You can see many examples of code on here and many other sites. But you would setup a filter for your AD query such as
The | represents OR. So this filter will search for employeeID for SearchVar OR SearchVar2. Normally you would also include a section to limit the search to only users (objectCategory=User) as well with an & which would mean AND. So the filter would only
pull objects that were users AND contained an employeeID of SearchVar OR SearchVar2.
Most of all you can run a query and dump the info into a dataset and then do any other type of sorting or queries.
Rouchie
Active Directory replication is 'pull' rather than 'push', meaning that replicas pull changes from the server where the change was effected.
This is refering to replication of changes through domain controllers throughout the network. You cannect to AD through one of your domain controllers. And it takes some time for all changes to be replicated throughout the domain. Depending on its size.
You can use your application to manage any aspect of the user objects you wish. Only system adminstrators have direct access to AD and many like myself make web interfaces for common tasks because they can be tedious to do in the UI for active directory.
I have a Self Service page setup just to allow users to update their own user information.
You can use your application to manage any aspect of the user objects you wish. Only system adminstrators have direct access to AD and many like myself make web interfaces for common tasks because they can be tedious to do in the UI for active directory. I
have a Self Service page setup just to allow users to update their own user information.
Ahh that's very interesting. Is your self-service page made using ASP.NET? If so, I can see easily here how such a page could then access a web method in my own application to mirror the updates to personal details.
I read that this attribute is optional for users. Is it possible to tweak AD to make it compulsory?
Not sure about that. You may have to speak with someone who has setup an AD before. AD only needs a few items populated to make a user object in AD, most everything else is optional and there are many. Your sysadmin can show whats available. Its important
to know that if an attribute is not populated it technically does not exist for that user. So in your code you would need to to wrap the code in a try/catch block or test if the property exists in those returned with If SearchResult.properties.contains("employeeIE")
for example. And you can do queries to return all users that do not have such a property populated, such as (!employeeID="*"). I think thats correct.
Rouchie
Ahh that's very interesting. Is your self-service page made using ASP.NET? If so, I can see easily here how such a page could then access a web method in my own application to mirror the updates to personal details.
Yes, its done in asp.net. You can make your application web based or console. In your application you will need to provide credentials of an account that has write access to AD in order to make updates to it. This can be done in code, stored in the web.config
file or in web based applications the application pool can be run under such an account. Typically I make a service account with these permissions that are only used for the application.
A good resource is the asp.net from 4guysfromrolla. But there are tons of resources out there.
Rouchie
Member
359 Points
352 Posts
Help conceptualizing adding Active Directory to an existing (SQL) application
Oct 19, 2012 01:02 PM|LINK
Okay first off, apologies for my ignorance - I have no experience with Active Directory/LDAP.
We currrently run a web application using ASP.NET 4 & SQL Server 2005. The application manages skill levels for users. We host it, and all customers access the same application (so SQL stores all users in a single table seperated with CustomerID field) via a single URL. Some big potential clients are now saying, "We'll buy your product if you can integrate AD/LDAP/Single sign on', but I just cannot figure how to go about this, if it is even possible.
I understand the single sign-on authentication part, e.g. using a connection string to link back to the customers LDAP URL. Its the next part that gets me... Our SQL database has a User table, where all data relating to a user is stored, (cue oversimplified example):
Many of the above are used to provide detailed reporting capability. I looked at the property fields that AD provides, but I don't get how to synchronise them with our SQL database (if that's even possible) to actual perform reports...
So my newbie questions to begin with are as follows:
Any help would be gratefully appreciated. At least it will give me a starting point on how to proceed.
gww
Contributor
2143 Points
458 Posts
Re: Help conceptualizing adding Active Directory to an existing (SQL) application
Nov 11, 2012 02:13 PM|LINK
Best way to do that is to use the samaccountname for your UserID in your database. Just think of Active Directory as another database. You should be able to integrate it with your current application and use it for authentication. Much of the other information is already stored in AD such as firstname, surename, country, department, etc as part of their user account. And just about everything else can be added using extension attributes. So instead of pointing to your SQL table for users, you can write functions that look up the same information in AD if they choose the AD integration option. The site does not have to be hosted internally as long as the AD functions pass credtials that give it access to their AD. Using roles is easy as well. You can easily use roles with AD as well. You can use a current roles setup in a SQL database or create roles based on group membership in AD. See DirectoryServices.AccountManagement for more information.
Single SignOn is a bit different since it allows you access to multiple sites when you authenticate with one. Not done any work with that, so cant help there but im sure there are resources for it.
There should be plenty of resources and books on Active Directory authentication. Check the FAQs thread above the forum or search for information on DirectoryServices.AccountManagement.
Rouchie
Member
359 Points
352 Posts
Re: Help conceptualizing adding Active Directory to an existing (SQL) application
Nov 13, 2012 08:48 AM|LINK
Thanks for your detailed answer - its much appreciated. Unfortunately I need some more clarification please on the points you've made...
Okay great.
Interesting - I was not aware of extension attributes.
Firstname and surname etc are generally just 'flat' properties. In our existing system, department/site/country are hierarchical representations, e.g. place a person into a new department and the site/country aspect gets handled as part of the database structure. We have created some very powerful reporting based on the relationships defined across the SQL tables.
Can AD handle the country/site/department relationship in isolation? What if a department exists at multiple sites/countries - how does that work? Can data relationships be established between extension attributes to simulate other software schemas?
Single Sign On seems relatively straightforward as long as the company using AD has a URL that can be accessed externally.
Can AD be 'extended' so that events take place, some automated functionality occurs? For example, when a user account is created/updated/deleted in AD, that change automatically launches a web method in another system? I'm not too bothered about the web method launching process, but more on what AD can achieve with or without modification.
Thanks again for your help - I'm slowly learning...!
gww
Contributor
2143 Points
458 Posts
Re: Help conceptualizing adding Active Directory to an existing (SQL) application
Nov 13, 2012 06:57 PM|LINK
In AD there are a number of extensionattributes listed for each object. You cant change the name but you can assign values to them and record what info you place there. IE: extensionAttribute5 = site
Not sure if I got what you are asking but you can run queries on Active Directory much like you can in SQL. So you can run a report that pulls all users that are listed as being in department no matter where their user object is stored in AD.
Normally objects in AD are stored in OUs. Think of them like folders on a file system. They are used for organizing objects. Example there is a computer OU where are computers are placed. It can then have sub OUs broken down into departments or whatever you choose. Typically helpful for applying group policies to computers. You can also setup security groups to add objects to and then run actions based on membership to those groups.
AD cant be extended like that. You would probably need to setup some software that ran scripts or setup a windows service to run automated functions.
Rouchie
Member
359 Points
352 Posts
Re: Help conceptualizing adding Active Directory to an existing (SQL) application
Nov 14, 2012 09:39 AM|LINK
Fantastic response. I think I'm getting somewhere fast...
That's very interesting that you can't rename them. Can the value be any data type (e.g. string/integer/double etc)? I probably wouldn't need to use these based on the rest of your reply.
That's also an interesting point. From your reply though I feel that AD serves the purposes of defining network access abilities perfectly, but isn't particularly well-suited to personnel data management. From our larger customers (e.g. 6000+ users) I imagine organisational structure & hierarchy can be very different from network structure...!
Does AD have an employeeID field (or related)? If so, is it possible to do something like this in a report (passing in an array of input parameter values?
Yesterday while doing some more research I saw this on Wikipedia:
Active Directory replication is 'pull' rather than 'push', meaning that replicas pull changes from the server where the change was effected.
This obviously fits with your observation and recommendation. Strangely though, wouldn't that mean that my application would then become the central point for managing usernames/passwords/names/surnames, which then pushed the change into AD? When considering the single sign-on aspect, this almost contradicts the approach needed...?!?
Thanks again for sharing your knowledge with me.
gww
Contributor
2143 Points
458 Posts
Re: Help conceptualizing adding Active Directory to an existing (SQL) application
Nov 15, 2012 01:43 PM|LINK
You should be able to enter any type of data in those fields. I am not sure of a limit as to the amount of characters.
It can be used for both. If looking at a user object in ADUC there are specific tabs for oganization, phones, address and the general tab where you enter organizational information.
There is an employeeID attribute. And you can run a query but they look a bit different.
You can see many examples of code on here and many other sites. But you would setup a filter for your AD query such as
The | represents OR. So this filter will search for employeeID for SearchVar OR SearchVar2. Normally you would also include a section to limit the search to only users (objectCategory=User) as well with an & which would mean AND. So the filter would only pull objects that were users AND contained an employeeID of SearchVar OR SearchVar2.
Most of all you can run a query and dump the info into a dataset and then do any other type of sorting or queries.
This is refering to replication of changes through domain controllers throughout the network. You cannect to AD through one of your domain controllers. And it takes some time for all changes to be replicated throughout the domain. Depending on its size.
You can use your application to manage any aspect of the user objects you wish. Only system adminstrators have direct access to AD and many like myself make web interfaces for common tasks because they can be tedious to do in the UI for active directory. I have a Self Service page setup just to allow users to update their own user information.
Rouchie
Member
359 Points
352 Posts
Re: Help conceptualizing adding Active Directory to an existing (SQL) application
Nov 15, 2012 02:30 PM|LINK
Excellent response again - thank you; I really appreciate you sharing this information.
I read that this attribute is optional for users. Is it possible to tweak AD to make it compulsory?
That's makes complete sense - thank you.
Ahh that's very interesting. Is your self-service page made using ASP.NET? If so, I can see easily here how such a page could then access a web method in my own application to mirror the updates to personal details.
gww
Contributor
2143 Points
458 Posts
Re: Help conceptualizing adding Active Directory to an existing (SQL) application
Nov 15, 2012 04:31 PM|LINK
Not sure about that. You may have to speak with someone who has setup an AD before. AD only needs a few items populated to make a user object in AD, most everything else is optional and there are many. Your sysadmin can show whats available. Its important to know that if an attribute is not populated it technically does not exist for that user. So in your code you would need to to wrap the code in a try/catch block or test if the property exists in those returned with If SearchResult.properties.contains("employeeIE") for example. And you can do queries to return all users that do not have such a property populated, such as (!employeeID="*"). I think thats correct.
Yes, its done in asp.net. You can make your application web based or console. In your application you will need to provide credentials of an account that has write access to AD in order to make updates to it. This can be done in code, stored in the web.config file or in web based applications the application pool can be run under such an account. Typically I make a service account with these permissions that are only used for the application.
A good resource is the asp.net from 4guysfromrolla. But there are tons of resources out there.
Rouchie
Member
359 Points
352 Posts
Re: Help conceptualizing adding Active Directory to an existing (SQL) application
Nov 16, 2012 08:11 AM|LINK
Thank you very much that's been a really interesting thread and I've learned loads.