Zinform

Installing SQL and Zinform

Revision as of 21:31, 10 October 2024 by Andrew (talk | contribs)

Installation of SQL

These instructions are for new installations of SQL. Existing SQL installations for emPOWER or Zinform do not require any changes.

SQL Server can be downloaded from Microsoft at: https://www.microsoft.com/en-us/sqlserver/sql-server-downloads.  SQL 2019 is recommended as this is compatible with more servers/PCs than later versions.

If installing the Express Edition:

  • Installing using the Basic option is more straight forward but doesn’t give you the chance to  specify items such as the System Administrator's password.
  • The recommendation is to install using the Custom option with the following options:
    • "Express Core" package
    • Include SQL Server product updates
    • There will be a warning regarding Windows Firewall and see notes below regarding this.  You can drilldown to the link to review the warning information.
    • Give the instance a name e.g. "SQLEXPRESS"
      • For Zinform Accounts the Server Name will be [Domain]\SQLEXPRESS, e.g. Exodesk\SQLEXPRESS
    • Use the standard Collation order
    • Latin1_General_CI_AS
    • Mixed Mode (SQL Server authentication and Windows authentication)
    • Enter a strong password for your "sa" (System Administrator), and make sure you record this for future use.  It is important you know where this is recorded.


Once complete you should install SQL Server Management Studio (SSMS), this will be required for backups and other database maintenance functions.


Configuration of Windows Server (on AD Server)

Create a Zinform Security Group on the AD Server and assign to it the users that will be using Zinform.

The Zinform Group allows you to easily give Users who require it access to all Zinform databases – rather than needing to assign individual Users to the database.


To add the Zinform Security Group if running Windows 10

  1. Go to the Start menu, click Run and run lusrmgr.msc
    1. The Local Users and Groups console should open
  2. Right-click Groups and click New Group…
  3. In the New Group wizard enter details as follows:
    1. Group name as Zinform
    2. Add Users under Members
  4. Click OK.

Once added you can easily add or remove Users by drilling down to the Zinform group.


To add the Zinform Security Group for Windows versions

  1. Run Server Manager
  2. Open Tools and click Active Directory Users and Computers
  3. Expand your Domain and click on Users
  4. From the Action menu, point to New and click Group
  5. In the New Object wizard enter details as follows:
    1. Group name as Zinform
    2. Group scope as Global
    3. Group type as Security
  6. Click OK.


Assign users to the Zinform group

Then add the users that to the group who have permission to open Zinform databases.

  1. Run Server Manager
  2. Open Tools and click Active Directory Users and Computers
  3. Expand you Domain and click on Users
  4. Right click the Zinform group and click Properties
  5. Click on the Members tab
  6. Click Add and add the Users required


SQL Server Configuration

SQL Server Security Authentication mode must be setup as "SQL Server and Windows Authentication mode", sometimes called "Mixed Mode". This is not recommended by some documentation, but it is needed to allow Zinform to automatically create and configure databases.

  1. Open SQL Server Management Studio
  2. Connect to your Database Engine
  3. Expand your Database Engine
  4. Right-click on your Database Engine, and click Properties
  5. Select the Security page
    SQL Database Security page
  6. Under Server Authentication, ensure that SQL Server and Windows Authentication mode is checked
  7. Click OK


System Administrator Login

As SQL Server is now configured in SQL Server and Windows authentication mode the sa or system administrator login should be properly password protected.

To assign a new password to sa

  1. Open SQL Server Management Studio
  2. Connect to your Database Engine
  3. Expand your Database Engine
  4. Expand Security, Logins
  5. Right-click on your sa Login and click Properties
  6. Under SQL Server authentication update the password


SQL Server Network Protocols

You need to ensure SQL Server protocols are correctly configured

Enable TCP/IP protocol

  1. Run SQL Server Configuration Manager
  2. Expand SQL Server Network Configuration
  3. Enable TCP/IP if disabled
    1. Right-click TCP/IP and click Enable
    2. You may be required to restart your computer at this time
  4. Right click TCP/IP and click Properties
  5. Select the IP Addresses tab
  6. Scroll to IPAll section and update the TCP Port to 1433
    IPAII section
  7. Click OK
  8. Restart SQL Server so changes take effect


Firewall Configuration

Next you need to ensure the firewall on the Server allows connections via the port specified previously.

Firewalls help prevent unauthorized access to computer resources. If a firewall is turned on but not correctly configured, attempts to connect to SQL Server might be blocked.

Opening a port in Windows firewall

  1. From the Start menu, click Run and run wf.msc
  2. In Windows Firewall with Advanced Security, in the left pane, select Inbound Rules.
  3. In the Actions menu, click New Rule…
  4. In the Rule Type page, select Port, and then click Next.
  5. In the Protocol and Ports page, select TCP. In Specific local ports, enter the port number of the instance of the Database Engine, 1433 for the default instance (as defined previously).
  6. Click Next.
  7. In the Action page, check Allow the connection, and then click Next.
  8. In the Profile dialog page, select the profiles that describe the computer connection environment when you want to connect to the Database Engine, and then click Next
  9. In the Name page, type a name and description for this rule
  10. Click Finish


Model database

Within SQL Server there are several system databases, including the master, model, msdb and tempdb databases.

The model database is used as the template for all databases created. When a database is created, the database is created by copying in the contents of the model database.

This means all the base settings of a new database are copied from the model database.  The model database should be correctly setup before to doing anything else.

To check Model database options

  1. Open SQL Server Management Studio
  2. Connect to your Database Engine
  3. Expand Server, Databases, System Databases
  4. Right-click the model database and click Properties
  5. Select the Options page
  6. We suggest you configure options as follows:
    Model Database Properties
  7. Ensure the following options are set:
    1. Collation is either Latin1_General_CI_AS or SQL_Latin1_General_CI_AS
      1. Recovery Model is Simple (Note: Regular backups of the database must be setup so the database can be recovered if required).
    2. Auto Create Statistics is True
    3. Auto Shrink is True
    4. Auto Update Statistics is True
    5. ANSI NULL Default is False
Retrieved from ‘https://wiki.zinform.co.nz/index.php?title=Installing_SQL_and_Zinform&oldid=79’