Using SQL Server as the Auditing Database
Microsoft Desktop Engine MSDE is bundled with the Server as a default
database for auditing transactions. MSDE has a built-in size limit of
2GB, limiting it to about a million transactions before the database fills
up. MSDE is not supported on the Microsoft Vista operating system, performance
throttling occurs when there are more than five (5) concurrent workload
batches in progress, and MSDE does not offer OLAP / data warehousing capabilities.
Choosing SQL Server over MSDE overcomes these limitations, and provides
centralization of data for federation, redundancy, and performance. The
procedure below describes how to setup the ARM database in SQL 2005. The
process is similar for earlier versions of SQL Server.
To use SQL Server as the auditing database
Install the Server without the MSDE component, unless
you want to perform auditing on the local system for testing purposes.
Point the Server to the SQL Server of your choice.
To configure SQL Server for use with the Server
On SQL Server, launch Microsoft
SQL Server Management Studio or equivalent and provide your administrator
login credentials when prompted.
In the left pane, expand the Security
node, then click the Logins node.
Right-click the Logins
node, then click New Login. The
New Login dialog box appears.
Create a new user called gsftpuser
and click SQL Sever Authentication.
|
|
If SQL Sever Authentication
is not available as a choice, verify that SQL Server has been set up to
support Mixed-mode. |
In the Password
and Confirm password boxes, provide
a complex password consisting of an alphanumeric and symbol mix at least
8 characters long.
Select the Enforce
Password Policy check box.
Leave the Default database
as master.
In Default language,
click the down arrow to select your language, if other than English, then
click OK.
In the left pane, right-click the Databases
node, then click New Database.
In the dialog box that appears, name the database gsftpdb.
In the Owner
field, provide the login name you just created (gsftpuser).
In the Database files
table, change the Initial size
value to 10MB for the gsftpdb
logical name (first row). Leave the gsftpdb_log
row alone and click OK.
In the left pane under Databases,
click the newly created gsftpdb
database, then on the Query menu,
click New Query. A blank screen
appears in the right pane in which you can type in a SQL query.
Paste the SQL
Script below into the Query
text box. Make sure the query begins with the words if
exists and ends with a parenthesis. Include everything between
the sections labeled begin SQL query
and end SQL query.
To run the query you just entered, click Execute
on the toolbar. A message appears indicating whether the query was able
to complete successfully.
Expand Databases,
then gsftpdb, then Tables.
Verify that the database has populated correctly. (The tables defined
in the script should have been created.)
Test your connection
Create a test connection with your FTP client to the
Server and upload and download a few files.
Switch back to SQL Server and select the dbo.tbl_ProtocolCommands
table under the gsftpdb database
icon. It should return several rows with the commands issued by your FTP
client from the test connection.
You can now pull reports directory from the Server against
data audited to the SQL Server.
|
|
If you are running the Administrator you must have an
entry in the Administrator computer's DNS for the name of the SQL (or
MSDE) server, otherwise the Administrator will not be able to connect
to the SQL Server when attempting to pull reports. |
SQL Script
****BEGIN SQL QUERY***
if exists (select * from
dbo.sysobjects where id = object_id(N'dbo.tbl_Actions') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table dbo.tbl_Actions
GO
if exists (select * from
dbo.sysobjects where id = object_id(N'dbo.tbl_Authentications') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table dbo.tbl_Authentications
GO
if exists (select * from
dbo.sysobjects where id = object_id(N'dbo.tbl_Groups') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table dbo.tbl_Groups
GO
if exists (select * from
dbo.sysobjects where id = object_id(N'dbo.tbl_CustomCommands') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table dbo.tbl_CustomCommands
GO
if exists (select * from
dbo.sysobjects where id = object_id(N'dbo.tbl_EventRules') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table dbo.tbl_EventRules
GO
if exists (select * from
dbo.sysobjects where id = object_id(N'dbo.tbl_ProtocolCommands') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table dbo.tbl_ProtocolCommands
GO
if exists (select * from
dbo.sysobjects where id = object_id(N'dbo.tbl_ResultCodes') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table dbo.tbl_ResultCodes
GO
if exists (select * from
dbo.sysobjects where id = object_id(N'dbo.tbl_SocketConnections') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.tbl_SocketConnections
GO
if exists (select * from
dbo.sysobjects where id = object_id(N'dbo.tbl_Transactions') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table dbo.tbl_Transactions
GO
if exists (select * from
dbo.sysobjects where id = object_id(N'dbo.tbl_ClientOperations') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table dbo.tbl_ClientOperations
GO
CREATE TABLE dbo.tbl_Transactions
(
TransactionID numeric(18,
0) IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_Transactions PRIMARY KEY
CLUSTERED ,
ParentTransactionID numeric(18,
0) NULL REFERENCES tbl_Transactions(TransactionID),
TransactionObject varchar
(50) NOT NULL
)
GO
CREATE TABLE dbo.tbl_EventRules
(
EventID
numeric(18, 0) IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_EventRules PRIMARY
KEY CLUSTERED,
Time_stamp
datetime NOT NULL ,
SiteName
varchar (50) NULL ,
EventName
varchar (50) NULL ,
EventType
varchar (50) NULL ,
ConditionValues
varchar (1000) NULL ,
TransactionID
numeric(18, 0) NOT NULL REFERENCES tbl_Transactions(TransactionID) ON
DELETE CASCADE
)
GO
CREATE TABLE dbo.tbl_Actions
(
ActionID
numeric(18, 0) IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_Actions PRIMARY
KEY CLUSTERED,
Time_stamp
datetime NOT NULL ,
SiteName
varchar (50) NULL ,
EventName
varchar (50) NULL ,
ActionType
varchar (50) NULL ,
Parameters
varchar (1000) NULL ,
IsFailedAction
bit NULL ,
ResultID
numeric(18, 0) NOT NULL ,
EventID
numeric(18, 0) NOT NULL REFERENCES tbl_EventRules(EventID),
TransactionID
numeric(18, 0) NOT NULL REFERENCES tbl_Transactions(TransactionID) ON
DELETE CASCADE
)
GO
CREATE TABLE dbo.tbl_Authentications
(
AuthenticationID numeric(18,
0) IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_Authentications PRIMARY
KEY CLUSTERED,
Time_stamp datetime NOT
NULL ,
RemoteIP varchar (15)
NOT NULL ,
RemotePort numeric(18,
0) NULL ,
LocalIP varchar (15)
NOT NULL ,
LocalPort numeric(18,
0) NULL ,
Protocol varchar (50)
NULL ,
SiteName varchar (50)
NULL ,
UserName varchar (50)
NULL ,
PasswordHash varchar
(500) NULL ,
SettingsLevels varchar
(500) NULL ,
ResultID numeric(18,
0) NOT NULL ,
TransactionID numeric(18,
0) NOT NULL References tbl_Transactions(TransactionID) ON DELETE CASCADE
)
GO
CREATE TABLE dbo.tbl_Groups
(
GroupID numeric(18, 0)
IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_Groups PRIMARY KEY CLUSTERED,
GroupName varchar (50)
NULL ,
AuthenticationID numeric(18,
0) NOT NULL REFERENCES tbl_Authentications(AuthenticationID) ON
DELETE CASCADE
)
GO
CREATE TABLE dbo.tbl_CustomCommands
(
CustomCommandID numeric(18,
0) IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_CustomCommands PRIMARY KEY
CLUSTERED,
Time_stamp datetime NOT
NULL ,
SiteName varchar (50)
NULL ,
Command varchar (50)
NULL ,
CommandParameters varchar
(1000) NULL ,
ExecutionTime numeric(18,
0) NULL ,
ResultID numeric(18,
0) NOT NULL ,
TransactionID numeric(18,
0) NOT NULL REFERENCES tbl_Transactions(TransactionID) ON DELETE CASCADE
)
GO
CREATE TABLE dbo.tbl_ProtocolCommands
(
ProtocolCommandID numeric(18,
0) IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_ProtocolCommands PRIMARY
KEY CLUSTERED,
Time_stamp datetime NOT
NULL ,
RemoteIP varchar (15)
NULL ,
RemotePort numeric (18,0)
NULL ,
LocalIP varchar (15)
NULL ,
LocalPort numeric (18,0)
NULL ,
Protocol varchar (50)
NULL ,
SiteName varchar (50)
NULL ,
Command varchar (10)
NULL ,
CommandParameters varchar
(1000) NULL ,
FileName varchar (500)
NULL ,
VirtualFolderName varchar
(500) NULL ,
PhysicalFolderName varchar
(500) NULL ,
IsInternal numeric(18,
0) NULL ,
FileSize numeric(18,
0) NULL ,
TransferTime numeric(18,
0) NULL,
BytesTransferred numeric(18,
0) NULL ,
ResultID numeric(18,
0) NOT NULL ,
TransactionID numeric(18,
0) NOT NULL REFERENCES tbl_Transactions(TransactionID) ON DELETE CASCADE
)
GO
CREATE TABLE dbo.tbl_ResultCodes
(
ResultID numeric(18,
0) NOT NULL CONSTRAINT PK_tbl_ResultCodes PRIMARY KEY CLUSTERED,
Description varchar (100)
NULL ,
Category varchar (10)
NULL
)
GO
CREATE TABLE dbo.tbl_SocketConnections
(
SocketID numeric(18,
0) IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_SocketConnections PRIMARY
KEY CLUSTERED,
Time_stamp datetime NOT
NULL ,
RemoteIP varchar (15)
NULL ,
RemotePort numeric (18,0)
NULL ,
LocalIP varchar (15)
NULL ,
LocalPort numeric(18,
0) NULL ,
SiteName varchar (50)
NULL ,
ResultID numeric(18,
0) NOT NULL ,
TransactionID numeric(18,
0) NOT NULL REFERENCES tbl_Transactions(TransactionID) ON DELETE CASCADE
)
GO
CREATE TABLE dbo.tbl_ClientOperations
(
ClientOperationID numeric
(18, 0) IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_ClientOperations PRIMARY
KEY CLUSTERED ,
Time_stamp datetime NOT
NULL ,
Protocol varchar (50)
NULL ,
RemoteAddress varchar
(50) NULL ,
RemotePort numeric (18,
0) NULL ,
Username varchar (50)
NULL ,
RemotePath varchar (500)
NULL ,
LocalPath varchar (500)
NULL ,
Operation varchar (50)
NULL ,
BytesTransferred numeric
(18, 0) NULL ,
TransferTime numeric
(18, 0) NULL ,
ResultID numeric (18,
0) NOT NULL ,
TransactionID numeric
(18, 0) NOT NULL REFERENCES tbl_Transactions(TransactionID) ON DELETE
CASCADE
)
****END SQL QUERY***