Setting up the SQL Server for Audit Trail

If you have a full SQL Server, it will provide all the functionality you need without any limitations. The free version, Microsoft SQL Server Express, has a max. limit of 10 GB per database (2017 edition).

You cannot store audit trail data in any other database than a Microsoft SQL database. If the Audit Trail database and server is not set up correctly, the Audit Trail button in the Home tab of the IGSS Master will not be displayed.

The below installation guide is based on Microsoft SQL Server 2017 Express edition. The installation and setup steps, screen dumps etc. for other editions or other types of Microsoft SQL servers may differ from the ones below.

Install and set up the Microsoft SQL Server

Start by installing the SQL Server on the IGSS Server or on another PC in the IGSS network.

Microsoft SQL Server Express and other MS SQL Servers can be downloaded at www.microsoft.com.

Make sure that your Windows system fulfills the system requirements of the SQL Server Express Edition or other MS type of SQL server you plan to install.

If you're using SQL Server Express, it is recommended to create a new server name, instead of accepting the default name, SQLEXPRESS. For example, you could call it "IGSS".
- When you create a new SQL Server name, you obtain two advantages:
  - If you have other applications dependent on SQL Server Express, they will not conflict in the default server, SQLEXPRESS.
  - You have the full 4 GB of data available, as only IGSS data will be saved in this server.
  To create a new SQL server name, click the Customize button in the installation form:
  An SQL Server Setup will appear and go through the steps in the left pane. You can keep the default settings and click Next for most settings except for the following:
  - Under Instance Configuration, select Named instance and enter a new name e.g. related to IGSS.
  Make sure to pick the right name the first time and to write it correctly as it is NOT possible to rename an SQL Server instance name. You would have to uninstall and install the SQL server, or to add a new SQL server.
  - Under Database Engine Configuration, select Mixed Mode (SQL Server authentication and Windows authentication), specify a password and click Next.
  Note the reference to the system administrator (sa) account. Later you are going to use “sa” as login in SQL Server Management Studio to setup for IGSS Audit Trail.
  - When the installation is successfully completed, close the SQL Server Setup form.
Before you leave SQL Server Express Edition window, click Install SSMS for installation of SQL Server Management Studio at the same time, which is needed for the setup to IGSS Audit Trail.
Start the SQL Server 2017 Configuration Manager from your Windows Start menu. Click on SQL Server Services and make sure that the relevant SQL Server and the SQL Server Browser are running. If not, right click the item and select Start. The SQL Server Agent is not part of the SQL Express Edition and is not needed.
If it is not possible to select Start for the SQL Server Browser, you need to enable this first. Right-click the SQL Server Browser, select Properties and then the Service tab, where you click the Start mode and change it to Automatic.

If that does not work, open your Windows Control Panel, navigate to the folder Control Panel\System and Security\Administrative Tools and click the Services short cut. In the Services list, right-click the SQL Server Browser and in the General tab, make sure that the Startup type is set to Automatic.

Expand SQL Server Network Configuration, click on Protocols for your SQL server (in the example Protocols for IGSSSERVER), and make sure that the following protocols are enabled. If not, right-click the disabled items and select Enable.

No action is needed for the two SQL Native Client 11.0 Configurations as their Client Protocols do not need to be enabled.
Close the SQL server Configuration Manager.

Run the Audit Trail script

Start the Microsoft SQL Server Management Studio from the Windows start menu.
In the Connect to Server dialog, do the following:

Under Server type, select Database Engine.
Under Server name (if you have renamed the SQL server as recommended above), <Browse for more …>).

Expand Database Engine, select the server with the new name you defined and click OK.
Under Authentication, select Windows Authentication, SQL Server Authentication or one of the Active Directory options, as required. Note, that you will have to define user name and/or password for some of these options.

NB! If you defined a password for the SQL Server system administrator account under Database Engine Configuration in the SQL server setup (where you could also rename the server), you should enter the same password here. As Login, enter “sa” (= system administrator account).
Click Connect.

In the File menu, choose Open -> File.

Browse to the [IGSS installation path]\SQL folder.
Default path is normally C:\Program Files (x86)\Schneider Electric\IGSS32\V13.0\GSS\SQLscripts.
Select the script named audittraildb.sql and click Open.
Press the greenExecute! button in the toolbar. The Audit Trail script is now loaded into SQL Server Management Studio.
Under Messages, there should be 1 line saying (1 Row(s) affected). If there are more lines, an error has occurred. Troubleshoot the error and repeat this step, once fixed.
If you have an operator station which is not in the same domain / workgroup as the SQL Server, you must configure an SQL Server Authentication account. In the Object Explorer tree view, unfold the Security branch, right-click on Logins and select New Login.
Select SQL Server Authentication and enter a password. In below example an account called sql with a 6-digit password has been created.

Deselect all other options, unless required by your company’s password policy etc.

Under Default database, select AUDITTRAIL. Change default language, if desired, and click OK.
On the Server Roles page, you can select various roles e.g. Public. If you want your new user to also be system administrator for the selected default database AUDITTRAIL, select sysadmin and click OK.
Close the Microsoft SQL Server Management Studio.

Set up Audit Trail in IGSS System Configuration

In the IGSS Master, click the System Configuration button in the Design and Setup tab to open the System Configuration form.
In the left pane of the System Configuration form, select the IGSS server or single user station you want Audit Trail to record data from.
No setup is necessary on the operator station in System Configuration. The only requirement for the operator station is that it must have read/write access to the SQL Server.
On the Files tab, under Audit Trail data and Database, click the SQL Settings button.

In the Save data for ____ days field, write 0 (zero) to keep records forever, or write a specific number of days.
In the SQL Server Settings form, select the check box Write audit trail value to SQL Server and click the Database Setup button.
In the Data Link Properties dialog box, select Microsoft OLE DB Provider for SQL Server and click Next.
On the Connection tab, do the following:

Step 1: Select your SQL Server in the drop-down list (in our example IGSSSERVER).

Step 2: Set user access to Use Windows NT integrated security or Use a specific user name and password, if you're using SQL Server Authentication. Enter the user name and password and remember to select Allow saving password.

Step 3: Under Select the database on the server, select the AUDITTRAIL database.

Step 4: Click Test Connection to test that the connection is working. If successful, then click OK.
Verify that the SQL Server Settings dialog box has these settings and click OK.
Finally, click the Access Control tab.
Enable the audit trail by selecting the Keep a record of all user activity in an audit trail database check box.

Then select the activities you want to store in the audit trail database. Select the Comment required check box, if you want to force the user to enter a comment for this type of operation.

Audit Trail for IGSS systems with Dualized servers

For plants operating with more than one IGSS server (Dualized server concept) – a redundancy option to safeguard against lost production output or safety hazards arising from the absence of online surveillance data – there are different ways to store the recorded Audit trail data:

Separate SQL servers & databases: Users of IGSS version 12 or older need to have one SQL server/database per IGSS server, resulting in audit trail information being stored several places. Users of IGSS Version 13 and onwards can also choose this option.
Single, common SQL server/database for more IGSS servers: Users of IGSS version 13 can choose to use only one Audit Trail SQL server to collect data from several IGSS servers. This will provide plant operators and auditors with one common overview.

Setup SQL Servers for Dualized servers

Install the 2 (or more) different SQL servers with instance names as described above and connect each IGSS server to a different SQL server.
Install 1 SQL server/dabase by following the procedure above. During the Audit Trail setup, select the same SQL server instance name for both IGSS servers (IGSS version 13 and onwards).

User administration and Audit Trail

Audit Trail really only makes sense, if you have users logged in. This will allow you to record exactly what happened and who did it.

Therefore, we recommend that you do the following before starting the audit trail:

Define the relevant users and user groups in the User Administration module in IGSS Master’s Design and Setup tab.
Clear the check box Disable access control in the Access Control tab in System Configuration.
Make sure that users are logged in permanently, while they are using IGSS by selecting Allow permanent user login in the Access Control tab. If a user is only temporarily logged in, he will be prompted for his user name and password, every time an action is recorded in Audit Trail.