Configuring Serv-U For ODBC User and Group Storage - KB Article #1748Related Articles -- 1692
Serv-U MFT Server supports the use of ODBC databases to store user accounts. Storing users in an ODBC database can provide much faster response times when user counts grow extremely large, and also makes the user list accessible to back-end user provisioning systems.
Our Serv-U Database Integration Guide (PDF) contains complete information about this process. The following article highlights some of the mechanics around configuration of the ODBC connection required by this connectivity.
Creating A Windows ODBC Connection
To allow Serv-U on Windows to access a database, an ODBC connection must first be established in Windows. ODBC, or Open Database Connectivity, is an open standard for allowing applications to interface with databases. To establish this connection, follow the steps below:
- Open the Control Panel | Adminstrative Tools | Data Sources (ODBC) menu
- If Serv-U is running as a service, open the System DSN tab - if Serv-U is going to run as an application (this is uncommon), open the User DSN tab
- Click on "Add", select the data source to be used (most common are "SQL Server" for MS SQL 2000, "SQL Native Client" for MS SQL 2005, and "MySQL ODBC 3.51 Driver" for MySQL), then click on "Next"
- Specify the username and password for the database server, as well as the specific database that will be in use (these steps will vary based on database and ODBC driver)
Creating A Linux ODBC Connection
To allow Serv-U on Linux to access a database, an ODBC connection must first be established in Linux. This requires that the following packages first be installed on the Linux server:
There are two kinds of Data Source Names (DSNs) - User DSNs, which are available in the user context only, and System DSNs, which are available for the whole system and for all users. Precedence is IMPORTANT - if you have identically named DSNs in the User and System levels, the User DSN takes precedence. User DSNs are created in ~/.odbc.ini. System DSNs are created in /etc/odbc.ini. Remember that you must be logged in as root to make changes to /etc/odbc.ini. To create your new DSN, use the formats below for MySQL and Postgres:[MySQL-test]
Description = MySQL test database
Trace = Off
TraceFile = stderr
Driver = MySQL
SERVER = YOURIPADDRESS
USER = USERNAME
PASSWORD = PASSWORD
PORT = 3306
DATABASE = YOURDATABASE
Description = Test to Postgres
Driver = PostgreSQL
Trace = Yes
TraceFile = sql.log
Database = YOURDATABASE
Servername = YOURIPADDRESS
UserName = USERNAME
Password = PASSWORD
Port = 5432
Protocol = 6.4
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
The names in brackets should be adjusted to your needed DSN name string.
Test New Linux DSN
Testing the new DSN can be performed using the "isql" application, using the syntax below.
[rhinosoft@rhinofc13x64DEV ~]$ isql %DSN% -c -v Be sure to replace %DSN% with the name of your DSN. This will allow you to confirm whether your DSN is operating. Remember that if you have identically named DSNs in the Root and User levels, the User level DSN takes precedence.
Configuring Serv-U For ODBC
Once the ODBC connection has been created and tested, Serv-U can be automatically configured to use this connection by following the steps below:
- Navigate to the Domain Details | Database menu in Serv-U (if using a database to store Global Users and Groups, use the System Details | Database menu)
- Enter the Data Source Name (DSN), and the user name and password used to access the database
- Click on "Save"
NOTE 1: It is highly recommended that each Domain and Server be assigned its own database for user and group storage rather than creating custom mappings for each domain.
Customizing ODBC Mappings/Settings
Serv-U also allows Administrators to customize the database fields in each table. Since Serv-U automatically generates all the tables and columns necessary when the Save button is clicked, any customization must be done before ODBC storage is enabled. The following can be customized:
- Automatically create required tables: If this option is checked, Serv-U will automatically create the required tables for ODBC authentication. This option is recommended as it eases the setup process greatly.
- Automatically create required columns: If this option is checked, Serv-U will automatically create the required columns for ODBC authentication. This option is recommended as it eases the setup process greatly.
- SQL Templates: This button allows the SQL commands used for various key functions to be altered. These are critical for proper ODBC functionality, and should only be changed by experienced database administrators for custom database configurations.
- User Table Mappings: This button allows the individual columns used in each user-related table to be altered, if custom columns must be used.
- Group Table Mappings: This button allows the individual columns used in each group-related table to be altered, if custom columns must be used.