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.

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