Getting started
      Back to home
      1. AllMax Knowledge Base
      2. Getting started

      Installing and Configuring Microsoft SQL Server 2022 Express

      This article will serve as a guide for clients needing to install and configure a Microsoft SQL Server instance for use with the Antero, Operator10 Wastewater, Operator10 Water, or Synexus Pretreatment applications.

       

      Pre-Installation Considerations:

      • Our software requires a SQL instance to use TCP port 1433 for default communications.
      •  Check for previously installed SQL instances on the intended database server by opening the Windows Services console (run services.msc). Sort the list alphabetically to find all the entries starting with “SQL”. 
      •  If an instance already exists, determine which port the instance is using in SQL Server Configuration Manager as described later in this guide. 
      • If the existing instance is using a port other than TCP 1433 for default communications, a new instance will have to be installed using this guide.
      •  If the existing instance is already using TCP 1433, and the version of SQL is supported by our applications (SQL Server/Express 2014 and up), you may skip the installation portion of this guide, as the instance is already configured for communication with our apps. Please move on to the section dealing with configuring the required logins and security for the SQL instance. 
      • Be sure to follow the above steps, because installing an instance with the same communications port as another instance or configuring two instances to use the same port will cause one of them to not start, or not be available for communication.

      Software Installed in this guide:

       

      SQL Installation

      1. Navigate to this link: SQL Server 2022 Express

      2. Select the button shown in the image below to download the free version of SQL Server 2022, called SQL Server 2022 Express:

      3. Run the downloaded installer:

      4. Select Custom from the installation type window:

      5. In this window, we’ll specify where the installer will place the SQL installation media it’s about to download. Leave this as the default path, which is usually C:\SQL2022. Later, we’ll choose where the actual database files will reside. Click the Install button.

       

      6. The installer will download all the files it needs to continue. Once they are downloaded, you’ll be presented with the SQL Server Installation Center window. Choose the option “New SQL Server standalone installation…”.

      7. The License Terms window will open. Accept the license terms and click Next.

      8. The Install Rules portion of the installer will run and check for common issues that could prevent the installation of SQL Server Express 2022. You will normally see a warning for the Windows Firewall as in the image below. For now, click the Next button.

      Ensure that firewalls are allowing inbound traffic over TCP port 1433 on the server, and outbound traffic over TCP port 1433 is allowed on all client workstations.

      9. The next window you’re presented with is Azure Extension for SQL Server. Our applications do not require this extension to be enabled, so uncheck the Azure Extension for SQL Server checkbox as shown below and select the Next button.

      10. Next up is the Feature Selection window. The only Feature we need selected is the Database Engine Services feature like in the image below. If the user or IT administrator would like the SQL Server application files (Instance root directory) to reside in a different location than the default of C:\Program Files\ Microsoft SQL Server\, select the path for the Instance root directory before clicking the Next button. The Instance root directory is not the path for the user’s database files, only the SQL Server application files. Setting the path to the user database files is done later in step 13.

      11. Next up is the Instance Configuration window, where we’ll choose to use the Default or Named instance. If there were no SQL instances already installed on the intended database server (these will be listed under Installed instances), select Default instance. If there are other SQL Instances installed, leave Named instance selected, and give the instance a unique name. In the screenshot below, there are multiple SQL instances installed on the server, and we’re sure to give the one we’re installing a unique name of SQLExpress2022. Click the Next button.

      12. Next up is the Server Configuration window. Our software does not require any changes in this window. Click Next.

      13. In the Database Engine Configuration window, you will find two tabs that we are interested in: Server Configuration, and Data Directories.

      • In the Server Configuration tab, ensure that the Authentication Mode is set to Windows authentication mode. Our software does not work with Mixed Mode, or with SQL Authentication (the method to login to the SQL instance that requires a SQL instance-specific username and password) and will only work with Windows authentication.
      • In the Server Configuration tab towards the bottom of the window, under “Specify SQL Server administrators” you will see that the user installing the instance will be added to the instance as a SQL Server administrator. This is a user that will have full permissions to the entire instance, and all databases inside. If additional SQL Server administrators need to be added to the instance, you would use the Add… button to add them.

      • In the Data Directories tab, if a different path for the User databases (the databases created by Operator10 or Antero) other than the default is desired, you may enter or browse for the path. Similarly, if different paths for any of the other items shown here are desired, they can be changed.


      14. Press the Next button. The installation of the new SQL instance will begin and will take a short time to complete. Once it is complete, you may be prompted to restart the PC. Go ahead and restart – the SQL instance has been installed, and the SQL installer needs to clean up some files. Once the PC has restarted, please continue to the next section.

      Configuring the SQL Instance

      We will now move on to configuring the instance using the SQL Server 2022 Configuration Manager application, which was installed along with the SQL instance.

      15. Open the Windows Start Menu, and begin typing SQL Server Configuration Manager, and then select the correct application from the displayed list:

      If SQL Server 2022 Configuration Manager does not appear in the start menu, navigate to C:\Windows\System32 in the File Explorer, and locate SQLServerManager16.msc as in the image below. This is the same application as SQL Server 2022 Configuration Manager:

       

      16. In the SQL Server Configuration Manager window, expand the SQL Server Network Configuration section (NOT the 32bit section, we will only ever install 64bit versions of SQL) as in the image below, and select the instance that was just installed. Once you have selected the SQL instance, you will see three options on the right-hand side as in the image below. Right click on TCP/IP and select Properties from the menu. The TCP/IP properties window will show.

      17. On the Protocol tab, enable TCP/IP for the instance as in the image below. If you selected Default instance during step 11, skip to step 19 after completing this step.

      18. On the IP Addresses tab, scroll all the way to the bottom. In the IPAll section’s TCP Port field, enter 1433 as in the image below, then click Apply, then OK. You will see a warning stating that the changes made will not take effect until the instance is restarted. Click OK.

      19. From the left-hand pane of the SQL Server Configuration Manager window, Select SQL Server Services. From the right-hand pane, right-click on the instance you just created (it will have the same name you gave it, or if you selected Default instance the name will be MSSQLSERVER, and its state should be “Running”), then select Restart as in the image below. Allow the instance to restart.

       At this point, the SQL instance has been fully installed and the instance’s communication settings have been configured for use with AllMax products. Next up will be installing SQL Server Management Studio, configuring the instance’s security to work with the Antero and/or Operator10 agent services, and configuring the instance’s security for the users that will be connecting via Operator10, Synexus, or Antero. 

       

      SQL Server Management Studio (SSMS) Installation

      As of the creation of this document, SQL Server Management Studio (SSMS) 20.1 is the latest stable version available. Navigate to https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16 , and scroll to the section “Download SSMS”.

       

      1. Click the link shown in the image below to begin the download of SSMS:

      2. The application will begin downloading. Once it is finished, run the downloaded installer:

      3. Click the Install button

      4. Allow the installer to continue if presented with a User Account Control prompt. SSMS will be installed.

      5. Once the installer has finished, the installation of SSMS may require you to restart your PC. Restart if prompted to do so.

      6. Once the installer has completed, and/or you have restarted the PC, SSMS is installed.

      Next up, we will configure security for the instance.

       

      Configuring Instance Security

      We recommend that SQL, Operator10, Antero, and Synexus be installed in a Domain environment. In the case that the server and client PCs are not attached to a Domain and are instead in a Workgroup, a workaround for SQL authentication will need to be made so that PCs can authenticate against the SQL instance, since SQL is designed for use in a Domain environment. 

      For Workgroup Environments ONLY:

      This workaround involves the two steps listed here. Step 2 will be done the same as if we were operating in a Domain environment and is covered later in this section.

      1.  Create local user accounts on the server/serving PC that have the EXACT same username and password as the accounts used to login to Windows on the PC running Antero/Operator10 (the client PCs). Password expiration may be an issue in the Workgroup scenario, since every time a user changes their password on their workstation (their client PC), the password of the local account on the server will need changed to match. 

      2.  Add those local accounts created in step 1 as logins to the SQL instance’s security and map the databases in SQL as described later in this section. 

      The following is demonstrated in a Domain environment. The steps will be the same in a Workgroup environment, and the only difference is we’ll be adding the user logins/groups via Active Directory instead of adding the local accounts on the server as described above in the Workgroup scenario.

      If there is only one user of the application, and you were logged in as that user when you installed the SQL instance, that user will already have the highest role and permissions to the entire SQL server instance, and every database added to it. If this is the situation, login to the instance as in step 1 below, and expand the Security folder, and the Logins folder as in step 2 below. Skip the steps related to mapping and move on to setting the NT AUTHORITY\SYSTEM roles in step 19. 

      1.  Open the SQL Server Management Studio (SSMS) application. If the instance was configured according to this guide, you will login using Windows Authentication as in the image below. Ensure the correct Server Name is selected and click the Connect button. If you see an error related to the certificate not being trusted, check the Trust Server Certificate checkbox as in the image below, and press Connect. 

      2. SSMS will connect to the SQL instance. From the Object Explorer pane on the left, expand the Security folder, then expand the Logins folder as in the image below.

      3. Right-click on the Logins folder, and select New Login… The Login-New window will open.

      4. Click the Search… button on the right-hand side of the Login-New window. The Select Users or Group window will open. Perform step 5a, 5b, or 5c below, depending on the scenario:

      5a. If you are adding an Active Directory/Domain user group that contains your current Antero, Operator10, or Synexus users (recommended), select the Object Types… button, and ensure that the Groups checkbox is checked, then select OK to close the Object Types window. Click the Locations… button, then select Entire Directory, and press OK to close the Locations window. 

      5b.  If you are adding users individually in a Workgroup environment, click the Locations… button, then select the current computer to ensure you are looking for the local users on the local server, and press OK to close the Locations window. 

      5c.  If you are adding Active Directory/Domain users individually, select the Object Types… button, and ensure that the Users checkbox is checked, then select OK to close the Object Types window. Click the Locations… button, select Entire Directory, and press OK to close the Locations window. 

      6. In the field labeled “Enter the object name to select”, enter the name of your User Group as in step 5a, or the name of the local user as in step 5b, or the name of the Active Directory user as in step 5c. 

      7.  Click the Check Names button, and if the item was found, click the OK button. If it was not found, you will be presented with a window informing you that the item was not found. In the case it was not found, ensure that the correct Location and Object Types are selected, and that the username/user group was spelled correctly. 

      8.  Once the OK button is clicked in step 7, the login name will be displayed in the Login – New window from step 3. 

       The next steps involve “mapping” the logins we created to the databases they’ll need access to. Mapping simply means setting the level of permissions each user or group requires to each database. Adding the user group or individual user’s login has already given them access to the SQL instance so that they can connect via their AllMax application, mapping gives them roles/permissions to the actual databases on a per-database level.

      AllMax Software creates a license database, and the actual “production”, or user database, and each user/group needs to be mapped to each one they need access to. For example: a client purchases Antero, configures SQL as in this guide, then installs Antero, and creates a new license (license database), and a new Antero (production/user) database.

      For users to open and use Antero, they will need mapped to the license database, and each user database they will work in, since we can add many user databases, but there will only ever be one license database per product. 

       At this stage in the guide, we have no databases to map users to, because Antero/Operator10/Synexus has not been installed yet, and no license or user databases have been created by Antero/Operator10/Synexus.

      Please see the guide on installing your application, and the guide on creating your license and user database.

      Once you have created the license database, and created the user database(s) or restored the user database backup, come back to this guide, and continue with mapping the SQL logins and setting security. 

      If you have purchased multiple AllMax Software products, each user or user group will need roles mapped to the license database for each product they will use, and roles mapped to each database they will work in.

      9. If your AllMax Software product has not yet been installed, please read the above notes and warnings, and then continue. 

      10. At this stage, you should have a license database, and at least one user database. Antero license databases are always named AllMaxAntero. Operator10 Wastewater license databases are always named WAS, with the client’s 6-digit account number appended, such as WAS000000. Operator10 Water license databases are always named WTR, with the client’s six-digit account number appended, such as WTR000000. Synexus Pretreatment license databases are always named PRE with the client’s six-digit account number appended, such as PRE000000. 

      11. User databases can have any name acceptable by SQL. Make a note of the name of the user database.

      12.  In the Login – New window, select User Mapping from the top-left pane. 

      13.  In the mapping page, you will see two panes. The top pane is a list of all databases in the SQL instance, including the AllMax user databases, AllMax license databases, some databases related to the functioning of SQL, and any other databases. The bottom pane has a list of checkboxes with each relating to a ‘role’ in the database selected in the top pane. 

      14.  First, select the license database for the application you have installed from the top pane. WASXXXXXX for an Operator10 Wastewater license database, WTRXXXXXX for an Operator10 Water license database, PREXXXXXX for a Synexus Pretreatment license database, AllMaxAntero for an Antero 7 license database, ANTXXXXXX for an Antero 6.15 and previous license database. 

      15.  In the bottom pane, ensure that the following checkboxes are checked: db_datareader, db_datawriter, and public as in the image below. 

      16.  Next, select the user database you have created/restored from the list in the top pane of the User Mapping window. 

      17.  In the bottom pane, ensure that the following checkboxes are checked: db_datareader, db_datawriter, and public as in the image below. 

      18.  Now that the database roles for the user/user group have been mapped to the license database, and the user database, you may press the OK button to apply the mappings. 

      In some situations, the users will create multiple Operator10, Synexus, or Antero databases. At those times, each user/user group will need mapped to each user database as described above. There will only ever be one license database per product, however, and mapping to the license database only needs to be done once per user/user group. 

      19.  Now that the users/user groups have a login to the SQL instance, and they have had roles mapped to the database(s) they’ll be working in, we’ll need to map the NT AUTHORITY/SYSTEM login to the same databases with the same role memberships. This is due to the Antero Service, and/or the Operator10 Agent services needing to interact with the database since they use the Local System account. 

      20. Expand the Security folder, and then expand the Logins folder.

      21. Right-click on NT AUTHORITY\SYSTEM, and select Properties… The Login Properties window for NT AUTHORITY\SYSTEM will show. 

      If NT AUTHORITY\SYSTEM isn’t in the list of logins, see the troubleshooting section at the end of this document for instructions on giving it a login, and then return here to map roles to it. 

       

      22.  From the top, left-hand pane, select User Mapping. 

      23. Repeat steps 13-18 from this section to map NT AUTHORITY\SYSTEM as having db_datareader, db_datawriter, and public roles to every license database and every user database.

       

      Conclusion

      At this point, the SQL instance is fully configured for use with Operator10 Wastewater, Operator10 Water, Synexus Pretreatment, and Antero. We hope you enjoy using your AllMax Software product!

      Troubleshooting & Tips

      In the case that the client application cannot connect to the database server, ensure that any firewall/Antivirus software is allowing traffic over TCP port 1433 - outbound on the client machines, and inbound on the server.

      If in a Workgroup environment: any time a username or password changes on a client machine, the local account on the server will need updated, and the login to SQL will need updated. They must still match exactly the username and password on the client machine.

      If NT AUTHORITY\SYSTEM does not appear in the login list, you will need to give it a login like what is described in the Configuring Security section of this document.

      • In SSMS, expand the Security folder, then expand the Logins Folder.
      • Right click on Logins, then select New Login…
      • Click the Search… button in the top-right corner of the Login - New window.
      • Ensure that you are searching for Users or Built-in security principal by clicking the Object Types… button and ensuring that you have the same checkboxes selected as in the image below. Once you have the same checkboxes selected, click the OK button:

      • Ensure that the Location that you are searching is the local computer by clicking the Locations… button and selecting the local computer name.
      • In the “Enter the object name to select” text box, type in ‘system’ (without the single quotes), and press the Check Names button.
      • Once you click Check Names, you should see SYSTEM in the “Enter the object name to select” text box. If system was not found, ensure you have the correct Location and Object Type selected. Press the OK button.
      • The Login – New window should now have NT AUTHORITY\SYSTEM in the Login name field.
      • From here, select User Mapping from the top, left-hand area of the Login – New window under Select a page.
      • Repeat steps 13-18 of the Configuring Instance Security section to give NT AUTHORITY\SYSTEM the same roles as the users, so that the Antero Service and/or the Operator10 Agent can interact with the databases as needed.