MySQL with Integrated Windows Authentication

[JamieHowarth0]

I have been trying to find a solution to this on the Internet for months. Literally, ages and ages and ages, praying that someone in the open-source community has enough knowledge to put together an LDAP connector for MySQL Community to connect to Active Directory. Alas, nothing. So I figured I'd try doing something similar.

My business problem is/was as follows:
I use Active Directory on Windows Server 2003 to manage my small corporate network, including Exchange Server email addresses, file permissions and so on.
I wanted to build a customer intranet which would connect the world of my internal business - staff communications, software code etc. - with an interface where my customers could see what I was actually doing for them, how long it was taking, how much it was costing, whilst keeping track of all communications regarding a project. In short, full-on business management, project management, cashflow and payment processing, as well as version tracking and comms management.

Before you ask, I never like to set myself easy tasks.

I stopped using Microsoft SQL Server because in short it hacked me off. Big time. I found out that MSSQL Express Edition wouldn't send mail (although, apparently this has changed in MSSQL SP2, from petitioning by web developers, as the most common use of small databases is in small web apps - and having them send mail automatically is a god-send!).

So I wrote a piece of cool code to send mail from MySQL (cause there's no native functionality), and then I thought that if I could write this extended functionality for MySQL, to make some better features for it, then why not use it as the platform for this super-Intranet that I wanted to build?
Only one hurdle - how to match Active Directory data with MySQL, without forking out a hefty license fee for MySQL Enterprise PLUS an add-on solution for LDAP connectivity (and AFAIK, Active Directory integration only steps in on MySQL Cluster Edition - so minimum of three servers required, which I definitely don't have!).

So, I came up with this theory as follows:

• Add a custom field to your Active Directory user, which specifies either a boolean value to state that the user has a matching username in a MySQL database schema, or a string value stating the username in a MySQL schema.
• When the user logs into your ASP web app, the code queries the database using LDAP looking for this custom field. If it finds it, it knows that there is (or should be) a matching user in the MySQL database. If this custom field is missing or null, then the user gets booted from your web app.
• When AD user logged in (status 200 if login successful), then we try and connect to the MySQL DB schema using the username specified by way of our LDAP custom field.

But what about first-time users? You can't set the MySQL password for a user to the password stored in their AD profile, as it's irretrievable.

True - so we test for two things:
1) Are they supposed to access the MySQL DB (LDAP custom field)?
2) Do they already have a MySQL login?

If the first one is false then we kick them as they're not meant to be there in the first place.
If the second one is false then we know they haven't logged in before - so we use a SQL statement to create a new user in the master table, with their AD password (which we get from the HTTP variable "AUTH_PASSWORD").

So what happens when a user changes their AD password?

Good question - I have yet to work this one out but I reckon it'd be a custom script that you can write into a Group Policy on an Active Directory network, which would then change the password of the associated MySQL username. Either that, or get the user to change their AD password within your web app - that way you can write the code to update both passwords at the same time.

Notes:

1. I have not practiced this yet! I would never recommend it in an Internet-facing web app unless you are using a minimum of 256-bit SSL certificate or it is used on a corporate Intranet.
2. I suggest creating a specialist desktop app for administering which users are allowed to access the MySQL app - preferably your network admin. Using VB and/or .NET you can query and edit AD data fairly easily.
3. If you're very fluent with MySQL permission base structure, then you can create "profiles" - similar to "users", "power users", "admins" etc. for your web app. This way you can achieve some level of delegation.
4. As I'm learning ASP.NET, I thought I would briefly point out - this solution is completely solved, using a mixture of two authentication system - Windows Auth and Forms Auth and mixing the variables between the two (I'm sure I'll do my next article on this soon).

Comments appreciated.

medicineworker