FileAudit Documentation
FileAudit Documentation

FileAudit Frequently Asked Questions

How to use SQL Express and transfer records from FileAudit’s default database, a MS Access Database.

In the below process we will see how to install / configure SQL Express and how to configure FileAudit using SQL Express Database.
A section is also dedicated to explain in details how to transfer records from the default MS Access Database to SQL Express Database.

Note:
SQL Express version used: SQL Express 2014 + Tools
Authentication Method used: SQL Server Authentication
SQL Installation type : Local with default parameters

SQL Express 2014 Download links:
https://www.microsoft.com/en-us/download/details.aspx?id=42299

You will have several choices, related to your system please download the 32 or 64 bits version of the SQL Express 2014 + Tools

Version system

Additional information regarding DB size limit:
MS Access Database : 2 GB
SQL Express Database : 10 GB

SQL Express Installation

  1. Click on New SQL Server…

    New SQL Server

  2. Check the box ‘I accept the License terms’ and click on ‘Next’

    license terms

  3. Click on ‘next’ except if you want to perform an update immediately of SQL Express 2014, in that case check the box ‘use Microsoft Update to check for updates’ and click on ‘Next’

    Microsoft Update

  4. Let the default parameters and click on ‘Next’

    Feature Selection

  5. Click on ‘Next’

    Instance Configuration

  6. Click on ‘Next’

    Server Configuration

  7. Select ‘Mixed Mode’, type a password for SA Account and click on ‘Next’

    Databse Engine Configuration

  8. Click on ‘close’ to terminate the installation.

    Complete

SQL Express Configuration

  1. Click on ‘SQL Server 2014 management studio’

    Management

  2. At the logon window, type the ‘server name\instance name’, choose ‘SQL Server authentication method’, enter your credentials and click on ‘connect’

    Connect to Server

  3. Make a right click on Database in the left menu and select New ‘Database’

    New Database

  4. In Database name field, type the name of the database (By example: FileAudit here) and press OK

    New Database

  5. For security reasons, it is preferable to not use SA Account and to create a new one.
    Here, we will create a new account ‘SQLTEST’

    New Login

  6. In ‘general section’: put a login name and select SQL Server authentication.
    Optionally you can select the default database and language.
    Click on ‘User Mapping’ to continue the account configuration.

    New Login

  7. Select ‘FileAudit’ database and select the following Database roles: db_datareader, db_datawriter, db_ddladmin.
    Click on ‘OK’ to validate the new Login.

    Login Properties

  8. Note : (only for windows authentication)
    By default FileAudit service runs as the SYSTEM account. So if SQL Express is installed locally configure these database roles for SYSTEM. If it is on a remote server do it for the account DOMAIN\FILEAUDITSERVER$ (replace DOMAIN and FILEAUDITSERVER with corresponding names).

    Login Properties

FileAudit Configuration using SQL Express Database

  1. Open FileAudit Console and go to ‘settings’ section.
    In “Settings configuration” menu, click on ‘Database’ in the left pane and on … (see picture below)

    Setting configuration

  2. Select ‘SQL Server’ in the Database connection window and validate by ‘OK’

    Database connection

  3. In SQL Server properties window, type your ‘server name\sql instance’, select ‘SQL Server Authentication’ and enter credentials, select the database created in SQL Express and click on ‘Test’ button to check the connection string.

    SQL Server properties

  4. Click twice on ‘OK’ to finish the configuration.

    Connection database succeeded

Transfert records from MS Access Database to SQL Database

  1. To transfer the data from your old MS Access database to your new SQL database, you need to use the SQL server import tool (32 bits):

    SQL Server New

  2. Executable path: "C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\DTSWizard.exe"
    Just click on ‘Next’ once the wizard is launched.

    Welcome

  3. In ‘Data source’ drop down list, Select ‘Microsoft Access’
    In ‘File name’, type the path of MS Access DB.
    By default the path is ‘C:\ProgramData\ISDecisions\FileAudit\Database\FileAudit.mdb
    Then click on ‘Next’

    Choose a Data Source

  4. Select SQL Server Native Client in the drop down list and type your ‘server name\SQL Instance’.
    To import data in the SQL Express DB, you need to use an account with owner permissions.
    Select SQL Server Authentication, type the ‘SA Account’ credentials.
    Select in the drop down list ‘FileAudit’ database and click ‘Next’

    Choose a destination

  5. Select the ‘copy data from one or more tables or views’ and click on ‘Next’

    Copy data from one or more tables or views

  6. Select ‘FA_Events’ Table and click on ‘Edit mappings’

    Select Source Tables and Views

  7. In ‘Column Mappings’ windows, search ‘ID’ in ‘Source’ column and select ‘Ignore’ in the ‘Destination’ drop down list.
    Validate by clicking on ‘OK’ Button

    Column Mappings

  8. Click on ‘Next’

    Select Source Tables and Views

  9. Click on ‘Next’

    Review Data Type Mapping

  10. Click on ‘Next’

    Run Package

  11. Click on ‘Finish’ to launch the data import.

    Complete the Wizard

  12. Click on ‘Close’ to finish the import process.

    Execution successful