Integrating Xlight FTP Server with database through ODBC

Xlight FTP Server can be integrated with database system to authenticate users and store user settings in the database. This function will save much maintenance cost and effort to manage large number of users.

In this example, we use MySQL database to demonstrate the procedure of integrating Xlight FTP Server with database through ODBC. For other databases, setup procedures are very similar. In theory, Xlight FTP Server should work with any type of database supporting ODBC. We didn't test each of them, but we know it can work for MS-SQL, MYSQL, ORCALE, ACCESS database. Note: after 30-day evaluation period, this function is only supported by the Professional edition of Xlight FTP Server.

To use database through ODBC, you need to configure ODBC data source first.

1. Download MySQL ODBC data source driver from http://www.mysql.com/downloads/api-myodbc.html and install MySQL Data Source Driver in the machine running Xlight FTP Server.

2. Go to "Control Panel->Administrative tools->Data Sources (ODBC)"; select the "System DSN" tab.

3. Click "Add..." button, select "MySQL ODBC 3.51 Driver" and click the "Finish" button.

4. In this example, we use "Xlight FTP Server" as the name of data source. You can use any name for the data source. The MySQL database is running in the remote Linux machine "192.168.11.2". We will create a database "test" in this machine; you can use any database name for ftp server. In the machine of 192.168.11.2, at the MySQL prompt mysql>, type command "create database ftpd_user_db;" to create a database named "ftpd_user_db"

5. You need to configuration external database in Xlight FTP Server from [Global Options]->[Advanced]->[ODBC database configuration]. You have to fill in the "User" and "Password" box with a database user. If you want to create database table manually, you can go to step 7. In this example, we assume database user "test" has full access right to the "ftpd_user_db" database. You can click "Create" button to let Xlight FTP Server create database tables. Or you can click "Test" button to test if you have correct database tables.
Note: For 64bit system, you need to configrue DSN using the 32 bit ODBC Administrator. It can be found in C:\Windows\sysWOW64\odbcad32.exe. Check http://support.microsoft.com/kb/942976 for detail.

6. To use database for external user authentication, you need to goto [virtual Server Configuration]->[General]->[Virtual Server], select the option "Enable external user authentication" . Click the "Setup..." button, ODBC must be selected in the "Authentication Type" of virtual server configuration as showed in the figure below:

7. This step is used only for creating database tables manually. If you already created database tables in the step 5, you can skip this step. To create database tables manually, you need to create three tables in the database: acct_table, acct_param_table and virtual_path_table. You can find the file "odbc_tables.sql" in the odbc directory at the place where Xlight FTP Server is installed. You can use this file to create database tables manually.

8. To secure database access, after creating these three tables, you can create a read only user for accessing database from the machine running Xlight FTP Server. This user can have only SELECT right to database, as the following:

At MySQL prompt mysql>, type command grant select on ftpd_user_db.* anon@'192.168.11.%' Identified by "ftpd"; Here you created a new database user "anon" with password "ftpd", who has only SELECT access right to the database "ftpd_user_db". This command also restricts the connection for querying database can be made only from "192.168.11.0" network. Then you can change the "User" and "Password" in the MySQL ODBC Driver and Xlight FTP Server ODBC settings to this read only user.

Now you have finished the ODBC setup procedure. The ODBC function is enabled on the base of virtual server. In the same Xlight FTP Server, you can have some virtual servers to use ODBC database, and some don't. If you choose option "Skip host_id column" or "Skip host_port column", during database queries, Xlight FTP Server will skip "host_id" or "host_port" column. Because "host_id" column could be used to identify different machines, and "host_port" column could be used to identify different virtual servers in the same machine. These two options can be used to share the same user database between different machines running Xlight FTP Server or different virtual servers in the same machine. If you use database only for authenticate, you can choose the option "Only check username and password", which you have to create the same user in the local ftp server. The database is used only to verify user's password stored inside database. When this option is turned on, all other settings will get from settings of this user in the local ftp server.

Create a ftp user in database

You can use external database tools to connect to your database and create a test user. This is for test purpose.

In real environment, you may use PHP or other CGI script to create a web interface for adding, deleting users or allowing user to change his password in the database. You may also integrate it with your existing database applications. Since Xlight FTP Server only reads user database and don't write to user database, you won't need to worry about writing conflicts with your applications.

Before creating a ftp user in the database, you should better read the section "ODBC Database Tables Structure" in the Xlight's help document. After reading, you can have better understanding of each column in database tables.

User's password can be stored in MD5/SHA1 hash or in clear text in the database. MD5 hash is the default settings. You can change it in the external user authentication and ODBC settings of each virtual server. If you use MD5 hash, you can easily get a MD5 hash calculation tool by searching "MD5 calculator" in the Google. In our example below, the username is "test" and his password is "test" also, which the MD5 hash is "098f6bcd4621d373cade4e832627b4f6". It is showed in below:

Note: If a user's home_path doesn't exist, when he logins for the first time, his home path will be created by Xlight FTP Server. For example, if the home_path "c:\wutemp" for above "test" user doesn't exist. When "test" user logins, Xlight FTP Server will create it automatically. The implicit virtual path for home path is "/".

If you want to set more parameters, you can create a "param_index" row in the "acct_param_table". Fill this row with parameter settings you want, and put the index number into "param_index" column of the "acct_table" for this user. The "param_index" value can be shared by different users, so if you have many users with the same parameter settings you don't need to create many separate param_index rows in the "acct_param_table".

For those database table columns you don't use, you can leave it blank(empty). But "host_id", "host_port", "username" columns are required to have value, you must fill them. If using "home_path" column, "home_perm" column is required too.

"virtual_path_table" is used to set virtual paths. A user can have multiple virtual paths in the database. These virtual paths can have different or the same "virtual_path_index" in the "virtual_path_table". The "virtual_path_indexes" column in the "acct_table" can link to one or multiple rows in the "virtual_path_table" by using "virtual_path_index" column value from "virtual_path_table". If it contains multiple "virtual_path_index", they must be separated by ","(comma).

Troubleshooting database authentication problem

You can troubleshoot database authentication problem by selecting the option "Show debug trace information in the Error Log" from [Virtual Server Confiuration]->[Enable external user authentication]. After enable this option, you can see database query process and SQL query statements used by Xlight FTP Server.

Use other databases

When a user logins to FTP server, Xlight FTP Server will send two SQL queries to the database through ODBC connection to verify his password and get his settings. The username and password is used by the first SQL query to find a match record in the database, and get the basic parameters of this user, such as "home_path", "home_perm" etc. The "param_index" value returned by the first SQL query will also be used by the second SQL query. The second SQL query will use the "param_index" value returned from the first SQL query to query the database table "acct_param_table" to get more settings of this user.

It is possible that "acct_table" and "acct_param_table" used by Xlight FTP Server do not exist in the database. Xlight FTP server doesn't know if "acct_table" or "acct_param_table" really exists in the database. It only looks the returned result sets of both SQL queries. If the first SQL query replacement statement returns a row from database, Xlight FTP will think the authentication is successful. It will use values from the returned row as basic parameters of this user. If you want to deny this user from accessing FTP server, you should let database not return any row to the SQL query, which indicates that this user failed authentication. The "param_index" value returned from the first SQL query can be used by the second SQL query replacement statement.

Xlight FTP Server allows system administrator to replace these two SQL query statements to support other database and advanced applications, such as "stored procedure" in the database. You can look at the example for how to use stored procedure.

Statement to replace the first SQL query - The following parameters can be used in the first SQL query replacement:

$USER - This variable will be replaced with username of the ftp user
$PASS - This variable will be replaced with password(MD5 or Text) of the ftp user
$ID - This variable will be replaced with "Local Host ID" value of the machine
$PORT - This variable will be replaced with virtual server port
$USERIP - This variable will be replaced with the IP address of the ftp user
$SERVERIP - This variable will be replaced with the IP address of virtual server where the ftp user is connected to

For example, if you use "EXEC proc_getuser '$USER', '$PASS', $ID, $PORT" for the first SQL query replacement, when user "test" logins with the password "hello" to ftp server port 21 and host_id 1, after replacement it actually send "EXEC proc_getuser 'test', 'hello', 21, 0" to database.

Statement to replace the second SQL query - The following parameters can be used in the second SQL query replacement:

$INDEX - This variable will be replaced with the "param_index" value returned from the first SQL query replacement
$PORT - This variable will be replaced with the virtual server port
$SERVERIP - This variable will be replaced with the IP address of the virtual server where the ftp user is connected to