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 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 SQL Express 2014 + Tools package.

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…

   

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

   

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

   

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

   

5. Click on ‘Next’

   

6. Click on ‘Next’

   

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

   

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

   

SQL Express Configuration

1. Click on ‘SQL Server 2014 management studio’

   

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

   

3. Create UserLock DataBase

   

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

   

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’

   

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

   

7. 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. Make a right click on the server name and select ‘properties’.
   Go to ‘Logs’ section, select ‘other database’ and click on ‘…’
   In DataBase connection window, please select SQL Server and click ‘OK’

   

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

   

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

   

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.

   

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

   

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

   

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

   

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’

   

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

   

6. Select ‘UserLogonEvents’ Table and click on ‘Edit Mappings…’

   

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

   

8. /!\ Repeat the above actions for the ‘UserStatus’ table and click on ‘Next’

   

9. Click on ‘Next’

   

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