Configuration Database Schema

CompleteFTP stores configuration data in a SQL Server Compact database. Modifying this database directly is not generally advised as it's very easy to render CompleteFTP unusable. It is however possible to do using the cftpconfig tool. EnterpriseDT advises users wishing to perform anything but the simplest property-changes to contact support for advice prior to putting changes into production.

This document describes the schema of the CompleteFTP configuration database. It lists the fields of the tables along with notes on how rows should be added. Note that the constraints on the database do not fully embody the format of the data, so the notes should be read carefully.

Tables

Table: User
Purpose: Stores all explicitly defined users. This does not include users that are authenticated using authentication extensions, including automatic windows users and external database users.
Notes: Create one row for each new user.
Field SQL Type .NET Type Required Default Comments
UserID uniqueidentifier Guid yes
Generate new for each user
AuthenticationTypeID nvarchar(40) string yes
"SERVER" for non-Windows users and "WINDOWS" for Windows users
AuthorizationTypeID nvarchar(40) string yes
"SERVER" for non-Windows users and "WINDOWS" for Windows users
Description nvarchar(100) string no
May be NULL. Only used in event-macro
Email nvarchar(256) string no
May be NULL. Only used in event-macro
Enabled bit bool yes
Usually TRUE
ExpiryDate datetime DateTime no
NULL if no expiry
FTPEnabled bit bool no TRUE TRUE if FTP is to be allowed; FALSE otherwise.
FTPSEnabled bit bool no TRUE TRUE if FTPS is to be allowed; FALSE otherwise.
FullName nvarchar(100) string no
May be NULL. Only used in event-macro
HTTPEnabled bit bool no TRUE TRUE if HTTP is to be allowed; FALSE otherwise.
HTTPSEnabled bit bool no TRUE TRUE if HTTPS is to be allowed; FALSE otherwise.
PasswordEncrypted image byte[] no
Leave as NULL
PasswordHash nvarchar(100) string no
NULL for Windows users and MD5 (unsalted) for non-Windows users
Permissions nvarchar(3) string no
NULL for full permissions or 555 for read-only
QuotaSpeedDownload bigint long no
NULL or download-speed in bytes/sec
QuotaSpeedUpload bigint long no
NULL or download-speed in bytes/sec
QuotaStorage bigint long no
NULL or storage-quota in bytes
SCPEnabled bit bool no TRUE TRUE if SCP is to be allowed; FALSE otherwise.
SFTPEnabled bit bool no TRUE TRUE if SFTP is to be allowed; FALSE otherwise.
SSHPublicKeyDSA image byte[] no
NULL or OpenSSH format DSA public key
SSHPublicKeyRSA image byte[] no
NULL or OpenSSH format RSA public key
SSHTerminalEnabled bit bool no FALSE TRUE if SSH is to be allowed; FALSE otherwise.
SSLCertificate image byte[] no
NULL or PEM format SSL cert (no private key)
System bit bool yes
Must be FALSE
UserName nvarchar(100) string yes
Must be unique. Must include "MYDOMAIN\" for Windows domain users
CreatedTime datetime DateTime yes
Set only when creating
ModifiedTime datetime DateTime yes
Update every time a change is made
Table: Node
Purpose: Stores folders of the virtual file-system.
Notes: (1) If each user is to have their own unique home-folder then you must create one row for each user. Set the OwnerID to the GUID of the new user. (2) If any of OwnerID, GroupID or Permissions is NULL then the permissions will be inherited from the parent.
Field SQL Type .NET Type Required Default Comments
NodeID uniqueidentifier Guid yes
Generate new for each folder
Configuration nvarchar(4000) string yes
Full Windows path for Windows folder. Append ?%AutoCreate%" to path to have CompleteFTP create the Windows folder when it's first accessed by user.
GroupID uniqueidentifier Guid no
"8a9b1ba4-5d1b-41f6-893a-f99a319be033" for users group.
Name nvarchar(100) string yes
Must be unique amongst nodes with same ParentNodeID
NodeTypeID uniqueidentifier Guid yes
"c3435efd-3b78-44b1-b270-79667906afa9" for Windows folder
OwnerID uniqueidentifier Guid no
GUID of user who owns the folder
ParentNodeID uniqueidentifier Guid no
NULL for root folder, otherwise GUID of parent folder
Permissions nvarchar(12) string no
UNIX-style permissions, e.g. 750 is full-control by owner, read-only by group and none for anyone else.
System bit bool yes
Leave as NULL or set to FALSE. Never set to TRUE.
CreatedTime datetime DateTime yes
Set only when creating
ModifiedTime datetime DateTime yes
Update every time a change is made
Table: SiteUser
Purpose: Stores site-specific information for users - mainly whether or not the user is enabled on the site and what its home-folder is.
Notes: You must create one for every site-user combination. By default there are two sites ('Default Site' and 'Admin') so you must create at least two rows in this table for each user, each with a different SiteID (see below).
Field SQL Type .NET Type Required Default Comments
SiteID uniqueidentifier Guid yes
"c7a09c8d-4a7c-4b17-90fd-9897562ee979" for default site and "d7183a00-f941-4b89-a3dc-bd3d1a79c2ca" for admin site.
UserID uniqueidentifier Guid yes
GUID of user
Enabled bit bool yes TRUE TRUE for default site and FALSE for admin site
NodeID uniqueidentifier Guid no
GUID of home folder. May only be NULL if Enabled is FALSE
CreatedTime datetime DateTime yes
Set only when creating
ModifiedTime datetime DateTime yes
Update every time a change is made
Table: Membership
Purpose: Stores membership of users to groups.
Notes: Add at least one row to make the new user a member of the 'users' group.
Field SQL Type .NET Type Required Default Comments
UserID uniqueidentifier Guid yes
GUID of user
GroupID uniqueidentifier Guid yes
"8a9b1ba4-5d1b-41f6-893a-f99a319be033" for users group.
CreatedTime datetime DateTime yes
Set only when creating
ModifiedTime datetime DateTime yes
Update every time a change is made
Table: Group
Purpose: Stores user-groups.
Notes: -
Field SQL Type .NET Type Required Default Comments
GroupID uniqueidentifier Guid yes
GUID of group
Name nvarchar(100) string yes
Must be unique
System bit bool no
Leave as NULL or set to FALSE. Never set to TRUE.
CreatedTime datetime DateTime yes
Set only when creating
ModifiedTime datetime DateTime yes
Update every time a change is made
Table: Server
Purpose: Stores information about servers, including currently available IP addresses and activation keys. For stand-alone installations there will be only one row. In clusters each machine will have one row.
Notes: There must be one row for each node in a cluster. For stand-alone installations there will only be one row. MachineName, LatestMachineID and IPAddresses are set automatically by the CompleteFTP Service.
Field SQL Type .NET Type Required Default Comments
ServerID uniqueidentifier Guid yes
GUID of the server
ActivationKey nvarchar(1000) string no
Unlocks the purchased edition of CompleteFTP on this machine
ConnectionInfo ntext string no
Information about how to connect to this server remotely. This field is encrypted and can only be set using CompleteFTP Manager.
IPAddresses nvarchar(1000) string no
Do not set this field.
IsPrimary bit bool no
Is this the primary node in the cluster?
LatestMachineID uniqueidentifier string no
Do not set this field.
LogLevel nvarchar(40) string no
Level of logging produced on this machine. Must be on of: off, error, warning, information, debug or all.
MachineName nvarchar(100) string no
NetBIOS name of machine [Set automatically]
Name nvarchar(100) string yes
Must be unique
SyncEnabled bit bool no
Is synchronization enabled for this cluster-node?
SyncPeriod int int no
Milliseconds between synchronizations
CreatedTime datetime DateTime yes
Set only when creating
ModifiedTime datetime DateTime yes
Update every time a change is made
Table: Site
Purpose: Stores information about sites.
Notes: By default there are two sites: one for administration and one for general users. The admin site must always exist, but the other site may be replaced.
Field SQL Type .NET Type Required Default Comments
SiteID uniqueidentifier Guid yes
GUID of the site
AnonymousEnabled bit bool

Are anonymous FTP connections allowed?
AnonymousHTTPEnabled bit bool no TRUE Is anonymous HTTP enabled on this site?
AnonymousMaxConnections int int

Maximum number of anonymous FTP connections.
AnonymousUserID uniqueidentifier Guid

UserID of the anonymous user.
ArchiveNavEnabled bit bool no FALSE Is ZIP-file navigation enabled?
ArchiveNavSuffix nvarchar(20) string no .folder Extension to add to ZIP-file when listing ZIP-folder.
AutoBanDuration int int no 60000 Period (in ms) for which an auto-ban remain in force.
AutoBanTriggerCount int int no 10 Number of authentication failures until an IP address is banned.
AutoBanTriggerPeriod int int no 3600000 Period (in ms) within authentication-failures are counted.
BackslashPathSep bit bool no FALSE Allow backslashes when resolving paths in the file-system.
DefaultDomain nvarchar(100) string no
Default Windows domain to use when authenticating user-names that don't specify a domain.
DefaultHomeDirID uniqueidentifier Guid yes
NodeID of the folder that's the parent to default user home-folders
Enabled bit bool yes
Is this site enabled?
Encoding nvarchar(40) string no
Character-encoding for all strings in all protocols. NULL, the default, means UTF-8. To get a listing of valid values, search for 'code page identifiers'
FTPEnabled bit bool yes
Is FTP enabled on this site?
FTPSEnabled bit bool yes
Is standard (explicit) FTPS enabled on this site?
FTPSImplicitEnabled bit bool yes
Is legacy (implicit) FTP enabled on this site?
FTPSVerifyClient b bool yes
Require FTPS clients to present their certificates?
HideProductVersion b bool no FALSE Don't show product and version in any communications (set to true for PCI compliance).
HomeDirIsRoot b bool no FALSE Should user's home-folders be shown as their root folder?
HTMLListingTemplate ntext string no
Template to use for HTML listings.
HTTPEnabled bit bool yes
Is HTTP enabled on this site?
HTTPSEnabled bit bool yes
Is HTTPS enabled on this site?
IPFilterID uniqueidentifier Guid no
IPFilterID of site's IP filter.
ListingFormatID nvarchar(40) string yes
FTP directory listing format. 'UNIX' for UNIX-style listings, 'WINDOWS' for Windows-style listings or 'FILENAME' for listings that only show file-names.
MaxConnections int int no
Maximum number of simultaneous (non-HTTP) connections allowed for this site
MaxConnectionsHTTP int int yes 200 Maximum number of simultaneous HTTP connections allowed for this site
MaxLoginAttempts int int no
Maximum number of login attempts allowed in a single connection
Name nvarchar(100) string yes
Must be unique
PASVIP nvarchar(100) string no
IP address to tell clients to connect to for passive-mode transfers
PASVPortMax int int no
Maximum port-number to use for passive-mode transfers
PASVPortMin int int no
Minimum port-number to use for passive-mode transfers
PortFTP int int yes
Port for FTP and standard (explicit) FTPS
PortFTPSImplicit int int yes
Port for legacy (implicit) FTPS
PortHTTP int int yes
Port for HTTP
PortHTTPS int int yes
Port for HTTPS
PortSFTP int int yes
Port for SFTP, SCP and SSH terminal
SCPEnabled bit bool yes
Is SP enabled on this site?
SSHAuthTypes int int no
SSH authentication-types allowed for SSH (null by default)
SSHCipher int int no
Ciphers allowed for SSH (null by default)
SSHCompression int int no
Compression algorithms allowed for SSH (null by default)
SSHKeyAlgorithm int int no
Key-types allowed for SSH (null by default)
SSHKeyExchange int int no
Key-exchange algorithms allowed for SSH (null by default)
SSHKeyPairDSA image byte[] yes
Site's DSA private key stored in SECSH format.
SSHKeyPairRSA image byte[] yes
Site's RSA private key stored in SECSH format.
SSHMAC int int no
MAC algorithms allowed for SSH (null by default)
SSHTerminalEnabled bit bool yes
Is SSH terminal access enabled on this site?
SSLCertificate image byte[] yes
Site's SSL/TLS certificate in PFX format (by default no password is used)
SSLCertificatePassword nvarchar(100) string no
Encrypted password for certificate (null by default)
SSLCipherSuites int int no
Cipher-suites allowed for SSL/TLS (null by default)
System bit bool no
Is this a system site? Must be FALSE for all sites except the admin site.
TimeoutHTTP int int no 1800000 Time-out period for HTTP sessions.
TimeoutIdle int int no
Milliseconds of inactivity before a connection is closed.
TimeoutLogin int int no
Milliseconds allowed to log in.
TimeoutPassiveWait int int no
Milliseconds to wait for a passive channel connections before giving up.
TimeoutStalled int int no
Milliseconds of inactivity before a transfer is considered to have stalled.
WelcomeMessage nvarchar(2000) string no
Message shown to users when they connect to this site
CreatedTime datetime DateTime yes
Set only when creating
ModifiedTime datetime DateTime yes
Update every time a change is made
Table: ServerSite
Purpose: Stores server-specific information about sites, including enabled-status and IP bindings. For each server in the configuration there will be a row for each site.
Notes:
Field SQL Type .NET Type Required Default Comments
ServerID uniqueidentifier Guid yes
ServerID of the server
SiteID uniqueidentifier Guid yes
SiteID of the site
Enabled bit bool no TRUE Is the specified site enabled on the specified server?
FTPInterfaces nvarchar(1024) string no
IP addresses to listen on for FTP (null means all).
HTTPInterfaces nvarchar(1024) string no
IP addresses to listen on for HTTP (null means all).
SFTPInterfaces nvarchar(1024) string no
IP addresses to listen on for SFTP (null means all).
CreatedTime datetime DateTime yes
Set only when creating
ModifiedTime datetime DateTime yes
Update every time a change is made
Table: NodeType
Purpose: Defines the various types of folders in the virtual file-system.
Notes: Every node-type must have a plug-in of the "File System" type (PlugInTypeID=0).
Field SQL Type .NET Type Required Default Comments
NodeTypeID uniqueidentifier Guid yes
GUID of the node-type
CanHaveChildren bit bool yes
Can folders of this type have sub-folders?
Name nvarchar(100) string yes
Must be unique
PlugInID uniqueidentifier Guid yes
PlugInID of the plug-in defining the class that implements this node-type.
System bit bit no
Must be FALSE.
Visible bit bit no
Show this folder-type in the list of folder-types when creating new folders.
CreatedTime datetime DateTime yes
Set only when creating
ModifiedTime datetime DateTime yes
Update every time a change is made
Table: PlugIn
Purpose: Defines plug-ins (aka extensions) of all types
Notes: Extensions of the "File System" type (PlugInTypeID=0) will not be usable unless a NodeType has been defined for them. Extensions of the "Authentication" type (PlugInTypeID=3) must have a row for each site (allowing distinct configurations for each site).
Field SQL Type .NET Type Required Default Comments
PlugInID uniqueidentifier Guid yes
GUID of the plug-in
AssemblyPath nvarchar(1024) string no
NULL or path to assembly that contains the class
ClassName nvarchar(400) string yes
Full name of class that implements the plug-in.
Configuration ntext string no
Configuration data for the plug-in. Note that FileSystemAdapter plug-ins also have the option of storing configuration information in each individual Node row that references the plug-in.
EditorClassName nvarchar(100) string no
Name of editor class (optional)
Name nvarchar(100) string yes
Must be unique
PlugInTypeID uniqueidentifier Guid yes
PlugInTypeID of the plug-in-type of this plug-in.
System bit bool yes
Must be FALSE.
CreatedTime datetime DateTime yes
Set only when creating
ModifiedTime datetime DateTime yes
Update every time a change is made
Table: PlugInType
Purpose: Enumeration of plug-in types.
Notes: Do not modify this table. Values are (0=File System, 1=Custom Commands, 2=Event, 3=Authentication)
Field SQL Type .NET Type Required Default Comments
PlugInTypeID int int yes
See notes
Name nvarchar(100) string yes
See notes
CreatedTime datetime DateTime yes
See notes
ModifiedTime datetime DateTime yes
See notes
Table: Authenticator
Purpose: Stores authenticator information for specific sites.
Notes: Plug-ins must be of the Authentication type (i.e. PlugInTypeID=3). There must be one row for each site-authenticator combination).
Field SQL Type .NET Type Required Default Comments
PlugInID uniqueidentifier Guid yes
PlugInID of the plug-in.
SiteID uniqueidentifier Guid yes
SiteID of the site.
UserID uniqueidentifier Guid yes
UserID of the log-in-as user that is used as a template for users logging in using the references authenticator.
Configuration nvarchar(4000) string no
Site-specific authentication configuration.
Enabled bit bool yes
Is this authenticator enabled on this site?
CreatedTime datetime DateTime yes
Set only when creating
ModifiedTime datetime DateTime yes
Update every time a change is made