You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@httpd.apache.org by Nicolas Michel <ni...@lemail.be> on 2010/12/22 15:38:01 UTC
[users@httpd] Authenticate users against a Microsoft SQL (odbc - freetds)
Hello,
I'm trying to setup an apache authentication using a user list (and
password) that resides in a Microsoft SQL Server 2000.
I read these docs :
http://www.freetds.org/userguide/odbcconnattr.htm
http://www.unixodbc.org/doc/FreeTDS.html
http://code.google.com/p/odbc-dbd/downloads/list
http://www.freetds.org/userguide/freetdsconf.htm
http://people.apache.org/~niq/dbd.html
Facts :
I have a Microsoft SQL Server 2000, installed and configured odbc on a
Debian Lenny (so my DB is declared in /etc/odbc.ini and driver
in /etc/odbcinst.ini)
I get a working connection to the MSSQL in the linux shell with
isql DSN user pass
and I'm able to make selects on my DB.
But I don't succeed to use it in apache. When specifying DBDriver odbc
in my apache config file, starting apache log an error (the driver odbc
is not found).
I also tried to compile apache following these instructions :
http://code.google.com/p/odbc-dbd/wiki/Linux
but it didn't work.
Can you help me? Giving me some tips or tutos?
Thank you very much,
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] Authenticate users against a Microsoft SQL (odbc
- freetds)
Posted by Nicolas Michel <ni...@lemail.be>.
On 12/23/2010 02:58 AM, Tom Donovan wrote:
> On 12/22/2010 9:38 AM, Nicolas Michel wrote:
>> Hello,
>>
>> I'm trying to setup an apache authentication using a user list (and
>> password) that resides in a Microsoft SQL Server 2000.
>>
>> I read these docs :
>> http://www.freetds.org/userguide/odbcconnattr.htm
>> http://www.unixodbc.org/doc/FreeTDS.html
>> http://code.google.com/p/odbc-dbd/downloads/list
>> http://www.freetds.org/userguide/freetdsconf.htm
>> http://people.apache.org/~niq/dbd.html
>>
>> Facts :
>> I have a Microsoft SQL Server 2000, installed and configured odbc on a
>> Debian Lenny (so my DB is declared in /etc/odbc.ini and driver
>> in /etc/odbcinst.ini)
>> I get a working connection to the MSSQL in the linux shell with
>> isql DSN user pass
>> and I'm able to make selects on my DB.
>>
>> But I don't succeed to use it in apache. When specifying DBDriver odbc
>> in my apache config file, starting apache log an error (the driver odbc
>> is not found).
>>
>> I also tried to compile apache following these instructions :
>> http://code.google.com/p/odbc-dbd/wiki/Linux
>> but it didn't work.
>>
>> Can you help me? Giving me some tips or tutos?
>>
>> Thank you very much,
>
> The Debian-5 (Lenny) apache2 package is Apache-2.2.9 from back in 2008,
> which was before Apache
> included the ODBC driver.
>
> Your problem seems to be that the libaprutil1 package (Apache Portable
> Runtime Utilities) supplied
> by Debian-5 only contains built-in DBD drivers for PostgreSQL, SQLite3,
> and MySQL. It cannot load
> DBD drivers dynamically from an .so file, so it can't load the ODBC driver.
>
> You can rebuild libaprutil1 from the Debian source code to fix this. You
> must add a definition
> (-DAPR_DSO_BUILD=APR_HAS_DSO) which allows .so files to be loaded; then
> install the new .deb files
> to update libaprutil1 on your system. Make sure Apache is stopped when
> you do this and that your system is backed-up! You will need to watch
> for updates to the Debian-5 libaprutil1 package, and repeat these steps
> whenever libaprutil1 is updated.
>
> The steps are:
>
> sudo apt-get build-deps libaprutil1
> CFLAGS=-DAPR_DSO_BUILD=APR_HAS_DSO apt-get -b source libaprutil1
> sudo dpkg --install libaprutil1*.deb
>
> Second, since Apache didn't contain an ODBC/DBD driver in version 2.2.9
> - download odbc-dbd from GoogleCode and build it with these steps:
>
> wget http://odbc-dbd.googlecode.com/files/odbc-dbd-1.0.10.tar.gz
> tar -xzf odbc-dbd-1.0.10.tar.gz
> cd odbc-dbd-1.0.10
> ./configure --with-apr=/usr
> make
> sudo make install
>
> Next, enable DBD and DBD authentication in Apache:
>
> sudo a2enmod dbd
> sudo a2enmod authn_dbd
>
> Create a file: /etc/apache2/conf.d/authentication (as root) which
> configures the DBD driver and specifies an SQL query to authenticate
> users for certain directories. You will need to change this example to
> match your own datasource, the tables in your database, and the
> directories that you want to protect:
>
> <IfModule dbd_module>
> DBDriver "odbc"
> DBDParams "DATASOURCE=myDSN,USER=myDSNUsername,PASSWORD=myDSNPassword"
> </IfModule>
>
> <IfModule authn_dbd_module>
> <Directory /var/www/secret>
> AuthType Basic
> AuthName "Restricted Files"
> AuthBasicProvider dbd
> Require valid-user
> AuthDBDUserPWQuery "SELECT myPasswordField from myUsersTable WHERE
> myUsernameField = ?"
> </Directory>
> </IfModule>
>
> Now - restart Apache:
>
> /etc/init.d/apache2 start
>
> Note that you don't store clear text passwords in the database; you must
> store encrypted passwords. For example: if user 'fred' has password
> 'flintstone' - your SQL query should not return
> 'flintstone'. It should return an encrypted string, like:
> '{SHA}6WLN5wU+7RIPkozRjljr0xvndUM='.
>
> There is info about how to create these encrypted password strings at:
> http://httpd.apache.org/docs/2.2/misc/password_encryptions.html
>
> Also note that the SQL statement uses the standard ? for the username
> parameter. Later versions of APR changed this - so you may need to
> change your SQL to use %s instead of ? when your system is updated to a
> later APR version.
>
> I hope this helps,
> -tom-
>
>
> ---------------------------------------------------------------------
> The official User-To-User support forum of the Apache HTTP Server Project.
> See <URL:http://httpd.apache.org/userslist.html> for more info.
> To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
> " from the digest: users-digest-unsubscribe@httpd.apache.org
> For additional commands, e-mail: users-help@httpd.apache.org
>
Thank you very very much! I'll test it and will give you a feedback ;)
Best regards,
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] Authenticate users against a Microsoft SQL (odbc
- freetds)
Posted by Tom Donovan <do...@bellatlantic.net>.
On 12/22/2010 9:38 AM, Nicolas Michel wrote:
> Hello,
>
> I'm trying to setup an apache authentication using a user list (and
> password) that resides in a Microsoft SQL Server 2000.
>
> I read these docs :
> http://www.freetds.org/userguide/odbcconnattr.htm
> http://www.unixodbc.org/doc/FreeTDS.html
> http://code.google.com/p/odbc-dbd/downloads/list
> http://www.freetds.org/userguide/freetdsconf.htm
> http://people.apache.org/~niq/dbd.html
>
> Facts :
> I have a Microsoft SQL Server 2000, installed and configured odbc on a
> Debian Lenny (so my DB is declared in /etc/odbc.ini and driver
> in /etc/odbcinst.ini)
> I get a working connection to the MSSQL in the linux shell with
> isql DSN user pass
> and I'm able to make selects on my DB.
>
> But I don't succeed to use it in apache. When specifying DBDriver odbc
> in my apache config file, starting apache log an error (the driver odbc
> is not found).
>
> I also tried to compile apache following these instructions :
> http://code.google.com/p/odbc-dbd/wiki/Linux
> but it didn't work.
>
> Can you help me? Giving me some tips or tutos?
>
> Thank you very much,
The Debian-5 (Lenny) apache2 package is Apache-2.2.9 from back in 2008, which was before Apache
included the ODBC driver.
Your problem seems to be that the libaprutil1 package (Apache Portable Runtime Utilities) supplied
by Debian-5 only contains built-in DBD drivers for PostgreSQL, SQLite3, and MySQL. It cannot load
DBD drivers dynamically from an .so file, so it can't load the ODBC driver.
You can rebuild libaprutil1 from the Debian source code to fix this. You must add a definition
(-DAPR_DSO_BUILD=APR_HAS_DSO) which allows .so files to be loaded; then install the new .deb files
to update libaprutil1 on your system. Make sure Apache is stopped when you do this and that your
system is backed-up! You will need to watch for updates to the Debian-5 libaprutil1 package, and
repeat these steps whenever libaprutil1 is updated.
The steps are:
sudo apt-get build-deps libaprutil1
CFLAGS=-DAPR_DSO_BUILD=APR_HAS_DSO apt-get -b source libaprutil1
sudo dpkg --install libaprutil1*.deb
Second, since Apache didn't contain an ODBC/DBD driver in version 2.2.9 - download odbc-dbd from
GoogleCode and build it with these steps:
wget http://odbc-dbd.googlecode.com/files/odbc-dbd-1.0.10.tar.gz
tar -xzf odbc-dbd-1.0.10.tar.gz
cd odbc-dbd-1.0.10
./configure --with-apr=/usr
make
sudo make install
Next, enable DBD and DBD authentication in Apache:
sudo a2enmod dbd
sudo a2enmod authn_dbd
Create a file: /etc/apache2/conf.d/authentication (as root) which configures the DBD driver and
specifies an SQL query to authenticate users for certain directories. You will need to change this
example to match your own datasource, the tables in your database, and the directories that you want
to protect:
<IfModule dbd_module>
DBDriver "odbc"
DBDParams "DATASOURCE=myDSN,USER=myDSNUsername,PASSWORD=myDSNPassword"
</IfModule>
<IfModule authn_dbd_module>
<Directory /var/www/secret>
AuthType Basic
AuthName "Restricted Files"
AuthBasicProvider dbd
Require valid-user
AuthDBDUserPWQuery "SELECT myPasswordField from myUsersTable WHERE myUsernameField = ?"
</Directory>
</IfModule>
Now - restart Apache:
/etc/init.d/apache2 start
Note that you don't store clear text passwords in the database; you must store encrypted passwords.
For example: if user 'fred' has password 'flintstone' - your SQL query should not return
'flintstone'. It should return an encrypted string, like: '{SHA}6WLN5wU+7RIPkozRjljr0xvndUM='.
There is info about how to create these encrypted password strings at:
http://httpd.apache.org/docs/2.2/misc/password_encryptions.html
Also note that the SQL statement uses the standard ? for the username parameter. Later versions of
APR changed this - so you may need to change your SQL to use %s instead of ? when your system is
updated to a later APR version.
I hope this helps,
-tom-
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org