Submit a ticketCall us

Get a crash course on Network Monitoring delivered right to your inbox
This free 7-day email course provides a primer to the philosophy, theory, and fundamental concepts involved in IT monitoring. Lessons will explain not only how to perform various monitoring tasks, but why and when you should use them. Sign up now.

Home > Success Center > Serv-U Managed File Transfer & Serv-U FTP Server > Serv-U File Server Administrator Guide > Server overview > Database access

Database access

Created by Anthony.Rinaldi_ret, last modified by Anthony.Rinaldi_ret on Jul 20, 2016

Views: 43 Votes: 0 Revisions: 2

Serv-U enables the use of an Open Database Connectivity (ODBC) database to store and maintain group and user accounts at the domain and server levels. You can configure the ODBC connections in two locations:

  • Domain > Domain Details > Database
  • Server > Server Details > Database.

Serv-U can automatically create all of the tables and columns necessary to begin storing users and groups in the database. Because Serv-U uses one set of table names to store its information, individual ODBC connections must be configured for each item which stores details in the database. In other words, the server and each domain must have a unique ODBC connection to ensure they are stored separately.

Configure a database

  1. Create an ODBC connection for Serv-U to use. SolarWinds recommends MySQL, but you can use any database that has an ODBC driver available. Use a System data source name (DSN) if Serv-U is operating as a system service, or a User DSN if Serv-U is operating as a regular application.
  2. Open the Serv-U Management Console and browse to the appropriate domain or server database settings. Enter the required information, and click Save.

If configuring the database connection for the first time, leave the Automatically create options selected. With these options selected, the Serv-U File Server builds the database tables and columns automatically.

SQL templates

Serv-U uses multiple queries to maintain the databases that contain user and group information. These queries conform to the Structured Query Language (SQL) standards. However, if your database has problems working with Serv-U, you may need to alter these queries. In the SQL Templates window, you can modify each query used by Serv-U to conform to the standards supported by your database.

Incorrectly editing these SQL queries could cause ODBC support to stop working in Serv-U. Do not edit these queries unless you are comfortable constructing SQL statements and are sure that it is necessary to enable ODBC support with your database software.

User and group table mappings

By default, Serv-U creates and maintains the tables and columns necessary to store user and group information in a database. However, if you want to connect Serv-U to an existing database that contains this information, you must customize the table and column names to conform to the existing database structure. Click User Table Mappings or Group Table Mappings to get started.

Serv-U stores information for a user or group in 10 separate tables. Only the User/Group Info Table and User/Group Dir Access Table are required. You can change the current table in the Object Table list. The Attribute column lists the attributes that are stored in the current table. The Mapped Database Value displays the name of the column that attribute is mapped to in the database. The first row displays the table name and you can change the name.

Certain tables, where the order of the entries is important, have a SortColumn attribute listed. This column is used to store the order in which rules are applied.

Click Edit or double-click the column name to edit a value.

When enabled, the table is accessed as needed. In special situations, a table that is not being used can be disabled to reduce the number of ODBC (database) calls. For example, if you do not use ratios and quotas, you can disable the User Ratio-Free Files, Per User Files Ratio, Per User Bytes Ratio, Per Session Files Ratio, and Per Session Bytes Ratio tables to prevent unnecessary ODBC calls. Use caution when you disable tables, because although the fields appear in dialogs, they will not be saved or loaded.

The User Info and Group Info tables cannot be disabled.

Case file: ODBC authentication

Authentication in the Serv-U File Server can be handled through an ODBC database, allowing for scripted account management and maintenance. To use ODBC functionality, migrate to ODBC authentication through a database. By storing credentials in settings in a database, accounts can be managed from outside the Serv-U Management Console through scripted database operations which can be built into many existing account provisioning systems. A DSN must first be created in Control Panel > Administrative Tools > ODBC Data Sources. Use a System DSN if Serv-U is running as a service or a User DSN if Serv-U is running as an application. After you create the appropriate DSN, enter the required information and click Save. Serv-U creates the tables and columns. You can manage database users and groups in the Database Users and Database Groups pages of Serv-U, located near the normal Users and Groups pages.

Data source name creation in Linux

Database access in Serv-U on Linux follows the same method as Serv-U on Windows, with the one change to how data source names are created. On Linux, you can create a DSN after installing the following packages:

  • mysql-connector-odbc
  • postgressql-odbc
  • unixodbc

Only the ODBC driver corresponding to the database needs to be installed. If Serv-U is running as a service, the next step is to edit the /etc/odbc.ini file, which contains all system-level DSNs. If Serv-U is running as an application, edit the ~/odbc.ini file instead, and then enter the parameters as follows:

[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 =

Adjust the names in brackets to the DSN name string you want. Finally, test the DSN with the isql %DSN% -c -v command.

For further customization options, see the Serv-U database integration guide.

Last modified
08:40, 20 Jul 2016

Tags

Classifications

Public