Installing and Setting Up Microsoft SQL Server Express

About this task

This section provides information for administrators inexperienced with Microsoft SQL Server. The following steps assume you do not have Microsoft SQL Server installed. The following steps will guide you through downloading and configuring the free Microsoft SQL Server Express edition.

Tip: Install Microsoft SQL Server on a separate server or a Virtual Machine (VM) for best performance.

Step 1: Downloading and Installing Microsoft SQL Server Express and Microsoft SQL Server Management Studio

Before you begin

Decide where to install Microsoft SQL Server.

Important: Close Windows Services and Task Manager applications before installing.

Procedure

Download Microsoft SQL Server 2019 Express to your local machine.

Troubleshooting: If you face installation issues, update Windows and reboot. Repeat updating and rebooting until there are no more updates to install.

Note: You may need to restart Microsoft SQL Server more than once to ensure the security changes take effect during the installation process.
Run the Microsoft SQL Server Express installer .exe file on your local machine.
Select the Basic installation type to download Microsoft SQL Server Express and all related utilities.
Accept the license terms and conditions. Keep the default installation location (on the C: drive), then select Install.
The installer downloads and installs all the required Microsoft SQL Server Express packages. When everything has completed successfully, a notification page similar to the following displays:
Important: Take note of the Server value in the Connection String field (red box). You need it when installing SOTI Connect
On the installation successful notification page, select the Install SSMS button (yellow box in screenshot above) to download and install Microsoft SQL Server Management Studio.
When the Microsoft SQL Server Management Studio installation .exe file finishes downloading, run the .exe file. In the installer window, select where you want to install the Microsoft SQL Server Management Studio. Keep the default installation location on the C: drive.
Select Install.

When the installation completes, the Setup Completed screen displays.
Close all applications and restart the server or Virtual Machine (VM).

Step 2: Configuring Microsoft SQL Server to Work with SOTI Connect

Procedure

Open the Microsoft SQL Server Management Studio.
Establish a connection to the Microsoft SQL Server instance by entering the Server value in the Connection String you noted in Step 4 of Downloading and Installing Microsoft SQL Server Express and Microsoft SQL Server Management Studio section.
Select Connect.
Microsoft SQL Server connects successfully, and the instance name displays.
Right-click the instance name and select Properties.
In the left panel, select Security.
In the Server Properties panel, select SQL Server and Windows Authentication mode in the Server authentication section. Select OK to save your changes.
Decide whether you want to use a local SQL account or a Windows account for authentication. The following steps describe configuring a local account.
Expand the Security folder and right-click Logins.
Select New Login.
Enter a Login name, in this example Login name is SQLConnect.
Select SQL Server authentication, and create a password for the login.
In the left panel, under Server Roles, select the sysadmin checkbox and select OK.
Under the Logins header in the left panel, the setup automatically creates a new local login account.

Important: Use this login for your database when you install SOTI Connect.
Check NT AUTHORITY\SYSTEM has the sysadmin role assigned to it. Right-click NT AUTHORITY\SYSTEM and select Properties. Select the sysadmin checkbox under Server Roles.

Important: Use this login if installing SOTI Connect using Windows Authentication. If the database runs on a remote server, the login will not be NT Authority\System. It will be the MachineName$ (for example, domainname\computername).
Keep Microsoft SQL Server Management Studio running, then run the Microsoft SQL Server Configuration Manager utility, included as part of Microsoft SQL Server Express.
In the Microsoft SQL Server Configuration Manager utility, expand SQL Server Network Configuration and select Protocols for SQLEXPRESS.

Note: Select the correct protocol for your SOTI Connect instance if it is different than Protocols for SQLEXPRESS.
Right-click the TCP/IP protocol and enable the TCP/IP protocol.
Right-click the TCP/IP protocol again and then select Properties. The TCP/IP Properties panel displays.
In the TCP/IP Properties panel, select the IP Addresses tab, empty any IP TCP Dynamic Ports fields and set the TCP Port to 1433.
Select Apply, then select OK.
Restart your Microsoft SQL Server instance and Microsoft SQL Server Management Studio to apply your changes.

SQL Server Security Notes

The following information discusses securing access to the database and minimizing permissions.

The installation of SOTI Connect requires more database privileges than needed for regular working operations. You should reduce the level of access after the installation of Microsoft SQL Server is complete. Use the following table to reference the required access levels after the installation is complete.


Operation	Required Database Privileges
Installing or upgrading	`Sysadmin`. The SA account is a super admin with permissions for any scenario.
Normal working operation	`db_owner`.
Service running as a local system with DB installed on the same server	`Local System > NT AUTHORITY\System`. The service runs on the computer with this permission. Note: NT needs `Sysadmin` during an upgrade.
Service running as a local system with the DB installed on a remote server	`db_owner`. Using Microsoft SQL Server, add the user or the group containing the user to the `db_owner` role. Note: The database does not use the local system or `NT AUTHORITY\System`, but becomes `MachineName$` (for example, `domainname\computername`).
Service running as a service account	Make sure the user account is also in the DB as an account with the correct permissions. For example, if the user account `samplecompany/ConnectMDM` is running the service, `ConnectMDM` with the domain `samplecompany`, the account must have the required permissions.

If you used the Microsoft SQL Server authentication option (for example, when you created the SQLConnect login in the example above), you should remove the sysadmin role after completing the SOTI Connect installation. You should also confirm that the SQLConnect login has the db_owner user mappings for all the SOTI Connect databases.

Remember: You must restore the sysadmin role to the SQLConnect user before running the SOTI Connect installer again.

If you used the Windows authentication method to install SOTI Connect, you should remove the sysadmin role from the NT AUTHORITY/SYSTEM login. You should also confirm that this login has db_owner user mappings for all the SOTI Connect databases.

Microsoft SQL Server stores the SOTI Connect databases. The owner service is responsible for creating, updating, and deleting a database. When uninstalling SOTI Connect, you can retain all the databases or just the SotiConnectDeviceStateHistory and SotiConnectLoggingService databases.

Important: Due to the possible encryption of some files, you must retain the File Store database to keep access to File Store files after uninstalling SOTI Connect.

CAUTION: As the database grows bigger, user must manually shrink the database to get rid of unused space.

You should also remove the sysadmin role from the user who ran the SOTI Connect installer. You must restore the sysadmin role to that user before running the SOTI Connect installer again.