logo
Apache Lounge
Webmasters

 

About Forum Index Downloads Search Register Log in RSS X


Keep Server Online

If you find the Apache Lounge, the downloads and overall help useful, please express your satisfaction with a donation.

or

Bitcoin

A donation makes a contribution towards the costs, the time and effort that's going in this site and building.

Thank You! Steffen

Your donations will help to keep this site alive and well, and continuing building binaries. Apache Lounge is not sponsored.
Post new topic   Forum Index -> How-to's & Documentation & Tips View previous topic :: View next topic
Reply to topic   Topic: How-To Authentication through ODBC :: MySQL etc.
Author
Steffen
Moderator


Joined: 15 Oct 2005
Posts: 3049
Location: Hilversum, NL, EU

PostPosted: Sun 22 Jun '08 14:15    Post subject: How-To Authentication through ODBC :: MySQL etc. Reply with quote

With APR 1.3.2 the ODBC DBD driver is official included.

With the ODBC DBD driver you can authenticate through an ODBC driver for MySQL, Oracle, PostgreSQL, IBM-DB2, , Informix, Sybase, SQLServer, MS Access, Foxpro, etc. It enables access to ODBC datasources.

Note: Only for 2.2.9a and up

GENERAL SETUP FOR MYSQL 5.0 ON WINDOWS (mod_dbd)

1. You must have a working MySQL 5.0 database and you must install the MySQL ODBC driver (which MySQL calls "MySQL Connector/ODBC"). The MySQL ODBC driver can be downloaded here: http://dev.mysql.com/downloads/connector/odbc/3.51.html

2. In [Start] [Administrative Tools] [DataSources (ODBC)] go to the [System DSN] tab and add a new datasource for your database. If you run into trouble, the detailed docs are here: http://dev.mysql.com/doc/refman/5.0/en/myodbc-connector.html

3. Edit your httpd.conf file to enable DBD access. This example presumes you named your ODBC datasource "mySQLodbc" in step 2, but you can use any name for your datasource.
Code:
LoadModule dbd_module modules/mod_dbd.so
DBDriver odbc
DBDParams "DATASOURCE=mySQLodbc"
DBDKeep     10
DBDMax      10
DBDMin      10

See the mod_dbd docs at http://httpd.apache.org/docs/2.2/mod/mod_dbd.html and the ODBC driver docs at http://code.google.com/p/odbc-dbd/wiki/Windows for more details. You can skip the Compiling and Installing apr_dbd_odbc section in the ODBC driver docs - it's already done.

USING DBD FOR AUTHENTICATION (mod_authn_dbd)

1. Create a MySQL table with usernames and passwords. The column names can be anything you like (we use 'User_ID' and 'Password' in this example). The passwords can be either plain-text, SHA-encrypted starting with {SHA}) , or MD5-encrypted (starting with $apr1$). See the Apache docs for details if you do not want to store plain-text passwords in your database: http://httpd.apache.org/docs/2.2/misc/password_encryptions.html
Code:
mysql> CREATE TABLE Apache_Users (User_ID VARCHAR(64), Password VARCHAR(64), PRIMARY KEY (User_ID));

mysql> INSERT INTO Apache_Users (User_ID, Password) VALUES ('tom', 'mYsEcReTpAsSwOrD');

mysql> INSERT INTO Apache_Users (User_ID, Password) VALUES ('mary', '{SHA}rAu31qbrao6MnY6E968FtBy5kis=')


2. Edit httpd.conf to use DBD for user authentication via an SQL statement. Your SQL statement must contain exactly one %s where the actual username is to be substituted. Don't put the %s inside single-quotes, and don't put a semicolon at the end of your SQL statement
Code:
LoadModule authn_dbd_module modules/mod_authn_dbd.so
<Directory "C:/Apache2/htdocs/private">
AuthType basic
AuthName "private area"
AuthBasicProvider dbd
AuthDBDUserPWQuery "SELECT Password FROM Apache_Users WHERE User_ID = %s"
Require valid-user
</Directory>

See the Apache docs for mod_authn_dbd for details: http://httpd.apache.org/docs/2.2/mod/mod_authn_dbd.html

OTHER DATABASES

Using apr_dbd_odbc-1.dll with other databases is similar to using it with MySQL, but you will need to install the correct ODBC driver from your database vendor and you may need to adjust the SQL statements to be compatible with the syntax for your database.

The ODBC-DBD driver apr_dbd_odbc-1.dll works with the following database ODBC drivers on Windows:** DB2 "Express-C" includes the ODBC driver in "client components". This driver is compatible with the Apache Derby database.


Tom & Steffen
Back to top
suministros



Joined: 09 Jul 2008
Posts: 2
Location: Colombia

PostPosted: Wed 09 Jul '08 17:48    Post subject: How to show the autenthicated User Reply with quote

Thanks and Sorry for my english, i like to now how can i show in PHP the autenticated user.

I Using the login with Apache with the ODBC driver.

I follow your tutorial and all is Ok, but the content of the protected directory is based on the username login.

So i have no idea where the variable of the authenticated user is .

THANKS

Carlos Garcia
Back to top
James Blond
Moderator


Joined: 19 Jan 2006
Posts: 7288
Location: Germany, Next to Hamburg

PostPosted: Wed 09 Jul '08 17:55    Post subject: Reply with quote

There is a small tutorial about that on PHP.net[1]

Code:

<?php
if (!isset($_SERVER['PHP_AUTH_USER'])) {
    header('WWW-Authenticate: Basic realm="My Realm"');
    header('HTTP/1.0 401 Unauthorized');
    echo 'Text to send if user hits Cancel button';
    exit;
} else {
    echo "<p>Hello {$_SERVER['PHP_AUTH_USER']}.</p>";
    echo "<p>You entered {$_SERVER['PHP_AUTH_PW']} as your password.</p>";
}
?>


Notice: There is no logout until the browser is closed.



1 http://de.php.net/manual/en/features.http-auth.php
Back to top
suministros



Joined: 09 Jul 2008
Posts: 2
Location: Colombia

PostPosted: Wed 09 Jul '08 18:30    Post subject: THANKS Reply with quote

Thanks James thats a quick and GOOD answer..

many thanks

Carlos Garcia
Back to top
bbaumer



Joined: 09 Jan 2009
Posts: 2
Location: Flushing, NY

PostPosted: Fri 09 Jan '09 22:55    Post subject: Great Info, but still having trouble Reply with quote

First of all I apologize if this is not the appropriate place to ask for help. I've read this tutorial thoroughly, but I still can't quite get it to work. The error that I'm getting (in error.log) is:

Code:
[Fri Jan 09 14:47:44 2009] [dbd_odbc] SQLConnect returned SQL_ERROR (-1) at D:\asf-build\build-2.2.1:1057 [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified IM002
[Fri Jan 09 14:47:44 2009] [error] (20014)Internal error: DBD: Can't connect to odbc


My httpd.conf reads:
Code:

# mod_dbd configuration
DBDriver odbc
DBDParams "DATASOURCE=apache"

<Directory "C:/root/www">
  # core authentication and mod_auth_basic configuration
  # for mod_authn_dbd
  AuthType Basic
  AuthName "My Server"
  AuthBasicProvider dbd

  # core authorization configuration
  Require valid-user

  # mod_authn_dbd SQL query to authenticate a user
  AuthDBDUserPWQuery \
    "SELECT password FROM users WHERE username = %s"
</Directory>


I'm using Apache 2.2.11 on Windows with APR 1.3.4. It appears to me as though mod_dbd has found the ODBC driver (in \bin\apr_dbd_odbc-1.dll) and is trying to connect to the ODBC named "apache", which I have set up and tested on the localhost. It works, and I can even access it using other 3rd-party ODBC-aware applications like R.

So I'm fairly certain that the ODBC data source is set up properly, since I can use it with other applications. Does anyone have any ideas about what I'm doing wrong?

Thanks in advance!
Back to top
tdonovan
Moderator


Joined: 17 Dec 2005
Posts: 611
Location: Milford, MA, USA

PostPosted: Sat 10 Jan '09 16:21    Post subject: Reply with quote

Did you set up your "apache" ODBC datasource as a System DSN, so that Apache can find it?

A User DSN or File DSN is only available to you when you use an interactive program.
A System DSN is available to other processes - like Apache running as a Windows service.

-tom-
Back to top
bbaumer



Joined: 09 Jan 2009
Posts: 2
Location: Flushing, NY

PostPosted: Mon 12 Jan '09 22:27    Post subject: Reply with quote

$you = "Hero";
$me = "Idiot";

Thank you!
Back to top
sweeppicker



Joined: 28 Jul 2010
Posts: 2

PostPosted: Fri 30 Jul '10 4:31    Post subject: Reply with quote

if we don't set up apache ODBC datasource as a System DSN then is there is any chance that it would work?

thanks

dreams within dreams are too unstable ~pheromones


Last edited by sweeppicker on Sat 07 Aug '10 6:15; edited 1 time in total
Back to top
tdonovan
Moderator


Joined: 17 Dec 2005
Posts: 611
Location: Milford, MA, USA

PostPosted: Fri 30 Jul '10 21:25    Post subject: Reply with quote

Yes - instead of DATASOURCE, you can use CONNECT in the DBDParams directive.

An example for MySQL if you have Connector-ODBC 5.1.6 installed:
Code:
DBDParams "CONNECT='DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;DATABASE=test;'"

Note that you must use single-quotes for the connect-string, since it is already inside double-quotes.

Also, the driver name must be exact, and inside curly-braces. Look in [Programs] [Administrative Tools] [Data Sources (ODBC)] [Drivers] to get the exact name for your database.

The other arguments (like SERVER, DATABASE, etc.) depend on which database you are using.

Links to ODBC Connect-string documentation for some popular databases are:Hope this helps...

-tom-
Back to top


Reply to topic   Topic: How-To Authentication through ODBC :: MySQL etc. View previous topic :: View next topic
Post new topic   Forum Index -> How-to's & Documentation & Tips