HS-PCI in the Auditing Database

If you are not using the MSDE database included with the EFT Server installer, refer to Using a SQL Database as the Auditing Database for information about using SQL as the database. Administrators that prefer using command-line tools can use oSQL to create the auditing database and tables.

The file PCI_DB_Tables.sql is installed to the EFT Server installation directory (by default, C:\Program Files\GlobalSCAPE\EFT) if you did not install the MSDE database and you are upgrading from a version of EFT Server prior to 5.1, or if the connection to the auditing database is not available when you run the installer. Refer to the procedure in Alternative Method for Creating EFT Database Tables on SQL for the procedure for creating EFT Server database tables on SQL. You can create the tables for the HS-PCI module using the same procedure, and inserting the file name PCI_DB_Tables.sql. The procedure assumes a knowledge of SQL databases and oSQL.

An upgrade from a prior, non-PCI DSS version of EFT Server without the MSDE installed will install the PCI_DB_Tables.sql file in the EFT Server installation directory. If you installed MSDE, the script file will not be placed in the installation directory and you do not need to use the procedure in Alternative Method for Creating EFT Database Tables on SQL.

If you are installing or modifying an EFT Server that uses the MSDE database, you do not need to manually update the database tables. The database upgrade will occur the first time the Server service starts with a valid database connection and the tables described below will be added to the database.

HS-PCI Database Script

The file PCI_DB_Tables.sql contains the following script. The tables in the script are created automatically when you install MSDE for your database. You do not have to manually create them if you are using the MSDE database.

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_AdminCommands]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	CREATE TABLE [dbo].[tbl_AdminCommands] (
		[AdminCommandID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_AdminCommands PRIMARY KEY CLUSTERED,
		[Time_Stamp] [datetime] NULL ,
		[TransactionID] [numeric](18, 0) ,
		[Login] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[Data] [varchar] (7800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
	) ON [PRIMARY]
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_PCIViolations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	CREATE TABLE [dbo].[tbl_PCIViolations] (
		[PCIViolationID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_PCIViolations PRIMARY KEY CLUSTERED,
		[Time_Stamp] [datetime] NULL ,
		[ViolationID] [int] NULL ,
		[SiteName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[SettingsLevel] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[Admin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[Reason] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
	) ON [PRIMARY]
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_ServerInternalEvents]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	CREATE TABLE [dbo].[tbl_ServerInternalEvents] (
		[ServerInternalEventID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_ServerInternalEvents PRIMARY KEY CLUSTERED,
		[Time_Stamp] [datetime] NULL ,
		[SiteName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[SettingsLevel] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[EventName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[Parameters] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
	) ON [PRIMARY]
GO