Installing and Setting Up SQL Server

About this task

SQL Server is a sophisticated application, and there are many different ways to install, configure, administer, and secure it. If you are an experienced database administrator, you might have your own preferences and requirements for using SQL Server. The instructions below are intended for those with limited experience so they can get up and running quickly. The following steps assume you do not have an SQL Server instance installed, and will guide you through downloading and configuring the free SQL Server Express edition.

Note: We recommend that you install the databases on a separate machine (server or VM).

During SOTI Connect installation, more database privileges are required than during normal operation of SOTI Connect. This means you can reduce the level of access after installation is complete:

  • Normal working operations: db_owner required
  • During new install or upgrade of SOTI Connect: Sysadmin is required. For the SA account, this will be your normal super admin that will have all needed permissions for any scenario.
  • Service running as local system and DB is installed on the same server: in this case the service is run by the computer, so the permissions required will be Local System > NT AUTHORITY\System so NT will need the Sysadmin permissions during an upgrade.
  • Service running as local system and the DB is installed on a remote server: in this scenario, the DB will not be using the local system or NT AUTHORITY\System but instead it will become MachineName$ (for example, domainname\computername). In SQL you will add the group/ domain itself in the DB with permissions.
  • Service running as a service account: in this scenario you will need to ensure the user account is also in the DB as an account with the correct permissions. For example, if user account samplecompany/ConnectMDM is running the service, ConnectMDM with domain samplecompany must be added with the needed permissions.

Downloading and Installing SQL Server and SQL Server Management Studio

Before you begin

  • Decide where you will install SQL Server. We recommend that you install the databases on a separate machine (server or VM).
  • Make note of the connection string as it appears in the installation process. You will need this later when you are installing SOTI Connect.

Procedure

  1. Download Microsoft SQL Server 2019 Express to your local machine.
    If you encounter installation issues, update Windows and reboot. Repeat updating and rebooting until there are no more updates to install.
    Note: You might have to restart your SQL Server multiple times throughout this installation process so that security changes you make to your database setup can take effect.
  2. Run the SQL Server installer .exe file on your local machine.
  3. Select the Basic installation type to download SQL Server and all related utilities.

    SQL Server 2019 Express Edition Basic installation selection

  4. Accept the license terms and conditions. Keep the default installation location on the C: drive, then click Install.
    The installer will download and install all of the required SQL Server packages. When everything has been installed sucessfully, you will see a notification page that looks like the following:

    SQL Server successful installation notification page

  5. Make a note of the server address in the Connection String field. You will need to refer to this when you are installing SOTI Connect.

    Example Connection String field contents

  6. On the installation successful notification page, click the Install SSMS button to begin the download and installation of SQL Server Management Studio.
  7. When the SSMS installation .exe file finishes downloading, run the .exe. The installer window allows you to select where you want to install the SQL Server Management Studio. Keep the installation location on your machine's C: drive.
  8. Click Install.

    SQL Server Management Studio main installation page

    Once installation is complete, you will see the Setup Completed screen
  9. Close all of your applications and restart your machine.

Configuring SQL Server to Work with SOTI Connect

Procedure

  1. Open the SQL Server Management Studio.
  2. Establish a connection to the SQL Server instance by entering the server address you previously noted from the installation succesful notification page's Connection String field.
  3. Click Connect.

    SQL Server Connect to Server dialog box

    When SQL connects successfully, you will see the instance name in the list.

    SQL Server instance appearing in Object Explorer list

  4. Change the server authentication to SQL Server and Windows Authentication mode. Right click the instance name and select Properties.

    SQL instance right-click menu with Properties selection highlighted

  5. In the left pane, click Security.
  6. Select SQL Server and Windows Authentication mode in the Server authentication section. Click OK to save your changes.

    SQL Server properties screen with authentication mode selected

  7. Determine if you want to use a local SQL account or a Windows account for authentication. The following instructions reference a local account.
  8. Expand the Security directory, right-click Logins. Click New Login.

    SQL Server New Login selected

  9. Enter a Login name, then select SQL Server authentication and create a suitably complex password for the login.
    Entering a SQL Server password
  10. In the left pane, click Server Roles, then select the sysadmin checkbox and click OK.

    Sysadmin checkbox selected in the New Login screen

    You should now be able to see a new login account under Logins header on the left panel.

    List of new SQL logins

    Important: Use this login for your database when you install SOTI Connect.
  11. Ensure NT AUTHORITY\SYSTEM has the role sysadmin assigned to it. Right-click NT AUTHORITY\SYSTEM and click Properties. Under Server Roles, verify that the sysadmin checkbox is selected.
    Note: This login will be used if SOTI Connect is installed using Windows Authentication.

    If the database is running on a remote server, the login will not be NT Authority\System. It will be MachineName$ (for example, domainname\computername).

  12. Keep SQL Server Management Studio running, then run the SQL Server Configuration Manager utility, which is included as part of SQL Express.
  13. In the SQL Server Configuration Manager utility, expand SQL Server Network Configuration and click Protocols for SQLEXPRESS (or the protocol for your SOTI Connect Instance name if it is different).

    SQL Server protocol screen

  14. Enable the TCP/IP protocol by right-clicking it and clicking Properties.

    Selection screen for TCP/IP protocol

  15. Click the IP Addresses tab, empty any IP TCP Dynamic Ports fields and set the IPAII TCP Port to 1433.

    SQL TCP/IP Properties dialog box showing IPAII settings

  16. Click Apply, then click OK.
  17. Restart your SQL Server instance and SQL Server Management Studio to apply all of the changes you made.

Results

SQL Server Security Notes

The above steps should get you up and running with SQL Server quickly. Keep reading for some additional information about securing access to the database and keeping permissions to the minimum.

During SOTI Connect installation, more database privileges are required than during normal operation of SOTI Connect. This means you can reduce the level of access after installation is complete.

If you used the SQL Server authentication option (that is, using the SotiConnect login in the example above), then you can remove the sysadmin role after installation of SOTI Connect is complete. You should also confirm that the SotiConnect login has db_owner user mappings for all the SOTI Connect databases.

You will need to restore the sysadmin role to the SotiConnect user before running the SOTI Connect installer again.

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

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