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.

Procedure

  1. In a web browser, go to https://www.microsoft.com/en-ca/sql-server/sql-server-editions-express.
  2. Download Microsoft SQL Server 2017 Express to your local machine and install it. Use the default instance name SQLEXPRESS.
    If you encounter installation issues, update Windows and reboot. Repeat updating and rebooting until there are no more updates to install.
  3. Install SQL Management Server Studio (SMSS) and open it.
  4. Connect to the recently installed SQL Server using Windows Authentication.
  5. Right-click on the server name and select Properties.
  6. In the left pane, click Security.
  7. Under Server Authentication, select SQL Server and Windows Authentication mode.
  8. Go to Security > Logins and create a new login named SotiConnect.
    1. Select SQL Server authentication and give it a password.
    2. Under Server roles, add the sysadmin role.

    This login can be used during installation of SOTI Connect when using SQL Server authentication.

  9. Run the SQL Configuration Manager which comes with the SQL Server Express installation.
  10. Go to SQL Server Network Configuration for your SQL Server Express instance.
  11. Click on Protocols for SQLEXPRESS.
  12. Make sure TCP/IP is enabled.
  13. Double-click TCP/IP, switch to the IP Addresses tab, scroll down to IPAll, and set your TCP port to 1433. Make sure TCP Dynamic Ports is empty.

    We recommend that you enable Named Pipes.

  14. Restart your SQLExpress service instance from Windows Services.
  15. In SSMS, go to Security > Logins and make sure a login named NT AUTHORITY\SYSTEM exists. Create it if necessary, and give it the sysadmin server role.

    This login will be used if SOTI Connect is installed using Windows authentication.

    Note that the current user running the SOTI Connect installer must also have the sysadmin role within SQL Server if Windows authentication is being used for the SQL Server connection.

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.