MySQL Enterprise 5.5 (trial version available here) includes MySQL PAM authentication plugin. In this post I will show how to configure it with the OpenLDAP and Active Directory.
MySQL PAM authentication uses Linux pam_ldap library to send the calls. To configure MySQL LDAP authentication we will need to configure pam_ldap on linux.
OpenLDAP Linux configuration
- Make sure that libpam-ldap/openldap is installed. If not, on RedHat/CentOS use commands:
# yum install openldap openldap-clients
- Configure /etc/ldap.conf. Sample configuration:
debug 10 # set debug level only during the initial configuration
base dc=corp,dc=company_name,dc=com
binddn cn=service_account,OU=Service Accounts,OU=US Security,DC=corp,DC=company_name,DC=com
bindpw <password>
timelimit 120
idle_timelimit 3600
uri ldaps://<LDAP URL>:<LDAP PORT>
# The following lines are needed only for Active Directory
pam_login_attribute samaccountname
pam_member_attribute member
nss_map_objectclass posixAccount user
nss_map_objectclass shadowAccount user
nss_map_attribute uid sAMAccountName
nss_map_attribute homeDirectory unixHomeDirectory
nss_map_attribute shadowLastChange pwdLastSet
nss_map_objectclass posixGroup group
nss_map_attribute uniqueMember member
pam_login_attribute sAMAccountName
pam_filter objectclass=User
pam_password ad
Make sure you can connect to ldap
# telnet <ldap server> <ldap password> (should show “connected”)
Make sure you can search ldap with ldapsearch
# ldapsearch –w <password for bind user> -x –D 'cn=USER,OU=People …' “(&(ObjectClass=user)(cn=USERNAME))”
MySQL configuration
To setup mysql follow the instructions from http://dev.mysql.com/doc/refman/5.5/en/pam-authentication-plugin.html
- Make sure you use MySQL 5.5.16+ enterprise version
- Add line:
plugin-load=authentication_pam.so
into my.cnf and restart mysql server
- Put the following in /etc/pam.d/mysql:
#%PAM-1.0
auth required pam_ldap.so
account required pam_ldap.so
Individual User Authentication
- Create user for group substitution
CREATE USER 'user_name'@'localhost'
IDENTIFIED WITH authentication_pam
AS 'mysql';
Where mysql = name of the /etc/pam.d/mysql file above, user_name should match the LDAP’s uid (samaccountname for AD).
- Login to mysql with your LDAP username and password
mysql --user=user_name --password=verysecret mydb
Group Authentication
- Create user for group substitution
CREATE USER ''@''
IDENTIFIED WITH authentication_pam
AS 'mysql, ldap_group1=mysql_user1, ldap_group2=mysql_user2';
Where mysql = name of the /etc/pam.d/mysql file above, ldap_group1 is the ldap group, mysql_user1 is the user to substitute (we need to create this user)
- Create user for each LDAP group
CREATE USER 'mysql_user1'@'localhost' IDENTIFIED BY 'very secret password';
GRANT ALL PRIVILEGES ON mydevdb.* TO 'mysql_user1'@'localhost';
GRANT PROXY ON 'mysql_user1'@'localhost' TO ''@'';
- Login to mysql with your LDAP username and passwordand make sure the user was substituted correctly:
mysql --user=user_name --password=verysecret mydb
mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
+-------------------+-----------------------+--------------+
| USER() | CURRENT_USER() | @@proxy_user |
+-------------------+-----------------------+--------------+
| user_name@localhost | mysql_user1@localhost | ''@'%' |
+-------------------+-----------------------+--------------+
Debugging
- Add “debug 16” (or other level, 0 to 256) into /etc/ldap.conf
- Add “export AUTHENTICATION_PAM_LOG=1” to /etc/init.d/mysql and restart mysql
- Then for troubleshoot look into mysql error log (i.e. /var/log/mysqld.log).
Current issues
Due to some PAM library’s limitations the group authentication may not work with Active Directory (individual authentication works fine), probably because the “posix_group” is not fully supported by Active Directory.
Links and more info
-
MySQL PAM Module documentation
Download MySQL Enterprise Trial from Oracle E-delivery
We plan a session on MySQL LDAP Auth at MySQL Connect (Sept 29-30, San Francisco)
You might want to note that passwords are sent in the clear over the wire when external authentication (like pam) is used.
You should be able to use SSL to avoid that.
Note that in order for the group mapping to work you’ll need the UNIX apis getgrnam(3)/getpwnam(3) to work against your LDAP server.
And this means changing more PAM configuration profiles than just the /etc/pam.d/mysql file you can use for 1-to-1 authentication.
I need to extract data from 2 MySQL databases located at two different servers. How is it possible from one single PHP page?
I use the iPhone Configuration Utility available on the Apple web site to build an iPhone user prloife with the Pulse client preconfigured and install/apply a user digital certificate. Easy to do under VPN and the Credentials sections. Then either install the prloife directly on the phone or export then email it.I too have found that you cannot seem to use a digital certificate *and* password authentication like Joel mentioned. With a standard web browser on a computer (not iPhone) one can enter their login/password and then select their certificate, but the iPhone Pulse client seems to be one or the other, so I had to create a new Realm with more restrictions. Not something I want to roll out for production yet.
I just posted this qusieton on the JNet community forum. If this cold be answered, I would greatly appreciate it.Hello,I’m new to all of this VPN stuff, so please bare with me.I would like to let our iPad users connect to our network via Junos Pulse, be we have a policy that states:1) Only our computers can connect2) the suer needs to authenticate with a password to conenctSo, I see how we can use a certificate or a password, but that doesn’t help. For Mac/Linux/Windows we use host checker to looks for various stuff that only our computers have. Is there a way we can do anything to validate the iPad and then have the user submit a U/P to connect?Thanks,Allan
Got it! Thanks a lot again for helpnig me out!
Keith, I’ve successfully used IPCU to inatsll a skeleton Pulse-only configuration profile, in both cases when the app was already inatslled. In one case, the app had been enabled and manually configured already, in the other case, the app had been freshly inatslled from the app store but not enabled. Both cases behaved as expected with no issues. That’s probably not entirely helpful as it doesn’t move you closer to a solution, but at least you know how it ought to work.
We have it working with MS certs tniestg at this stage. They are very difficult to manage automatically. It works on Ipad 4.2 (beta) without a problem for us. Iphone works without a problem. Yes, you can put certs on the phone. But if you have 600+ phones already in the wild it is difficult to get the cert out to them. Juniper SA cannot read the SCEP / NDES machine cert on the phone. Can only read personal certs. ( MS Server 2008 R2 set up ) at this stage, I am told.
Using the latest IPCU on Snow Leopard: if I try inlislatng a profile that contains nothing but VPN settings *after* Junos Pulse is already installed, the profile installer on the phone itslef hangs indefinitely. If the profile is installed first, and then the Pulse app, all is well (and the VPN configurations show up properly in pulse as they should). Hmmm Anyone else using the IPCU having this issue? Thanks.
Thanks Kevin, I was able to get Junos Pulse working on the iPad last week. I cheekcd the App Store again today and it still looks like the only version of Pulse available on the iPad is the iPhone version (the iPad runs iPhone apps, just at a different resolution) but this version is working just fine.Now time to see if I can get it working on my 6.5 system with RSA integration.
Allan,Your best option at this point is going to use ciefeticatrs for the VPN connection, whith authentication to the backend resources. We are looking into the best way to handle both at the same time, but as of right now I don’t have anything that can be shared. In the meantime, looking into supporting ciefeticatrs on your devices would probably be time well spent.
A white paper like that would help. I am sure there are many interesting scanerios where this can make account management much simpler. Especially when you want to immediately remove access for a user who might be crafty enough to restore a database backup. And if the MySQL privilege tables are used, then the stale backup has stale privs.
I’ve noticed that since using a Cert on the clneit for the initial SSL (RADIUS two factor for secondary auth) the pulse clneit needs a manual kick to bring the VPN back to life ? It doesn’t need to fully re-auth, but I have to het connect again within Pulse.Annoying when flipping between 3G and Wi/Fi and back.
Jing,Good point. I’m not creating new VMs as much as I’m mainkg copies of existing VMs, tweaking them (ie: installing OpenLDAP), and then zipping them up for safe keeping. In a sense I’m creating a new one, but I’m not creating it in the sense that VMware means. That is, I can’t install a brand new OS, such as Windows XP, but I can certainly mutate existing snapshots.
VMWare rocks!Last weekend, my bhertor finally showed me VMWare. I had heard about it some time ago, but never had the time to actually try this out. After downloading evaluated one of the pre-built virtual machines, using the VMPlayer I decided to download the Wor
I am using Visual Studio 2010 and MVC2. I created a MySql dsaabtae with a sql script in MySql Workbench. I have Connector/Net 6.3.3 beta 2. I added a new item to create an ADO.Net Entity Model from the for the MySql dsaabtae. The resulting model had lower case names for all of the tables in the diagram. I looked an saw that this probably comes from the fact that the Table names in the MySql dsaabtae are also lower case. Note from my previous comments I am generating with the MySql tt file from Visual studio all use upper case names also. Somewhere in the process, the table names which begin with an upper case first letter are going to all lower case names. This is more a pain in the neck than anything else since I have to modify the names in the edmx diagram in oder for compatability with existing code. However, I am reporting this as a problem because it might make it hard for some people to migrate from Sql server to MySql. I’d think that is one of your goals.
Very helpful, didn’t want to cnonitue using my own admin account in our sh backup script anymore, but for anyone else interested, you should also give a user the SHOW DATABASES perm so the user can do all at once.
Keith Hope this helps but I’ve got 4.2 installed and was hanivg the same problem as you with the Failed to start the VPN service message. I rolled back to 4.1 and restored from a backup and had the same.What I’ve discovered though is that if you delete the app and reinstall it, it works on both 4.1 and 4.2 however.
I have been using Roboform for a year now. I love the program, and don’t know what I would do wtuhoit it. I would recommend it. I made a quick video about the program and posted it on squidoo
Finding this post solves a porblem for me. Thanks!
First off, Leopard’s autofs DOES work with LDAP. Second, we are also lokniog at a little bit of hardcoded application from Apple, ergo not very flexible. Third, the integration of Mac OS X into LDAP is not covered in this particular post, as it was quite heavily covered in this comprehensive article titled Integrating Mac OS X into Unix LDAP Environment with NFS Home Directories .
when i wanne create the table, i get the fwnoolilg error:Warning: mysql_query() [function.mysql-query]: Access denied for user nobody’@’localhost’ (using password: NO) in /home/a4690156/public_html/Scripts/create_Table.php on line 34PHP Error MessageWarning: mysql_query() [function.mysql-query]: A link to the server could not be established in /home/a4690156/public_html/Scripts/create_Table.php on line 34no TABLE created. You have problems in the system already, backtrack and debug!
about integrating Apple into your etnxiisg Unix/NFS environment, please read the article Integrating Mac OS X into Unix LDAP Environment with NFS Home Directories. Rant: Even with Leopard, there is no support for Microsoft DFS.
Ludo,I hadn’t gotten that far yet :). Currently, since this for tetnsig. My initial idea was to use setuid root. After you posted I thought I’d poke around, but I can’t figure out what I’d need to actually make setuid root since making the OpenDS.jar or the start-ds script won’t really work.If someone has an idea, I’d like to hear it.So after some hacking around with the other admins here, we have decided to try some iptables rules.We made the Directory server owned by a regular user, and let it attach to an unprivileged port, and then tossed this into iptables:iptables -t nat -A PREROUTING -p tcp –dport 389 -j REDIRECT –to-port 1389iptables -t nat -A OUTPUT -p tcp –dport 389 -j REDIRECT –to-ports 1389I am not sure how much I like that, but it’s a fix for now.
21ayour the man dude these tutorials are epic. i have one liltte problem with this and my products page though when i try to create a dynamic table with 2 columns so i can display more products it just duplicates the products so ile have two black tops next to each other and 2 black hats next to each other etc etc etc i have tryed editing the php code to like ID++ or +1 to no prevail and also as tanib10 said how can i implement a next button?? please help or anyone who has got this workingef
If you set the port for OpenDS to 389, it means that you must run OpenDS as root. Or your regular user must have the plegirevis to open ports under 1024, which requires afaik specific configuration on Linux. May be you could explain how you do configure the regular user ?RegardsLudo