Integrating Xlight FTP Server with the database through ODBC

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

In this example, we use the MySQL database to demonstrate the procedure of integrating the Xlight FTP Server with the database through ODBC. For other databases, setup procedures are very similar. In theory, the 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, and ACCESS databases. Note: after the 30-day evaluation period, this function is only supported by the Professional edition of Xlight FTP Server.

To use a database through ODBC, you need to configure the 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 the "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 the data source. You can use any name for the data source. The MySQL database is running on the remote Linux machine "192.168.11.2". We will create a database "test" in this machine; you can use any database name for the 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 configure the external database in the Xlight FTP Server from [Global Options]->[Advanced]->[ODBC database configuration]. You have to fill in the "User" and "Password" fields with a database user. If you want to create a 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 the "Create" button to let the Xlight FTP Server create database tables. Or you can click the "Test" button to test if you have the correct database tables.
Note: For a 64-bit system, you need to configure 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 details.

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

7. This step is used only for creating database tables manually. If you already created database tables in 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 in the folder 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 the database from the machine running Xlight FTP Server. This user can have only SELECT right to the 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 the database can be made only from the "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 a virtual server. In the same Xlight FTP Server, you can have some virtual servers to use the ODBC database, and some don't. If you choose the option "Skip host_id column" or "Skip host_port column", during database queries, Xlight FTP Server will skip the "host_id" or "host_port" column. Because the "host_id" column could be used to identify different machines, and the "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 the database only for authentication, 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 the user's password stored inside the database. When this option is turned on, all other settings will be from the settings of this user in the local FTP server.

Create an FTP user in the database

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

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

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

The user's password can be stored in MD5/SHA1 hash or clear text in the database. The MD5 hash is the default setting. 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 an MD5 hash calculation tool by searching "MD5 calculator" in Google. In our example below, the username is "test" and his password is "test" also, and the MD5 hash is "098f6bcd4621d373cade4e832627b4f6". It is shown below:

Note: If a user's home_path doesn't exist, when he logs in for the first time, his home path will be created by Xlight FTP Server. For example, if the home_path "c:\wutemp" for the above "test" user doesn't exist. When "test" user logins, Xlight FTP Server will create it automatically. The implicit virtual path for the 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 the parameter settings you want, and put the index number into the "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 the "host_id", "host_port", and "username" columns are required to have value, you must fill them. If using the "home_path" column, the "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 the "virtual_path_index" column value from "virtual_path_table". If it contains multiple "virtual_path_index", they must be separated by ","(comma).

The variable %username% can be used for the real path in the virtual_path_table. %username% will be replaced with the actual user name after user logs in. If the real path doesn't exist when a user logins, Xlight FTP Server will create it automatically.

Troubleshooting database authentication problem

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

Use other databases

When a user logs in to the 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 are 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 for this user.

It is possible that the "acct_table" and "acct_param_table" used by the 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 at the returned result sets of both SQL queries. If the first SQL query replacement statement returns a row from the 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 the FTP server, you should let the 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 the system administrator to replace these two SQL query statements to support other databases and advanced applications, such as "stored procedure" in the database. You can look at the example for how to use the 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 the username of the FTP user
$PASS - This variable will be replaced with the password(MD5 or Text) of the FTP user
$ID - This variable will be replaced with the "Local Host ID" value of the machine
$PORT - This variable will be replaced with a 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 the virtual server that an FTP user is connected to

For example, if you use "EXEC proc_getuser '$USER', '$PASS', $ID, $PORT" for the first SQL query replacement, when the user "test" logins with the password "hello" to FTP server port 21 and host_id 1, after replacement, it sends "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 that an FTP user is connected to