Querying AD from SQL Server

This is a great how-to article on querying Active Directory from within SQL Server. I've written in the past about using Virtual Directory technology to query SQL data via LDAP. This is the reverse and I can envision many use-cases where this would be useful. This isn't exactly new technology, but it's a new write-up on how it's done and very easy to follow.

For example, you could use this approach to extend the information available to an application without doing any data synchronization or introducing new data sources. If the application's logon ID is the user's email address, you could query AD based on that email and get info about the user's group memberships, attributes, manager, location, etc. and have that returned to the application as if the data were stored in the local app's database.

...another useful approach to keep in your development toolbox.

Weighing in on Persistent Cache

Ash, my experience says that Mark is correct. I believe the top vendors can all brag about similar throughput. But, my understanding is that's only what the VD puts on top of the process. There's still the back end data lookups, etc. To Mark's later point, that may not be a big deal either if those sources perform well.

Let's use a telecom example:

In a scenario where the VD serves an attribute that is a composite of multiple attributes from various sources (a mixed ODBC and LDAP call) or across numerous sources (customer databases from companies that merged or partner) and the attribute is needed to make a decision (does the subscriber get this feature) in real-time (the time between hitting "send" and hearing a ring) for millions of requests each second, effective use of cache can help – even though throughput is already relatively quick. In many (perhaps most) enterprise identity infrastructure uses, cache may not be of enormous value – or at least it's not the most compelling reason to use a Virtual Directory.

I can tell you that customers ask for it whether they need it or not. Probably because they have performance or availability concerns. But, from what I've seen the performance concerns are usually unfounded (unless the back end systems have serious problems). And VD cache isn't a great way to provide redundancy because it's implemented at an attribute level and cached based on use. If the idea is to put the entire data set somewhere else, you could argue that it'd be better to just have another directory instance there and do real-time synch (replication).

My opinion is that it's a nice feature to have in the tool bag when needed, but it's not always needed.

Metadirectories: What's left to say?

If you haven't been following the flurry of conversation since my post last week stating that metadirectories aren't dead, well you're in luck. We couldn't have asked for a better recap of the conversation than the one provided by Ian Yip (although I think he gave Nishant a bum rap on this one).

There were so many different angles explored that I'm not really sure where to start or what's left for me to say.

• I'll restate that I see perfect use-cases for both metadirectory and virtual directory. Now and in the near future. In the far future, there will probably be better ways to achieve the same goals.


• Also, it sounded like Clayton took my comments to mean that "everyone needs to be using Active Directory for everything", which was (I think obviously) not the intent. My point is that although the top 500 or 1000 companies may have a number of directories for various strategic uses, there are probably 20x that number of companies that use only Active Directory as the central and primary user store because of it's network and email integration. And those companies might like for their application vendors to offer direct plug-in to AD as an option.
  
  Plugin to LDAP might be another good option and virtual directory technology would be a great enabler to incorporate various vendors, schemas and even relational databases through that single mechanism. But those mid-market companies probably would prefer not to take on the complexity of virtual directory (even if relatively simpler than writing numerous connections) if they could just use AD natively. And I think some percentage of the Fortune 1000 would see AD as strategic enough to ask the same as well.
  
  That's my guess based on a customer perspective as opposed to a software vendor's ideal state of architecture. And I don't think this is limited to companies who are 100% Microsoft shops. AD just has a very far reach and because it holds email in most of those companies it will already have an account for every employee, be available, etc..
  
  I don't think any of this should be seen as threatening to the role that stand-alone directories or meta- or virtual- directories play. The difference in viewpoint between me and Nishant & Clayton (if I can group them together) might be in the types of customers we've been talking to. There are still a ton of companies out there that aren't super-strategic about their Identity Management architecture. Or that just want a point solution because it fits the current business needs.

I think that's it. For now.

[UPDATE] - forgot one:

• Bavo, I wasn't requiring that the HR database is the primary source for account creation and status. I also wasn't telling you that the HR database should be the primary source for Identity information. (However, I think it's more true than you think.) I was stating a requirement (one that I've seen many times). HR has been deemed THE authoritative source for employee existence in a majority of the companies I've worked with. My experience seems to differ from yours. [That's at least interesting! ...and one of the reasons I blog – to engage with people that have different experiences.]
  
  Yes, companies struggle with getting HR updated for the employee's start date. But, I've actually seen more than one customer implement a complicated AD-to-HR-back-to-AD process to accommodate for the issue. One customer integrated the candidate review system into the provisioning system. I think the reason for HR being authoritative is usually for deprovisioning. They want a disabled HR account to ripple downward.
  
  I think what you call the IDM system assumes a provisioning solution with work flow and its own internal store. These are luxuries that are not always available. In my scenario, the cost and complexity of a provisioning solution is probably overkill based on the requirements. And that's my point. There are scenarios where the simplicity of a metadirectory are not only sufficient to meet the requirement but actually a bit more of an elegant way to meet the requirements.

OK, now I'm really done for the night.

Proliferation of Multiple LDAPs

I was questioned today by someone who read my post on Synchronization versus Virtualization, which is topical given the recent blog debates by many of the big names in identity blogging on whether metadirectories are dead. Back then, I was trying to convince the world that there are cases that require a virtual approach. Now, the tables seem to have turned. I've stayed quiet on that debate, but I believe there's still a place for each method. Honestly, I'm not sure why there's such intensity behind that debate – they're just tools. My idea for Enterprise Identity Services Architecture included a layer for Identity Data Services, which is analogous to the proposed Identity Hub or Bus. It has a box for both synchronization and virtualization because there are use cases for both types of technologies often within the same infrastructure.

End of digression.

The question proposed was about how large enterprises (hundreds of thousands of users) are addressing the proliferation of LDAP directories in their environments. The person asking comes from a well-known and reputable company. The first thing I thought is here's the exact reason why we still need metadirectories. Many companies still haven't dealt with the user-store sprawl. In a general sense, I think metadirectory technologies can help get you to a place where you refine and consolidate your infrastructure and can then leverage virtual directory technologies where appropriate for applications that need access to data in multiple stores and/or multiple formats.

What the questioner was really looking for is research that discusses how other companies have handled the challenge. Here's my answer:

My recommendation is to find an experienced group of consultants who have tried various approaches with different clients. There are a number of them out there and I'm happy to give recommendations privately based on geography, technology, comfort-level with small vs. large companies, etc.. Whether you engage them contractually or enter into discussions via user groups, trade shows, etc., real world experience is priceless. Anybody that pushes one particular approach to this problem is probably biased (either by product or by their own limited experience). My experience suggests that most scenarios require a unique approach based on business goals, actual technologies, future plans, etc.. But you probably need someone to spend some time understanding your own scenario before recommending an approach.

Some questions you'll want to explore:

• Which data stores have overlapping data and which are unique?
• Does it make sense to consolidate?
• Is the data mappable across systems? Do they share unique identifiers?
• Where can multiple applications share a single store?
• Where do given applications require access to data in multiple stores?
• What applications or uses are coming in the future?
• Which stores are used for critical apps? What is the up time demand?
• In what format is the data stored?

The answers to these questions will drive your architectural decisions and help you prioritize next steps.

If you'd like to comment and provide a pointer to field research, whitepapers, or contact info for how you can help with this situation, feel free.

More on LDAP and the Novell-Microsoft Bridge

While at Tech-Ed, I picked up some literature on the efforts of Novell and Microsoft to build bridges with each other toward interoperability. I found that they also have a web site on this topic where you can still read the original press releases and FAQs from November 2006. Conspiracy theories aside, interoperability is probably a good thing.

While I was looking for more info on their planned directory & identity interoperability, I came across an article titled Novell eDirectory vs. Microsoft Active Directory. I can assure you that it's not part of the interoperability literature. In fact, I wouldn't be surprised if it's soon taken off line to preserve the recently positive flow of energy between these two companies. While it's clearly and unapologetically written to sway would-be buyers toward Novell's directory product, it's quite an interesting read for those evaluating LDAP options. If you read my introduction to LDAP directories and are ready for a more thorough drill-down that doesn't require you to read the LDAP RFC, it's a good next step. Novell provides a thorough business level view of what to look for in a directory server.

Incidentally, I didn't find much on their plans for directory interoperability. From what I could find, it sounds like any identity interoperability will initially be at the user access layer and not at the data storage layer. If you have any more details, please comment!

What is a Directory? ...LDAP, AD and ADAM

Note: This is not content for LDAP professionals. This is
content for the LDAP-confused. Proceed with caution.

Overview

An LDAP directory is simply a type of database that is hierarchical in structure. LDAP is the protocol used to interact with that database. So, when you hear someone refer to putting data into LDAP, what they really mean is using the LDAP protocol to put data into a directory (a hierarchical database that communicates via LDAP).

Relational Databases

The databases used most frequently across today's IT landscape (Oracle, SQL Server, MySQL, etc.) are relational in structure. That is, data is stored in tables and data is connected to other data by way of relationships between tables. For example:

In this sample database, we see that Joe Johnson is a Clerk that reports to John Smith. Joe's job title is not in the same table as his name, but we know what it is because of the relationship between the tables. This is a relational data structure and it's the structure that's commonly used when we use the term database.

LDAP Directory Structure

An LDAP directory is hierarchical in structure. Joe's data would look like this in a directory:A directory uses the concept of an object to represent Joe's data. The directory schema defines the type of objects that will be allowed in this directory and what attributes are associated to those objects. Above we see that an object exists for Joe Johnson with attributes for employee number, first name, last name, job title and manager.

Directory Servers

There are a number of directory servers available on the market, including but not limited to:

• Sun Directory Server
• Novell eDirectory
• Oracle Internet Directory
• IBM Tivoli Directory Server
• OpenLDAP
• Fedora Directory Server

Note: I purposely left Microsoft off this list because I'll
cover AD and ADAM in the next section.

Each of the directory servers listed above (and most others) support a common protocol for storing, accessing, modifying and managing data. This protocol is LDAP. By standardizing on a common protocol, application developers can write their applications to store data in any LDAP compliant directory server. Organizations can choose to implement whichever directory server they prefer and feel fairly confident that an LDAP-ready application will be able to consume or interact with their data.

Vendor-neutrality is theoretically achievable with relational database servers as well. I say theoretically because the big database vendors provide add-ons to the standard SQL language that are convenient for developers but sometimes challenging for portability. The main reasons the industry moved away from relational and toward directories for some uses are as follows:

• Query Speed: LDAP directories typically provide faster query response times
• Ease of Management: LDAP's hierarchical structure makes it easy to assign rights and privileges to subsets of users as well as to enable delegated administration over portions of the directory tree.
• There are probably others, but I believe these are the two big ones - please let me know if I'm missing anything here.

Microsoft Active Directory

With the release of Windows 2000 and the migration from Windows NT as their business networking platform, Microsoft introduced Active Directory (AD). AD is an LDAP directory at its core but it doesn't necessarily play by all the same rules. Because AD is used to manage a network environment - authentication, access rights, user management, etc., AD needed to be much more than just a database.

Note: AD is a predominant part of most IT organizations today. As I travelled to various organizations as an IdM consultant, I almost never encountered an organization that didn't have a mature AD environment containing some of the most up-to-date employee data in the entire organization. Based on my experience, AD has become the de-facto enterprise directory for most organizations.

As the identity management market grew throughout 2000 and 2001, organizations realized the need to leverage LDAP directories more and more. Because AD was already widely deployed and held good data, many organizations wanted to leverage AD -- or at least AD data -- for their identity infrastructure. AD administrators however recognized the need to protect their AD infrastructure and most decided against using AD for anything outside of its core network management role. This is why Microsoft introduced ADAM.

Microsoft ADAM

ADAM (Active Directory Application Mode) is based on the same core LDAP technology on which AD is based. But ADAM installs without all the overhead of the OS and network management capabilities that AD provides. ADAM is a stand-alone LDAP directory store similar to those provided in the list above. ADAM does not require AD to run -- it's not an extension of a given AD environment. But, it can be used to augment the functionality of AD. For example, some organizations use ADAM to store employee information for white pages or other applications that they prefer not to query AD directly. Other organizations use ADAM to store non-employee data while relying on AD for employee information. One interesting use of ADAM is as an authentication proxy. You can send an authorization request to ADAM and have it pass the request to AD - thereby leveraging AD credentials while disallowing applications to connect directly to AD.

Conclusion

A directory is just a type of database. LDAP is a way to communicate with a directory. ADAM is a stand-alone directory and not a feature of AD. There are lots of LDAP directory varieties to choose from.

Let me know if I should include additional info here. My main point was to tackle the simple points made in the conclusion. I run into confusion on those topics quite often.

re: Synchronization versus Virtualization

In his entry on Novell’s Cool Blogs, Volker Scheuber discusses the use-cases for virtual directories versus metadirectories and asks for input. Considering my number of previous posts on this topic, I felt an obligation to respond.

I've done a significant amount of services work around both metadirectory and virtual directory technologies. My last company had both and my current company offers neither. The bottom line is that if I were designing an IdM solution, I would want both options to be available. As with any enterprise technology, there's usually more than one way to accomplish a given goal. Most IdM data integration requirements can be met with either meta or virtual technologies. However, there are some scenarios that, for all practical purposes, require one or the other. A while back, I posted some common scenarios where a virtual directory can be really useful.

Even for some of these scenarios though, you may be able to achieve a similar result by standing up another data store and setting up data sync rules with a metadirectory. But it often requires a significant amount of programming that would need to be maintained in addition to the new data store (LDAP or other) and related infrastructure.

Here’s an example:

Let’s say you were deploying a COTS application for insurance claim processing and security is important. The app uses an Oracle database for system data and is built to leverage an enterprise LDAP store (eDirectory, Active Directory, etc.) for authentication. Each employee that needs access to the system already has an account and credentials in the enterprise directory (ED). The system leverages the ED’s groups to grant or deny access. And of course the ED user needs to be enabled.

Due to the sensitive nature of the material, a requirement is made that the system should not allow access to the app unless the employee is punched-in to the company time-clock system. So, if an employee were to piggy back off of a manager’s home VPN connection, they could not access the system unless physically punched-in at work where all claim processing personnel are monitored. The time-clock system has a MySQL database with both system and user data. The APIs for the claim system and the time-clock system are both difficult to work with and complex to maintain.

What are the various ways to meet this requirement?

You could potentially try to work with the API’s and write a custom authentication module that would connect to the time-clock system and verify that a user is punched in before granting access. This would require some internal training, development time and will need to be maintained as either vendor releases new versions in the future.

You could implement a metadirectory solution that sets the ED user as disabled each time status changes in the time-clock system. This may require some custom code, but should be fairly easy to implement. However, we don’t want to disable users in the ED each time they’re punched out. They still need access to HR benefit info and other systems. Not to mention that disabling a user in the ED may trigger other unwanted events to occur (de-provisioning).

You could have the metadirectory solution switch to a different attribute instead of disabling the user, but you’re back to writing a custom authentication module. Also, these metadirectory options will likely introduce some latency because metadirectory sync jobs are typically scheduled. So, there may be some window of time between when the user punches out and when the system denies access. This time window can be exploited to circumvent the intended security controls.

Or – you could have the claims app connect to a virtual directory that is configured to join data from the ED and the MySQL database in a way that is transparent to the claims app. The app sees the virtual directory as an enterprise LDAP directory, but when the user is not punched in, the user appears to be disabled. This is generally a very simple thing to configure for a virtual directory. And if the claims app expects a single group to grant or deny access but the ED groups are arranged differently, the virtual directory can display group memberships however is most appropriate for the app.

Let’s further suppose that the business rules are changed to grant restricted access for the user when punched in but at an alternate location. The virtual directory can be easily configured to set a flag indicating restricted access based on the requestor’s IP address. This is not easily accomplished with a metadirectory solution.

And it’s a plus that the same virtual directory can be used to display user data differently for any number of systems that need access to it – and without altering or moving the data. As we move toward increased regulations and encrypting sensitive data where it lives, reducing the number of data stores that need to be managed and encrypted is an advantage in itself.

Having said all that, I can come up with a number of examples where a metadirectory is clearly a better option than a virtual directory, but I think those are generally easier to see.

Hope this is useful.

One more post on Virtual vs. Meta

So, I already wrote that the question of Virtual vs. Meta is not the right question - they are complimentary solutions. One more thought on identifying where each would fit.

Every organization embarking on an IdM journey needs to begin by identifying data stores and collecting, cleansing, transforming & reconciling data. At this stage, traditional synch tools (metadirectory) are probably the right tool for the job.

If you already have a reasonably good set of data and are looking to provide additional or customized views into that data (via LDAP, SOAP, etc.), then Virtual Directory is probably the answer.

Of course, this is a very generalized view, but I think a good starting point. One of the things that make this question confusing is the functional overlap between the two solutions -- and there is plenty. But the point is to find the best fit solution for a given set of business challenges.

The Value of Virtual: Part II

For the past week or so, I've been thinking about the value proposition for virtual directory technology (Virtual-D). Since I was first introduced to it, Virtual-D was presented to me as a new way to sync data -- an alternative to metadirectories and traditional sync tools. So, I initially thought about its value in relation to that of metadirectory/sync (as in Virtual-D vs. Sync).

Today, a co-worker and I discussed the significant overlap in the two feature sets and the various scenarios that might call for one or the other. Our enlightened conclusion was that Virtual-D and sync tools are complimentary parts of a complete IdM solution.

Then, on the drive home, a light bulb went on. Virtual-D doesn't replace metadirectory, it replaces... [drum role] directory. Hence the name. In an end-to-end IdM solution where you might want multiple directory instances with application-specific attributes and/or security mechanisms, you can replace much of the cost and complexity of numerous directory instances with a Virtual-D solution.

If the goal is to LDAP-enable an application (e.g. SSO, white pages) with enterprise identity information from existing clean data stores, Virtual-D is much less complicated to implement than traditional sync solutions. It doesn't require another data store and it helps circumvent typical political data ownership issues. However, Virtual-D requires clean, current data.
So, if you don't have good identity data available, you might look to aggregate data from the numerous data sources around your enterprise and to create an enterprise directory (or database). This is probably best accomplished using traditional data sync tools. Then, Virtual-D can use that newly compiled identity data store to expose relevant subsets of your identity data to your various applications. And without the need for additional directory instances. And it does so regardless of the data layout or the technology used by the sync tools.

With Virtual-D in the toolbox, it's easy to see why your metadirectory and your enterprise directory don't actually need to be directories at all. They can be relational data solutions. It's easier to store, manage, and sync data in relational formats. And most companies already have relational database expertise (and usually even licenses). When it's time to expose the data to an application, Virtual-D presents it in LDAP format achieving application interoperability and minimizing risk by presenting only relevant attributes to each app.

The Value of Virtual

I'm working on a whitepaper discussing the business value for virtual directory technology. There seems to be a lot of buzz and some misconceptions about the technology and its business case. MaXware is in the unique position of having both a true virtual directory product and a traditional data-synch product (commonly termed metadirectory). So, I'm hoping we can provide a perspective that, while not completely unbiased, will present both technologies as having real value in certain business scenarios. Perhaps, we'll even lay out the scenario where the two technologies converge. If I had any readers, I would ask what you'd like to see in such a whitepaper. Of course, if you do happen to find this post, please do let me know your thoughts.