The database authentication feature allows the administrator to authenticate against an external data-source, rather than explicitly adding users to CompleteFTP's internal user-list. Typically, this data-source is a database table or spreadsheet containing a list of usernames, passwords or password-hashes, optionally home directories, groups, password-salts, RSA, DSA or ECDSA public keys. This is a feature of the Professional and Enterprise MFT only.
Database authentication may be set up in the General User Settings dialog, accessed from the User panel. The Database authenticator must be enabled and configured via the 'Configure' link shown below
Clicking the 'Configure' link will bring up the Database Configuration dialog.
In the database authentication configuration dialog, you can select the database type (ODBC, OleDb, Oracle...) you want from the 'Database type' drop-down list.
Support for new database types may be added by installing the appropriate ADO.NET provider and restarting the CompleteFTP service. Once this has been done the new database type should appear in this list. For example, support for MySQL may be added by downloading and installing the MySQL Connector/Net provider from the MySQL website and restarting the CompleteFTP service.
After a desired database type is selected, enter the connection-string in the 'Connection-string' text-box and press the 'Test connection' button to verify that it works. The website, connectionstrings.com, is an excellent resource for connection-strings. It lists various types, so remember to choose a connection-string that is appropriate to the selected database type. Also remember that the connection is made from the server, so if you are using CompleteFTP Manager on a machine other than the server then the connection-string may differ from the one you would use on your local machine.
The SQL query retrieves the user's password/password-hash. It may also optionally define additional fields that are described below: a Windows home-folder, a list of groups to which the user belongs, a password salt, and an RSA and/or DSA public key for the user.
| Field | Description |
| password or password-hash | This must be the first field returned. It is highly recommended that any passwords stored in a database are hashed. Even better, use a 'salt' prepended to passwords before they are hashed. See the fourth field, below. |
| Hone folder path | If a second field is returned then it will be assumed to be the Windows path of the user's home-folder. If the home-folder doesn't already exist when the user logs in then it will be created at that time. Note that this field will not be used if the type of the home-folder of the log-in-as user is "Virtual folder". |
| Groups | If a third field is returned then it will be assumed to be a comma-separated list of group-names (e.g. MyGroup1,MyGroup2,MyGroup3). The user will be treated as a member of all of the groups listed as well those of which the "defaultDatabase" user is a member. |
| Password salt | If a fourth field is returned then it will be assumed to be the 'salt' that was prepended to the password before it was hashed (if hashing is enabled). The salt should be a random string of at least 8 characters. Salting is intended to defeat rainbow table attacks. Rainbow tables are sets of pre-calculated hashes for common passwords. If salts are not used, attackers can simply match these hashes with the hashes in your database. If salts are used, a new rainbow table must be calculated for each salt value (and these tables are computationally expensive). |
| RSA public key | If a fifth field is returned then it will be assumed to be the RSA public key for the user. Public key fields should be text fields in either OpenSSH or SECSH format. |
| DSA public key | If a sixth field is returned then it will be assumed to be the DSA public key for the user. Public key fields should be text fields in either OpenSSH or SECSH format. |
| Log-in-as user-name | If a seventh field is returned then it will be assumed to be the log-in-as user-name. This user-name will be used instead of that log-in-as user-name that's been configured for the authenticator. |
| ECDSA public key | If an eighth field is returned then it will be assumed to be the ECDSA public key for the user. Because the order of fields must be maintained for legacy reasons, this field is after the Log-in-as user field. Public key fields should be text fields in either OpenSSH or SECSH format. |
If a field is required without the field preceding it, simply return the preceding field (or fields) as a null.
An SECSH format public key starts like this:
---- BEGIN SSH2 PUBLIC KEY ----
Comment: "imported-openssh-key"
AAAAB3NzaC1yc2EAAAABIwAAAIEA44J6LBloMWVvhOjMHZPnmgJWw+UWBl9nFEWa
An OpenSSH format public key starts like this:
ssh-dss AAAAB3NzaC1kc3MAAACBAJ3hB5SAF6mBXPlZlRoJEZi0KSIN+NU2iGiaXZXi9CDrgVxTp6/
sc56UcYCp4qjfrZ2G3+6PWbxYso4P4YyUC+61RU5KPy4EcTJske3O+aNvec/20cW7PT3TvH1+sxwGry
SELECT PasswordHash, HomeFolder,Groups,Salt FROM [Users] WHERE UserName='%UserName%'
SELECT Password, HomeFolder,null,null,SSHPublicKeyRSA FROM [Users] WHERE UserName='%UserName%'
SELECT Password, HomeFolder,null,null,SSHPublicKeyRSA,SSHPublicKeyDSA FROM [Users] WHERE UserName='%UserName%'
SELECT Password, null, null, null, null, null, null, ECDSA FROM [Users] WHERE UserName='%UserName%'
Users connecting via this method are subject to the settings of the "defaultDatabase" user, which may be found in the Users panel (and by selecting 'Show system users/folders/sites' in the main form's Options menu).
The administrator may, for example, enable only certain protocols for database user connections.
By default, the home folder is shown as /DatabaseUser. If the home folder is not provided by the query, the value of this folder will be used (which is a macro folder by default). If a home folder is provided by the query, the query's value will be used, but /DatabaseUser will still be displayed as the name of the user's home directory. The displayed name can be changed by modifying the name of the DatabaseUser system folder (which can be found in Folders panel if the "Show system users/folders/sites" menu item is currently checked) to use a macro, as described here.
For detailed steps on configuring external database users, please refer to Step-by-step guide: Authenticate users from a database.