Installing and Setting Up Microsoft SQL Server Express

About this task

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

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

Downloading and Installing Microsoft SQL Server Express and Microsoft SQL Server Management Studio

Before you begin

Decide where to install Microsoft SQL Server.
Note the connection string as it appears in the installation process. You need it later when installing SOTI Connect.

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

Procedure

Download Microsoft SQL Server 2019 Express to your local machine.

Note: If you encounter 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 multiple times during the installation process for security changes to take effect.
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 installed successfully, a notification page similar to the following displays:
Note: 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 VM.

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 the 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, select Server Roles, select the sysadmin checkbox, and select OK.
A new local login account is created and displays under the Logins header in the left panel.

Important: Use this login for your database when you install SOTI Connect.
Ensure 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.

Note: Use this login if installing SOTI Connect using Windows Authentication. If the database runs on a remote server, the login is not be NT Authority\System. It is 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 IPAII 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 normal working operation. You should reduce the level of access after the installation of Microsoft SQL Server is complete. Use the following table as a reference to 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 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 local system with the DB installed on a remote server	`db_owner`. UsingMicrosoft 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	Ensure 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 be given the needed 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 db_owner user mappings for all the SOTI Connect databases.

Note: 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. Creating, updating, and deleting a database is associated with its owner service. When uninstalling SOTI Connect, you can either 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.

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.