How to install and configure SQL Express

Set up Microsoft SQL Server Express and prepare it for use with UserLock.

Published May 1, 2024

Introduction

SQL Server Express is a free edition of Microsoft SQL Server. It is commonly used as a lightweight production database for UserLock when a full SQL Server is not required.

This guide walks you through installing SQL Express 2019 with default parameters and configuring it to host the UserLock database.

💡️ Before you begin, check the Database reference for supported versions and prerequisites.

Note

  • SQL Express version used: SQL Express 2019 + Tools

  • Authentication Method used: SQL Server Authentication

  • SQL Installation type : Local with default parameters

  • SQL Express database size limit: 10 GB max

Download: Microsoft SQL Server 2019 Express

Part 1 — Install SQL Express

  1. Launch the SQL Server setup and click New SQL Server installation.

  2. Accept the license terms and click Next.

  3. (Optional) To update SQL Express during installation, check Use Microsoft Update, then click Next.

  4. Leave the default parameters and proceed with Next through the wizard.

  5. When prompted for authentication mode, select Mixed Mode.

    • Define a password for the sa account.

  6. Continue with the default settings until the wizard completes, then click Close.

Part 2 — Configure SQL Express

  1. Download and install SQL Server Management Tools (SSMS).

  2. Open SQL Server Management Studio.

  3. At the logon window:

    • Enter the server name and instance (e.g. SERVERNAME\SQLEXPRESS).

    • Choose SQL Server Authentication.

    • Enter your credentials and click Connect.

  4. Create the UserLock database:

    • In the Database name field, enter e.g. UserLock.

    • Click OK.

  5. Create a dedicated SQL account (recommended, instead of using sa):

    • In Login name, type e.g. UserLockAdmin.

    • Select SQL Server authentication and assign a password.

    • Adjust password policies if required by your IT policy (enforce policy, expiration, force change at logon).

    • Optionally set a default database and language.

  6. Assign database roles:

    • In User Mapping, select the UserLock database.

    • Assign roles: db_datareader, db_datawriter, db_ddladmin.

    • Click OK to validate.

Best practices

  • Secure authentication: avoid using the sa account for day-to-day operations. Create a dedicated SQL account for UserLock with the required roles only.

  • Plan for growth: SQL Express has a 10 GB size limit. Monitor usage and consider migrating to a full SQL Server if your deployment grows.

  • Set up backups early: configure regular SQL Server backups before moving into production.

  • Restrict access: limit network access to the SQL Express instance with proper firewall and permissions.

Next steps

With SQL Express installed and configured, you can now connect UserLock to this database.

See: