472,348 Members | 1,198 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,348 developers and data experts.

MySQL with Integrated Windows Authentication

533 Expert 512MB
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.

  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.

Apr 29 '08 #1
0 13575

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Ravikanth[MVP] | last post by:
Hi It is possible that IIS and SQL Server can reside on Seperate Machines and you can use Integrated Windows Authentication to connect. ...
by: Tom B | last post by:
In my web.config file I've specified Windows for the authentication, in IIS I've set it to Integrated Authentication. But my SQL connection is...
by: Andrew | last post by:
Hey all, I would like to preface my question by stating I am still learning ASP.net and while I am confident in the basics and foundation,...
by: Patrick.O.Ige | last post by:
Hi folks, How can i pass credentials to windows integrated authentication. I want to use my credentials from windows authentication and pass it on...
by: cdlipfert | last post by:
Our intranet is running under windows integrated security. We have domain users that want to access our intranet site via ssl vpn. SSL VPN can...
by: Dariusz Tomon | last post by:
I got an ASPNET C# project from client. When I'm trying to open it in Visual Studio I get error message: "Unable to start debugging on the web...
by: =?Utf-8?B?RGFuZGFuIFpoYW5n?= | last post by:
Now I have a web application, a web service and a SQL Server database. The Web application will invoke the web service, the web service invokes...
by: zanzibar | last post by:
I need to develop an intranet app that uses Windows Authentication with IIS set up for Windows Integrated Authentication. The problem is that I...
by: benoitc | last post by:
I've been having a problem debugging an ASP.NET 1.1 application on an existing Windows XP/Visual Studio 2003 workstation that I've inherited from...
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.