UserLock Documentation
UserLock Documentation

UserLock Frequently Asked Questions

How to use SQL Express and transfer records from the default MS Access DB

In the below process we will see how to install / configure SQL Express and how to configure UserLock 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

  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 immediately an update of SQL Express 2019, 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’

    Mixed Mode

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

    Terminate the installation

SQL Express Configuration

  1. Click on « Install SQL Server Management Tools » to download and install the tool

    SQL Server Management

  2. Open « Microsoft SQL Management Studio »

    Connect to Server

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

    New Database

  4. Create UserLock DataBase

    New Database

  5. In Database name field, type the name of the database (By example: UserLock here) and click on 'OK'

    New Database

  6. 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

  7. In ‘general section’: put a ‘login name’, select ‘SQL Server authentication’ 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.

    Login

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

UserLock Configuration using SQL Express Database

  1. On UserLock server, open UserLock console. On the menu "Server properties" go to Database section
    Go to the DataBase tab and click on the "…" button to modify the connection string
    Select SQL Server and press OK

    Database connection

  2. Type you SQL Server Name\Instance, select SQL Server Authentication, type the credentials of the user previously created, select the Database created in SQL Express and click on ‘test’ button to check the connection string.
    Validate the connection string by clicking twice on ‘OK’ button

    SQL Server Properties

  3. Click on ‘create database tables’ and validate by ‘Yes’

    Create Databse Tables

  4. A message will be displayed to confirm you that the tables have been created. Click on ‘OK’ on the right pane to terminate UserLock configuration.

    Tables created

Transfer 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):

    New SQL Server

  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 to SQL Server

  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\UserLock\Database\UserLock.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 UserLock 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 ‘UserLock’ database and click ‘Next’

    Choose a Destination

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

    Copy data from one or more tables or views

  6. Select ‘UserLogonEvents’ 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. Repeat the above actions for the "UserStatus" and "AdminActionResults" tables (be careful not to ignore the id of the "AdminActions" table), select the four tables and click "Next"

  9. Click on ‘Finish’

    Column Mappings

  10. Click on ‘Close’ to finish the data import

    Complete the Wizard