Elyse SQL SERVER AND DATABASE SETUP

OVERVIEW

This document covers the complete SQL Server and database setup for Elyse. The minimum required edition is SQL Server Express (free); any higher edition (Standard, Enterprise, Developer) will also work.

Prerequisites from previous steps (SERVER deployments):

WORKGROUP deployments: The AD prerequisites above do not apply. For WORKGROUP (single-user, no domain) deployments, skip the AD-related steps (Steps 7a–7b and 7d) and instead follow the SQL login instructions in Backend Installation (Workgroup) — SQL Server Login.

After completing this document:

PREREQUISITES

STEP 1: INSTALL SQL SERVER

Elyse requires SQL Server 2022 or later. The minimum edition is SQL Server Express (free); any higher edition (Standard, Enterprise, Developer) will also work. The instructions below use Express as the example.

Other editions: If you are using SQL Server Developer, Standard, or Enterprise, the installer screens are similar but may appear in a slightly different order or include additional options. The key settings (Feature Selection, Instance Configuration, Service Accounts, FILESTREAM, and Security) are the same across all editions.

If the target server does not have internet access, download the Express Advanced media on a machine that does and transfer it to the server (see Domain in a Box for details on offline installation).

Run the installer:

  1. Locate the downloaded SQL Server Express installer file (e.g., SQL2022-SSEI-Expr.exe or the extracted setup files). Right-click the installer file and select Run as administrator. (This is important — the installer needs elevated permissions to install system services.)
  2. On the initial screen, select Custom to access the full installation wizard. Do not select “Basic” — the Custom option gives you control over feature selection, service accounts, and FILESTREAM configuration, all of which are required.
  3. Choose a download/extraction location (e.g., C:\Temp\SQLSetup) and click Install. Wait for the media to download and extract.
  4. The SQL Server Installation Center will open automatically. Click New SQL Server stand-alone installation or add features to an existing installation.
  5. Accept the license terms and click Next.

Microsoft Update

The installer may display a “Microsoft Update” page asking whether to check for updates via Windows Update.

Install Rules

The installer runs a set of Install Rules checks (sometimes labelled “Setup Rules”). This page shows a list of checks with Pass, Warning, or Failed status. Common items include:

If any rule shows Failed, click the failed item to read the details and resolve the issue before continuing. Otherwise, click Next.

Azure Extension for SQL Server

SQL Server 2022 may display an “Azure Extension for SQL Server” page. This extension connects your SQL Server instance to Azure Arc for cloud-based monitoring and management.

Important: Unless you specifically require Azure Arc integration, uncheck the “Azure Extension for SQL Server” checkbox and click Next. This feature is not required by Elyse and is not relevant for on-premises or lab deployments. If the server has no internet access, enabling this extension will cause errors or warnings during installation.

Feature Selection

Ensure the following features are checked:

All other features can be left at their defaults (unchecked) unless you have a specific need for them.

Click Next.

Important: If Full-Text Search is not installed, the full-text search features of Elyse will not work. If you discover later that it was not installed, you will need to uninstall and reinstall SQL Server with this feature included.

Instance Configuration

Recommended: Select “Default instance” rather than a Named Instance.

Using a Default Instance means the server address is simply the hostname (e.g., ELYSE-SQL01) which aligns with the SPN registration in KCD Configuration. If you must use a Named Instance, ensure your application connects using the hostname and port (e.g., ELYSE-SQL01,1433) rather than the backslash instance name format.

Click Next.

Server Configuration (Service Accounts)

The Server Configuration page has a Service Accounts tab listing the SQL Server services and the accounts they will run under. The SQL Server Database Engine service account must be set according to your deployment type:

SERVER deployments (domain-joined): Change the SQL Server Database Engine service account to the domain service account:

Account Name: YOURDOMAIN\svc_sql
Password: (the password set in Domain Setup)

The domain service account is required for Kerberos authentication. If the SQL Server engine runs under the default NT Service account, the SPNs registered in KCD Configuration will not match and KCD will fail.

WORKGROUP deployments (single-user, no domain): Leave the SQL Server Database Engine service account as the default (NT Service\MSSQLSERVER). There is no domain service account in a WORKGROUP environment, and KCD is not used.

Leave the SQL Server Agent and any other service accounts at their defaults unless you have a specific reason to change them.

The Server Configuration page may also have a Collation tab. Leave the collation at the default (SQL_Latin1_General_CP1_CI_AS) unless your organisation requires a different collation. Click Next.

Database Engine Configuration

The Database Engine Configuration page has multiple tabs. Configure each as follows:

Server Configuration Tab

  1. Under Authentication Mode, select “Mixed Mode (SQL Server authentication and Windows authentication)”.
  2. Set a password for the sa account and record it securely.
  3. Under Specify SQL Server administrators, click “Add Current User” to grant the current Windows account administrator access to SQL Server.

FILESTREAM Tab

Select the FILESTREAM tab and check:

Ensure the following are UNCHECKED:

Important: File I/O access and remote client access must remain disabled. Otherwise the exclusive access that the database stored procedures have to the files can be bypassed.

Click Next.

Ready to Install

The installer displays a summary of all selected options. Review the summary to confirm:

Click Install and wait for the installation to complete. This may take several minutes. When the installation finishes, click Close.

STEP 2: INSTALL SQL SERVER MANAGEMENT STUDIO

SQL Server Management Studio (SSMS) is a separate application used to manage SQL Server — it lets you run queries, restore databases, and configure permissions. It is not included with SQL Server and must be downloaded and installed separately.

Option A: Online Installation (server has internet access)

If the target server has internet access, install the latest version of SSMS:

  1. On the target server, open a web browser and go to the SSMS download page (or search for “Download SQL Server Management Studio”).
  2. Click the Free Download for SQL Server Management Studio link. This downloads a small bootstrapper file called vs_SSMS.exe (approximately 4 MB). This is not the full installer — it is a lightweight launcher that downloads the remaining components during installation.
  3. Right-click vs_SSMS.exe and select Run as administrator.
  4. Follow the installation wizard. No special configuration is required — accept the defaults and click Install. The installer will download the required components from the internet automatically.
  5. When installation completes, click Close.

Option B: Offline Installation (server has no internet access)

Important: SSMS 21 and later use a Visual Studio Installer-based bootstrapper (vs_SSMS.exe) that does not reliably support offline installation. Although Microsoft documents a --layout flag for creating offline media, the resulting installer may fail silently on air-gapped servers — the “Verifying” progress bar completes but the installation never starts, and the window closes without any error message. Do not use SSMS 21 for offline installations.

For offline environments, use SSMS 19.x instead. SSMS 19 uses a traditional standalone installer (a single .exe file, approximately 700 MB) that works reliably without internet access. SSMS 19 is fully compatible with SQL Server 2022 and supports all the management tasks required by Elyse (queries, database restores, permissions configuration, etc.).

  1. On a machine with internet access, search for “Download SSMS 19” or “SSMS 19.3 download”. The download is available from the Microsoft Learn website (look for the SSMS release notes or previous versions page). Download the full standalone installer — the file will be named SSMS-Setup-ENU.exe and should be approximately 700 MB.
    Important: Verify the downloaded file is approximately 700 MB. If the file is only a few MB, you have downloaded the SSMS 21 bootstrapper (vs_SSMS.exe) by mistake — this will not work offline.
  2. The download is a single file named SSMS-Setup-ENU.exe (approximately 700 MB). This is the complete installer — no additional downloads are required.
  3. Transfer SSMS-Setup-ENU.exe to the target server using a USB drive, network share, or any other file transfer method. Copy it to a local folder on the target server (e.g., C:\Temp\).
  4. On the target server, right-click SSMS-Setup-ENU.exe and select Run as administrator.
  5. Follow the installation wizard. No special configuration is required — accept the defaults and click Install.
  6. Wait for the installation to complete. When it finishes, click Close. A restart may be required.

STEP 3: VERIFY FILESTREAM AND FULL-TEXT SEARCH

If you followed Step 1 correctly, FILESTREAM and Full-Text Search are already enabled. This step verifies that both features are configured correctly before proceeding to the database restore.

Step 3a: Verify FILESTREAM in SQL Server Configuration Manager

  1. Open SQL Server Configuration Manager. To find it: click the Start button and type SQL Server Configuration Manager. If it appears in the results, right-click it and select Run as administrator.
    If it does not appear in search: On some systems, SQL Server Configuration Manager is not listed in the Start menu. In that case: press Windows Key + R, type mmc, and press Enter. In the window that opens, click File > Add/Remove Snap-in..., find and select SQL Server Configuration Manager in the list, click Add, then click OK.
  2. In the left pane, click SQL Server Services.
  3. Right-click the SQL Server instance (e.g., “SQL Server (MSSQLSERVER)” for a Default Instance, or “SQL Server (SQLEXPRESS)” for a Named Instance) and select Properties.
  4. Select the FILESTREAM tab.
  5. Verify that “Enable FILESTREAM for Transact-SQL access” is checked. If it is not, check it now. Ensure “Enable FILESTREAM for file I/O access” remains UNCHECKED.
  6. Click OK (or Apply then OK if you made changes).
  7. Leave SQL Server Configuration Manager open. You will return to it to restart the service after the next step.

Step 3b: Verify FILESTREAM and Full-Text Search via T-SQL

  1. Open SQL Server Management Studio (SSMS). To find it: click the Start button and type SSMS or SQL Server Management Studio. Click the result to open it.
  2. The “Connect to Server” dialog will appear automatically. Enter the following:
    Server name.\ (for Default Instance) or .\SQLEXPRESS (for Named Instance)
    AuthenticationWindows Authentication
    EncryptOptional
    Trust Server CertificateChecked
  3. Click Connect.
  4. Click New Query and execute the following to verify (and ensure) FILESTREAM is enabled at the T-SQL level: 
    EXEC sp_configure 'filestream_access_level', 1;
RECONFIGURE;

    Expected result: “Configuration option 'filestream access level' changed from 1 to 1” (confirming it was already enabled). If it shows “changed from 0 to 1”, FILESTREAM was not enabled during installation and has now been enabled.

  5. Open a new query and verify Full-Text Search is installed: 
    SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled');

    Expected result: 1. If the result is 0, Full-Text Search was not installed during Step 1. You will need to uninstall and reinstall SQL Server with the “Full-Text and Semantic Extractions for Search” feature included.

Step 3c: Restart the SQL Server Service

  1. Return to SQL Server Configuration Manager.
  2. In the left pane, click SQL Server Services.
  3. Right-click the SQL Server instance and select Restart.

STEP 4: FILESTREAM DATA FOLDER (OPTIONAL)

When the database is restored in Step 6, SSMS will display a Files page showing where each database file will be restored to, including the FILESTREAM container. SSMS automatically proposes default paths based on the target server’s SQL Server data directory. You can accept the defaults or choose a custom location.

Default location: If you are happy to store the FILESTREAM data in the SQL Server default data directory (typically C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\), you can skip this step entirely — the SQL Server service account already has the necessary permissions on its own data directory.

If you want to store the FILESTREAM data on a different drive or custom location (recommended for production environments), create the parent folder now and set the appropriate permissions. You will specify this path during the database restore in Step 6.

Considerations for Choosing a Custom Location

Example custom location: D:\FILESTREAMData\Elyse_DB\

Create the Folder and Set NTFS Permissions

  1. Create the parent folder (e.g., D:\FILESTREAMData\Elyse_DB). SQL Server will create the FILESTREAM container inside this folder during the database restore.
  2. Right-click the folder and select Properties.
  3. In the Properties dialog, click the Security tab.
  4. Click the Edit... button.
  5. In the Permissions dialog, click the Add... button.
  6. In the “Select Users, Computers, Service Accounts, or Groups” dialog, type the SQL Server service account:
    • SERVER deployments: YOURDOMAIN\svc_sql (replace YOURDOMAIN with your actual domain name)
    • WORKGROUP deployments: NT Service\MSSQLSERVER
  7. Click Check Names to verify the account resolves (it will become underlined if found), then click OK.
  8. Back in the Permissions dialog, select the service account in the “Group or user names” list.
  9. In the permissions section below, check the Full control checkbox under the Allow column.
  10. Click Apply, then click OK to close the Permissions dialog.
  11. Click OK to close the Properties dialog.

STEP 5: CONFIGURE TCP/IP PROTOCOL AND PORT ASSIGNMENT

By default, SQL Server Express only accepts connections from the same machine. To allow the backend server (on a different machine) to connect, we need to enable the TCP/IP network protocol and assign a fixed port number.

Step 5a: Enable the TCP/IP Protocol

  1. Open SQL Server Configuration Manager (if it is not still open from Step 3). Right-click and select Run as administrator.
  2. In the left pane, expand SQL Server Network Configuration.
  3. Click “Protocols for MSSQLSERVER” (Default Instance) or “Protocols for SQLEXPRESS” (Named Instance).
  4. In the right pane, right-click TCP/IP and select Enable.
  5. Click OK on the warning about restarting the service.

Step 5b: Set the Port to 1433

  1. Right-click TCP/IP and select Properties.
  2. Click the IP Addresses tab.
  3. Scroll to the IPAll section at the bottom.
  4. Clear the TCP Dynamic Ports field (leave it blank).
  5. In TCP Port, enter: 1433
  6. Click OK.
Note: Also verify that for the specific IP address assigned to this server, the “Enabled” property is set to “Yes” in the IP Addresses list. Sometimes only the IPAll section is enabled by default.

Step 5c: Restart the SQL Server Service

  1. In the left pane, click SQL Server Services.
  2. Right-click the SQL Server instance and select Restart.

Step 5d: Configure the Windows Firewall

Open a Command Prompt as Administrator: click the Start button, type cmd, then right-click Command Prompt in the results and select Run as administrator. Type the following command and press Enter:

netsh advfirewall firewall add rule name="SQL Port 1433" dir=in action=allow protocol=TCP localport=1433

This allows inbound TCP connections on port 1433 from the backend server.

STEP 6: RESTORE THE DATABASE

The Elyse database contains graph tables and preconfigured data. The initial deployment is performed by restoring from a database backup file. For subsequent upgrades, use Visual Studio SQL Server Data Tools.

Note: SSMS must be run with permissions to access the folder containing the backup file. If you encounter “access denied” errors during restore, try running SSMS as Administrator (right-click the SSMS shortcut and select Run as administrator).

Step 6a: Extract the Backup File

  1. Locate the database backup package (Elyse_DB_*.zip) that you downloaded.
  2. Right-click the zip file and select Extract All...
  3. Choose a destination folder (e.g., C:\Temp\) and click Extract.
  4. Note the location of the extracted .bak file — you will need this path in the next step.

Step 6b: Restore the Database

  1. Open SSMS if it is not already open.
  2. In the “Connect to Server” dialog, set Server name to .\ (for Default Instance) or .\SQLEXPRESS (for Named Instance), set Authentication to Windows Authentication, and click Connect.
  3. In Object Explorer, right-click Databases and select “Restore Database...”
  4. In the Restore Database dialog:
    1. Select “Device:” and click the [...] button.
    2. Click Add and browse to the backup file.
    3. Select the file and click OK to load the backup contents.
  5. Switch to the Files page (Select a page panel on the left).

    The Database files grid shows three files with their original paths (from the backup) and proposed “Restore As” paths (where they will be restored on this server). SSMS automatically proposes paths based on the target server’s default data directory.

    1. Review the “Restore As” column for each file:
      • Data file (.mdf) — the main database file
      • Log file (.ldf) — the transaction log
      • FILESTREAM (File Type = FILESTREAM) — the document storage container
    2. You can accept the default paths proposed by SSMS, or change them to custom locations. If you prepared a custom FILESTREAM folder in Step 4, update the FILESTREAM “Restore As” path to point to that folder. Type the new path directly into the field, or click [...] to browse. The parent folder must already exist.
  6. Switch to the Options page (Select a page panel on the left).
    1. Check “Overwrite the existing database (WITH REPLACE)” if restoring over an existing database.
    2. Verify all file paths are correct and accessible.
  7. Click OK to start the restore.

The script CleanDatabaseRestore.sql may also be used to restore a database as an alternative to the SSMS GUI.

Step 6c: Verify the Database

  1. In the Object Explorer panel (left side of SSMS), click the + icon next to Databases to expand it. Then click the + next to Elyse_DB, then Programmability, then Stored Procedures.
  2. Scroll to: reading.usp_SEL_files_contains
  3. Right-click the stored procedure name and select Execute Stored Procedure...
  4. In the “Execute Procedure” dialog, you will see a grid with parameter names. Click in the Value column next to the first row (@containsstring) and type the word structure.
  5. Click OK.
  6. Expected result: A table of data and the message “Transaction successful”.

If you see an error about full-text indexing not being installed, you will need to uninstall and reinstall SQL Server with Full-Text Search included (see Step 1).

STEP 7: CONFIGURE SQL SERVER LOGIN AND PERMISSIONS

WORKGROUP deployments: Skip Steps 7a, 7b, and 7d. Instead, follow the SQL login instructions in Backend Installation (Workgroup) — SQL Server Login. Step 7c (Verify Application Roles) still applies.

This step creates the SQL Server login that maps to the Active Directory security group, allowing Elyse users to connect to the database.

Step 7a: Create the Windows Login for the AD Group

You can create the login using either the SSMS GUI or a T-SQL script. Both methods achieve the same result.

Option 1: Using the SSMS GUI

  1. In SSMS (still open from Step 6), ensure you are connected to the SQL Server instance.
  2. In the Object Explorer panel, expand Security (the server-level Security node, not the one under a database).
  3. Right-click Logins and select New Login...
  4. In the “Login — New” dialog:
    1. Click Search... next to the Login name field.
    2. In the “Select User or Group” dialog, click Object Types... and ensure Groups is checked. Click OK.
    3. Click Locations... and in the “From This Location” tree, select Entire Directory (the top-level node). Click OK.
    4. Type Elyse_Users in the object name field and click Check Names. It should resolve to YOURDOMAIN\Elyse_Users. Click OK.
  5. Ensure Windows authentication is selected.
  6. Click OK to create the login.

Option 2: Using T-SQL

  1. Click the New Query button in the toolbar (or press Ctrl+N). A blank query editor tab will open.
  2. Type or paste the following SQL command and press F5 (or click the Execute button) to run it: 
    CREATE LOGIN [YOURDOMAIN\Elyse_Users] FROM WINDOWS;

    Replace YOURDOMAIN with your actual domain name.

Step 7b: Create the Database User and Grant CONNECT

Now map the login to the Elyse database and grant the CONNECT permission.

Option 1: Using the SSMS GUI

  1. In Object Explorer, expand Security > Logins.
  2. Double-click the YOURDOMAIN\Elyse_Users login to open its properties.
  3. In the “Select a page” panel on the left, click User Mapping.
  4. In the “Users mapped to this login” grid, find Elyse_DB and check the box next to it.
  5. In the “Database role membership” section below, ensure only public is checked. Do not check any other roles (such as db_owner or db_datareader).
  6. Click OK.
Note: Checking the Elyse_DB box in User Mapping automatically creates the database user and grants CONNECT permission.

Option 2: Using T-SQL

  1. Click the New Query button in the toolbar (or press Ctrl+N) to open a new query editor tab. Type or paste the following SQL commands and press F5 to execute: 
    USE [Elyse_DB];
CREATE USER [YOURDOMAIN\Elyse_Users]
    FOR LOGIN [YOURDOMAIN\Elyse_Users];
GRANT CONNECT TO [YOURDOMAIN\Elyse_Users];

    Replace YOURDOMAIN with your actual domain name.

Important: Grant ONLY the CONNECT permission. Do not grant any other permissions (such as db_owner, db_datareader, or db_datawriter). All data access in Elyse is controlled through application roles (CONFIGURATOR, READER, REVIEWER, CONTROLLER, EDITOR, AUTHORISER) which are activated by the backend using sp_setapprole. Individual users have no direct data permissions.

Step 7c: Verify Application Roles and Set Passwords

The database backup includes seven application roles. Verify they exist and set their passwords.

Verify the Roles

  1. In the Object Explorer panel (left side of SSMS), click the + icon next to Databases to expand it, then expand Elyse_DB, then Security, then Roles, then Application Roles.
  2. Confirm the following roles are present:
    • CONFIGURATOR
    • READER
    • REVIEWER
    • CONTROLLER
    • EDITOR
    • AUTHORISER
    • OWNERSHIP_CHAIN

Set the Application Role Passwords

Each application role has a password that the backend uses to activate the role via sp_setapprole. The passwords set here must match the passwords later configured in the backend .env file. Set or change each role’s password as follows:

  1. In the Object Explorer panel, under Elyse_DB > Security > Roles > Application Roles, double-click the first role (e.g., CONFIGURATOR) to open its properties.
  2. In the “Application Role Properties” dialog, clear the Password field and type a new strong password. Re-enter the same password in the Confirm password field.
  3. Click OK.
  4. Record the password securely — you will need to enter it in the backend .env file during backend installation.
  5. Repeat for each of the remaining roles: READER, REVIEWER, CONTROLLER, EDITOR, and AUTHORISER.

See Backend Installation (Server) — Step 2: Configure .env or Backend Installation (Workgroup) — Step 3: Configure .env for where these passwords are entered.

Set the OWNERSHIP_CHAIN Password

Important: The OWNERSHIP_CHAIN role is used only internally by the database’s stored procedures. Its password must never be stored outside of the database and there is no need for any external process or person to know it.
  1. In the Application Roles list, double-click OWNERSHIP_CHAIN to open its properties.
  2. Clear the Password field and type a long random string. Re-enter the same string in the Confirm password field.
  3. Click OK.
  4. Do not record this password anywhere. Do not add it to the backend .env file.

Step 7d: Service Account Restrictions (SERVER Only)

Do NOT create a SQL Server login for svc_elyse_be. The SERVER backend service account (svc_elyse_be) must never connect to SQL Server directly. It must not have a SQL Server login and must not be a member of the Elyse_Users AD group. The SERVER backend connects to SQL Server exclusively through KCD impersonation of the authenticated user. If svc_elyse_be has its own SQL Server login, KCD impersonation will not be used and the security model will be bypassed.
Note: If you are also deploying a WORKGROUP READ ONLY backend, that variant uses a separate, dedicated service account (e.g., svc_elyse_ro) which does require its own SQL Server login. This is a different account from svc_elyse_be and is configured separately. See Backend Installation (Read-Only) for those instructions.

STEP 8: RESTART AFTER DATABASE INSTALLATION

If a new copy of the database is installed by restoring over an existing one, restart the backend application pool and the web server (e.g., IIS) to clear any cached connections.

NEXT STEPS

SERVER deployments:

  1. Proceed to KCD Configuration to register Service Principal Names (SPNs) and configure Kerberos Constrained Delegation.
  2. After KCD is configured, install the backend and frontend application layers.
  3. After the application layers are installed, proceed to Bootstrapping to onboard users and configure the system.

WORKGROUP deployments:

  1. Skip KCD. Proceed to Backend Installation (Workgroup).
  2. After the backend is installed, install the frontend.
  3. After both layers are installed, proceed to Bootstrapping to onboard the user and configure the system.