Cheap VPS & Xen Server


Residential Proxy Network - Hourly & Monthly Packages

How to make apache2 authenticate against MS SQL 2000 Server


1. Intro

In a recent project I needed to make apache2 authenticate against MS SQL 200 Server.
I did some hacks to make it happen. So to keep history log of what was done this document was made.

2. Situation

1. MS SQL database server running on Windows 2003 Server
2. User records (logins and passwords) are kept in MS SQL database (1)
3. SuSe Linux Enterprise server 9 with apache2. Some directories on that server need to have limited access

3. Target

Avoid many passwords. Make apache2 authenticate against MS SQL. If user has the rights to access particular DB – she has right to use the restricted area on our SuSe Linux apache2 web server.

4. Solution design ideas

1. have some program/script to run on Linux machine. Take authentication (username/password) data from user (login form or standard apache authentication window like the one that is used with .htaccess authentication)

2. Pass these credentials to that program/script. So that script tries to make the connection to our MS SQL database. If connection is successful – program/script returns ok message and tells apache that user is allowed to access restricted area.

5. Tools and actual implementation

Looking around for sollution it turned out that connecting from Linux to MS SQL is not that straightforward. Ruby, perl, python – all have nice DBI (database interface modules) but when it comes to MS – it’s getting painful.
Please correct me if i’m wrong.

I was aware that on the same Linux machine runs jboss application that is web interface to the same MS SQL database. So looking into direction of java seamed most appropriate solution.

5.1. connecting from Java to MS SQL

It took not that much gogling to find some samples and glue together the java ‘program’ that initially looked like that:

import java.sql.*;
import java.io.*;
public class testConnection
{
    public static void main(String[] args)
    {
      String userName;
      String userPass;
      userName = args[0];
      userPass = args[1];
    DB db = new DB();
        db.dbConnect(
     "jdbc:jtds:sqlserver://xxx.xxx.xxx.xxx:1433/test",userName,userPass);
    }
}
class DB
{
    public DB() {}
    public void dbConnect(String db_connect_string,
  String db_userid, String db_password)
    {
        try
        {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            Connection conn = DriverManager.getConnection(
    db_connect_string, db_userid, db_password);
            System.out.println("connected");
        }
        catch (Exception e)
        {
            //e.printStackTrace();
            System.out.println("failed");
        }
    }
};

I’ve had already Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2) on that machine.
So only non out-of-the-box thing was net.sourceforge.jtds.jdbc.Driver part.
This is the driver that needs to be ‘installed’ to access MS SQL form java.
This project is located at http://jtds.sourceforge.net/ . I installed it like that:

wget http://belnet.dl.sourceforge.net/sourceforge/jtds/jtds-1.2-dist.zip
unzip jtds-1.2-dist.zip
cp jtds-1.2.jar /usr/lib/java/jre/lib/ext/

So now it’s possible to compile the program:

javac testConnection.java

and run it

java testConnection myUsername myPass

After that I looked out what can be done from apache’s side.

5.2. Apache part

After some googling again I found that there is an apache module, that makes it authenticate against almost everything. Modules name is mod_auth_external and project is located at http://www.unixpapa.com/mod_auth_external.html

Special interest for me was that “The external authentication program can be a shell script or perl program” (or as I strongly suspected – java program as in my case).
I’ve got the version that was suitable for our apache and unpacked it:

wget http://www.unixpapa.com/software/mod_auth_external-2.2.11.tar.gz
tar –xvzf mod_auth_external-2.2.11.tar.gz

README and INSTALL files from that package revealed pretty much all I needed to set it up.
Suse didn’t have apxs that was needed to compile and install the module – so I installed apxs2 which is in apache2-devel package from iso that has been mounted under /mnt/iso4.

rpm -ivh /mnt/iso4/suse/x86_64/apache2-devel-2.0.49-27.8.x86_64.rpm

Further following instructions in INSTALL file of mod_auth_external I compiled and installed mod_auth_external:

apxs2 -c mod_auth_external.c
apxs2 -i -a mod_auth_external.la

apxs should do some configuration but it was already warning in INSTALL file that it does not work in some cases.
So I manually added the line to /etc/apache2/sysconfig.d/loadmodule.conf line:

LoadModule auth_external_module /usr/lib64/apache2-prefork/mod_auth_external.so

And linked the mod_auth_external.so from /usr/lib64/apache2/mod_auth_external.so to /usr/lib64/apache2-prefork/mod_auth_external.so where our apache takes all its modules from.

Last thing that had to be done to configure apache – set directives in httpd.conf and <directory> as it was described in INSTALL.

But before that – I realized that there are incompatibilities in my testConnection.java. mod_auth_external can pass parameters to scripts as ENV variables, via pipe, checkpassword or hardcodedfunction (sybase, radius). I had to pass password and username to testConnection as command line arguments in first version – so easiest seemed to rewrite it to take username and password from stdin (pipe).

Another change I made was because The sample perl scripts if authentication was unsuccessful terminated with

exit 0

and if successful with

exit 1

I made analog changes in my java program.

5.3. The new testConnection.java

import java.sql.*;
import java.io.*;
import java.text.*;
public class testConnection
{
      static InputStreamReader converter = new InputStreamReader (System.in);
      static BufferedReader      in = new BufferedReader (converter);
      // Read a String from standard system input
      public static String getString() {
        try {
            return in.readLine();
        } catch( Exception e ) {
            System.out.println("getString() exception, returning empty string");
            return "";
        }
      }
    public static void main(String[] args)
    {
      String userName;
      String userPass;
      // userName = args[0];
      userName = getString();
      // userPass = args[1];
      userPass = getString();
    DB db = new DB();
        db.dbConnect(
     "jdbc:jtds:sqlserver://xxx.xxx.xxx.xxx:1433/test",userName,userPass);
    }
}
class DB
{
    public DB() {}
    public void dbConnect(String db_connect_string,
  String db_userid, String db_password)
    {
        try
        {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            Connection conn = DriverManager.getConnection(
    db_connect_string, db_userid, db_password);
            System.out.println("connected");
          System.exit(0);
        }
        catch (Exception e)
        {
            //e.printStackTrace();
            System.out.println("failed");
          System.exit(1);
        }
    }
};

So after the ‘program’ was written I had to compile it and put in some place for apache. So there comes javac again

javac testConnection.java

and we pack it with jar

jar cfm tc.jar manifest DB.class testConnection.class

manifest file should tell the name of main class like that:

Main-Class: testConnection

I copied the tc.jar to /usr/local/testConn/
5.4. Final apache configuration

And after that configured apache to execute it as external authentication program by editing /etc/apache2/default-server.conf and adding following lines:

AddExternalAuth archive_auth “/usr/lib/java/bin/java -jar /usr/local/testConn/tc.jar”
SetExternalAuthMethod archive_auth pipe

Final thing – tell to use particular authentication method for our ‘restricted’ directory in httpd.conf

<Directory /srv/www/htdocs/secretarea>
Options Indexes FollowSymLinks
AllowOverride None
AuthType Basic
AuthName “Autorizacija!”
AuthExternal archive_auth
Require valid-user
</Directory>

After restart of apache if we try to access http://xxx.xxx.xxx.xxx/secretarea – apache prompts for username and password.
User supplies these and apache executes tc.jar and passes user credentials to it. tc.jar talks to MS SQL. MS SQL answers and depending on answer – apache denies or allows proceeding to secured site.

 

Comments

comments