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