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.
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 theSysadmin
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 becomeMachineName$
(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 domainsamplecompany
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
-
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.
- Run the SQL Server installer .exe file on your local machine.
-
Select the Basic installation type to download SQL
Server and all related utilities.
-
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:
-
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.
- On the installation successful notification page, click the Install SSMS button to begin the download and installation of SQL Server Management Studio.
- 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.
-
Click Install.
Once installation is complete, you will see the Setup Completed screen
- Close all of your applications and restart your machine.
Configuring SQL Server to Work with SOTI Connect
Procedure
- Open the SQL Server Management Studio.
- 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.
-
Click Connect.
When SQL connects successfully, you will see the instance name in the list.
-
Change the server authentication to SQL Server and Windows
Authentication mode. Right click the instance name and select
Properties.
- In the left pane, click Security.
-
Select SQL Server and Windows Authentication mode in
the Server authentication section. Click
OK to save your changes.
- Determine if you want to use a local SQL account or a Windows account for authentication. The following instructions reference a local account.
-
Expand the Security directory, right-click
Logins. Click New
Login.
- Enter a Login name, then select SQL Server authentication and create a suitably complex password for the login.
-
In the left pane, click Server Roles, then select
the sysadmin checkbox and click
OK.
You should now be able to see a new login account under Logins header on the left panel.Important: Use this login for your database when you install SOTI Connect.
-
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 beMachineName$
(for example,domainname\computername
). - Keep SQL Server Management Studio running, then run the SQL Server Configuration Manager utility, which is included as part of SQL Express.
-
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).
-
Enable the TCP/IP protocol by right-clicking it and clicking
Properties.
-
Click the IP Addresses tab, empty any IP TCP
Dynamic Ports fields and set the IPAII
TCP Port to
1433
. - Click Apply, then click OK.
- 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.