FileAudit Frequently Asked Questions
How to use SQL Express and transfer records from FileAudit’s 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 2019 + Tools
Authentication Method used: SQL Server Authentication
SQL Installation type : Local with default parameters
SQL Express 2019 Download links:
Download Microsoft® SQL Server® 2019 Express from Official Microsoft Download Center
Additional information regarding DB size limit:
MS Access Database : 2 GB
SQL Express Database : 10 GB
SQL Express Installation
-
Click on New SQL Server…
-
Check the box ‘I accept the License terms’ and click on ‘Next’
-
Click on ‘next’ except if you want to perform an update immediately of SQL Express 2019, in that case check the box ‘Use Microsoft Update to check for updates(recommended)’ and click on ‘Next’
-
Let the default parameters and click on ‘Next’
-
Click on ‘Next’
-
Click on ‘Next’
-
Select ‘Mixed Mode’, type a password for SA Account and click on ‘Next’
-
Click on ‘close’ to terminate the installation.
SQL Express Configuration
-
Click on « Install SQL Server Management Tools » to download and install the tool
-
Open « Microsoft SQL Management Studio »
-
At the logon window, type the ‘server name\instance name’, choose ‘SQL Server authentication method’, enter your credentials and click on ‘connect’
-
Make a right click on Database in the left menu and select ‘New Database’
-
In Database name field, type the name of the database (By example: FileAudit here) and press OK
-
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’ -
In ‘general section’: put a ‘login name’, select ‘SQL Server authentication’ (if you want to choose windows authentication goes to point 9) and type a ‘password’.
Optional:
Related to your IT Department policy please check or uncheck the below options- Enforce password policy
- Enforce password expiration
- User must change the password at next logon
You can also select a default database and language. Click on ‘User Mapping’ in the left pane to continue the account configuration.
-
Select ‘FileAudit’ database and select the following Database roles: db_datareader, db_datawriter, db_ddladmin.
Click on ‘OK’ to validate the new Login. -
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).
FileAudit Configuration using SQL Express Database
-
Open FileAudit Console and go to ‘settings’ section.
In “Settings configuration” menu, click on ‘Database’ in the left pane and on … (see picture below)Or you can directly open the ‘Data manager’ and click on ‘Edit’ and on …
-
Select ‘SQL Server’ in the Database connection window and validate by ‘OK’
-
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.
-
Click twice on ‘OK’ to finish the configuration.
Transfer records from MS Access Database to SQL Database
-
To transfer the data from your old MS Access database to your new SQL database, open the Database manager in FileAudit. For more information click here.
-
Executable path: "C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\DTSWizard.exe"
Just click on ‘Next’ once the wizard is launched. -
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’ -
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’ -
Select the ‘copy data from one or more tables or views’ and click on ‘Next’
-
Select ‘FA_Events’ Table and click on ‘Edit mappings’
-
In ‘Column Mappings’ windows, search ‘ID’ in ‘Source’ column and select ‘Ignore’ in the ‘Destination’ drop down list.
Validate by clicking on ‘OK’ Button -
Click on ‘Next’
-
Click on ‘Next’
-
Click on ‘Next’
-
Click on ‘Finish’ to launch the data import.
-
Click on ‘Close’ to finish the import process.