Serv-U DB Integration Guide
Serv-U DB Integration Guide
Serv-U DB Integration Guide
Contents
Introduction ............................................................................................................................................................3
Integrating With A New Database ......................................................................................................................4
Integrating With An Existing Database..............................................................................................................5
Custom Database Mapping ................................................................................................................................6
Creating an ODBC Connection ..........................................................................................................................7
Basic Database Schema .....................................................................................................................................9
Generating Special Values .............................................................................................................................. 10
Example Code.................................................................................................................................................... 12
Schema Reference............................................................................................................................................ 16
Notices ................................................................................................................................................................ 41
Introduction
Serv-U (http://www.Serv-U.com/) is a file server that allows end users to transfer files via FTP, FTPS,
SFTP, web browsers, and mobile devices. Serv-U runs on either Windows or Linux and supports both
IPv4 and IPv6 networks. In addition, it runs natively on both 32-bit and 64-bit versions of Windows
and Linux, taking advantage of performance benefits inherent in 64-bit computing.
To authenticate end users Serv-U can use its own user database or an external collection of user and
group information. The two types of external collections supported today are Windows Active
Directory (on Windows-deployed servers only) and relational databases accessible via ODBC. This
paper discusses the latter case: how to use a relational database accessible via ODBC to provide
Serv-U with user and group information.
Serv-U MFT Server supports integration with external database engines like Microsoft SQL Server,
MySQL, PostgreSQL, Oracle, and more. Serv-U requires only an ODBC connection name,
username, and password to an empty database to integrate with any supported database engine.
Windows provides ODBC functionality via the “Data Sources” menu in the Control Panel, while Linux
provides this functionality via the “unixODBC” package, found in standard Linux repositories. Storing
users in an ODBC database can provide much faster response times when user counts grow above
several thousand, and also makes the user list accessible to back-end user provisioning systems.
This manual describes how to authenticate end users with relational databases. Two common
scenarios are covered:
1. Create a new empty database on the relational database of your choice. (Refer to your
Microsoft SQL Server, SQL Database, MySQL, or Postgres manual or consult with your local
DBA if you need help doing this.)
2. Create an ODBC connection to your database. For more information, see “Creating an ODBC
Connection”.
4. Ensure that the Automatically create required tables and the Automatically create
required columns options are both enabled.
5. Enter the Data Source Name (DSN) (see “Creating an ODBC Connection”), and the user
name and password used to access the database.
6. Click Save.
7. Test the ODBC connection by creating a new user account in the Users > Database Users
menu (or Global Users > Database Users), and see if the user is correctly displayed in the
list. Errors can be reviewed in the Domain Activity > Log menu.
Serv-U will proceed with the setup and create all the necessary tables and columns to begin database
user storage immediately.
Additional Domains
Serv-U supports the setup of multiple Domains, which are collections of Users and Groups bound by
common settings, such as a common Fully Qualified Domain Name or client company. When Serv-U
is set up this way, each Domain in Serv-U should be configured with its own database.
Global Users and Groups, which are special Users and Groups that can access any Serv-U Domain,
can also be stored in a database. Like additional Domains in Serv-U, the Global Users and Groups
should also be configured on their own database.
1. Create an ODBC connection to your existing database. For more information, see “Creating
an ODBC Connection”.
3. Disable the Automatically create required tables and the Automatically create required
columns options in Serv-U to prevent unnecessary table creation.
4. Enter the Data Source Name (DSN) (see “Creating an ODBC Connection”) that was created
earlier, the username, and the password to access the database.
5. Click Save.
The actual work to integrate Serv-U with the database begins at this point. Via the User Table
Mappings and Group Table Mappings menus, Serv-U must be configured with the correct table
names with which to integrate with, and which field names to use for its various database attributes.
For a comprehensive list of all attributes and standard labels used by Serv-U, see the accompanying
documentation in Excel spreadsheet format.
Additional Domains
Serv-U supports the setup of multiple Domains, which are collections of Users and Groups bound by
common settings, such as a common Fully Qualified Domain Name or client company. When Serv-U
is set up this way, each Domain in Serv-U should be configured with its own database.
Global Users and Groups, which are special Users and Groups that can access any Serv-U Domain,
can also be stored in a database. Like additional Domains in Serv-U, the Global Users and Groups
should also be configured on their own database.
Similarly, in the MySQL Workbench, we can see the field AcmeUserID shown, which can be used by
existing account management systems for user account provisioning.
1. Open the Control Panel > Administrative Tools > Data Sources (ODBC) menu.
2. 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.
3. Click Add, select the data source to be used (most common are "SQL Server" for Microsoft
SQL Server 2000, "SQL Native Client" for Microsoft SQL Server 2005 and SQL Server 2008,
and "MySQL ODBC 3.51 Driver" for MySQL), and then click Next.
4. 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).
mysql-connector-odbc
posgressql-odbc
unixODBC
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. Since Serv-U will typically be running as a service, creating a System DSN is
recommended. 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
[PostgreSQL-test]
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
ConnSettings =
The names in brackets should be adjusted to your needed DSN name string.
Testing the new DSN can be performed using the "isql" application, using the syntax below.
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.
Users Table
Table Name – The name of the table containing users. “SUUsers” by default.
LoginID - String value containing the username of the account.
PasswordChangedOn - Integer value calculated using time_t, showing the last date the password
was change
PasswordEncrypted - If the password is encrypted using the methods described in the following
section, “Generating Encrypted Passwords”, this value must be 1. If the password is stored plaintext,
enter 0.
HomeDir - Enter the full path to the user's home directory
LockInHomeDir - If the user must be locked into their home directory, enter 1. If not, enter 0
UserDirAccess Table
Table Name – The name of the table containing groups. “UserDirAccess” by default.
LoginID – String value containing the username of the account.
SortIndex - The order in which the rule will be processed for the user specified in "LoginID".
Dir - The directory of the Dir Access rule, stored in true format (C:\ftproot\user,
\\servername\files\folder, or /usr/ftproot, no escape characters needed).
Access - The privileges granted, calculated using the directions in the following section, “Generating
Directory Access Values”.
MaxSize - If applicable, the maximum size the directory may reach before no more uploads are
allowed.
For a comprehensive list of all tables and fields that Serv-U supports as well as the default values,
see the “Schema Reference” section.
Data Concurrency
Information about user accounts is loaded into memory when a user logs into Serv-U. If a change to a
user account is written manually to the database, it can cause this information to be overwritten.
Manually coded mechanisms must be able to wait for a user to log off before writing changes to
ensure they are saved properly.
To generate a Serv-U compatible encrypted password for insertion into an ODBC user database, two
random characters (the 'salt' - in the range a..z, A..Z) are added to the beginning of the clear-text
password. This is then hashed using MD5 and the resulting hash is hex-encoded. The result of this is
written as plain-text starting with the two salt characters followed by the hex-encoded hash.
Action Result
Password is established TestPassword
Two salt characters are added to increase cbTestPassword
complexity
Result is hashed using MD5 and displayed in 8EA58F0751BAA5AF391253F7DADD3D46
hexadecimal
Two salt characters are added to the beginning of cb8EA58F0751BAA5AF391253F7DADD3D46
previous hash
When verifying a user's password, Serv-U will do the same. It parses the salt from the user's stored
password (i.e., "cb" in this case), prepends it the password the user sent to it by the client, MD5
hashes it, and compares the result with the stored hash. If the values are equal, then the entered
password is correct.
To calculate the value of a DirAccess entry, the appropriate values (represented in hexadecimal
below) must be added and converted to decimal, then used in the DirAccess column of the CSV file.
Each value represents a specific bit which can be turned on or off by adding or removing that value.
For example, to grant Read/Write/List access, you add:
1
+2
+100
=103
This value must then be converted to hex, which can be done by opening the Windows Calculator
(Start > All Programs > Accessories > Calculator), changing it to Scientific Mode (View > Scientific
Mode), selecting Hex, typing in the value, then selecting "Dec" for an automatic conversion. For
example, hexadecimal 103 and decimal 259 are the same value. 259 is the value that should be
entered in the DirAccess column for the appropriate user. The values of all permissions are listed
below.
File Permissions
Read=0x00000001
Write=0x00000002
Append=0x00000004
Delete=0x00000008
Rename=0x00000010
Execute=0x00000020
Directory Permissions
List=0x00000100
Create=0x00000200
Remove=0x00000400
Rename=0x00000800
Subdirectory Permissions
Inherit=0x00001000
Example Code
To aid in the deployment of database integration, we have provided sample code for the special
values used in the Serv-U Database: encrypted passwords, and directory access calculations. The
example are provided in PHP, VB.NET, and C#.NET.
VB.NET
'
' INPUT: $strPwdOrig (a string containing a plaintext password)
' OUTPUT: $strDBValue (a string containing the value to insert or check in
the database)
'
' This is the password to "one way encrypt"
Dim strPwdOrig as String = "TestPassword" ' MAKE THIS VALUE DYNAMIC IN
PRODUCTION
Response.Write("|Original Password:" + strPwdOrig + "|") ' REMOVE THIS
LINE IN PRODUCTION
'
' This is the set of characters used in the two-character salt (note
hardcoded length)
Dim strSaltChars as String =
"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
' Generate and display the salt
Dim rand As New Random()
Dim strSalt as String = ""
strSalt = strSaltChars.Substring(rand.Next(62), 1) +
strSaltChars.Substring(rand.Next(62), 1)
strSalt = "cb" ' UNCOMMENT THIS TO TEST EXAMPLE IN SERV-U MANUAL
Response.Write("|Salt:"+strSalt + "|") ' REMOVE THIS LINE IN PRODUCTION
'
Dim x As New System.Security.Cryptography.MD5CryptoServiceProvider()
Dim bs() As byte = System.Text.Encoding.UTF8.GetBytes(strSalt + strPwdOrig)
bs = x.ComputeHash(bs)
Dim sbTemp As System.Text.StringBuilder = new System.Text.StringBuilder()
Dim i = -1
Dim strPwdHash as String = ""
For i = 0 To bs.Length - 1
strPwdHash &= bs(i).ToString("x").PadLeft(2, "0")
Next i
Response.Write("|Encrypted Password:"+strPwdHash + "|") ' REMOVE THIS LINE
IN PRODUCTION
'
Dim strDBValue as String = strSalt + strPwdHash
Response.Write("|Value in Database:"+strDBValue + "|") ' REMOVE THIS LINE
IN PRODUCTION
'
End Sub
C#.NET
PHP
$intServUFolderCreate = 512;
$intServUFolderRemove = 1024;
$intServUFolderRename = 2048;
$intServUSubfolderInherit = 4096;
// To use these, sum them and apply
$intTestReadWriteList = $intServUFileRead + $intServUFileWrite + $intServUFolderList;
echo "Serv-U Permission Value is ".$intTestReadWriteList;
VB.NET
C#.NET
Schema Reference
Programmers and integrators can use this schema to manage Serv-U users and groups, including
their related folder permissions, virtual folders, IP access, events, and other attributes.
Schema Diagram
The two core tables are “SUUsers” and “SUGroups”. The two are tied together in an “N:N”
relationship via the “UserGroupName” table. (I.e., each group may have multiple users as members
and each user may below to multiple groups.) “LoginID” is the primary key of SUUsers and
“GroupName” is the primary key of SUGroups” – values in these fields should be unique.
applies.
SortIndex INT NULL The order of the IP Access rule for the user account.
The IP Address or range to be permitted or denied.
IP VARCHAR(255) NULL IPv4, IPv6, wildcards, and CIDR blocks permitted.
Description VARCHAR(255) NULL Text description of the rule.
Whether to allow or deny the IP address or range. 0
Allow VARCHAR(2) NULL for deny, 1 for allow.
users.
The name of the account to whom the ratio-free
LoginID VARCHAR(255) NULL file mask applies.
The path or file name of the file or file type
against which upload and download ratios will
Mask VARCHAR(255) NULL not be applied.
Notices
This document is provided for use with the setup and maintenance of the Serv-U File Server. This
manual is provided “AS IS” and without warranties as to the accuracy of the information or any other
warranties whether expressed or implied. Because of the various hardware and software
environments into which Serv-U ® may be put, NO WARRANTY OF FITNESS FOR A PARTICULAR
PURPOSE IS OFFERED.
Good data processing practice dictates that any new program should be thoroughly tested by the user
with non-critical data before relying on it. The user must assume the entire risk of using the program.
ANY LIABILITY OF THE SELLER WILL BE LIMITED EXCLUSIVELY TO PRODUCT
REPLACEMENT OR, AT THE SELLER’S DISCRETION, A REFUND OF PURCHASE PRICE.
Contact Information
SolarWinds, Inc.
Phone: +1 (855) 498-4154