Zinform

Installing SQL and Zinform

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 other 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 your 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


Zinform Login

The Zinform login is required for general day to day use of Zinform databases. This maps directly to the Windows Zinform Security Group defined previously.

This means all Users belonging to the Zinform Security Group can access the Zinform database.

To add the Zinform Login

  1. Open SQL Server Management Studio
  2. Connect to your Database Engine
  3. Expand Security, and then click Logins
  4. Right-click on Logins and click New Login…
  5. On the General page enter details as follows:
    1. Enter Login name as [Domain]\Zinform, e.g. Exodesk\Zinform
      1. If using Search, click Object Types and ensure Groups are checked
    2. Check Windows authentication
  6. Click OK


Intersoft Login

The Intersoft Login is required so that that Zinform can have System Administrator rights to the SQL Server, for example to create a new database.

To add the Intersoft Login

  1. Open Microsoft SQL Server Management Studio.
  2. Expand your server.
  3. Expand Security.
  4. Right-click on Logins and click New Login…
  5. On the General page set Login Name as intersoft, change Authentication to SQL Server Authentication and set the password. Contact Exodesk for the password. Note: Enter the password without spaces.
  6. Uncheck Enforce password policy.
    Intersoft User properties
  7. Click on the Server Roles page. Check the sysadmin Server Role. Note the 'public' role will already be selected automatically and this cannot be unselected.
    Intersoft User Server Roles
  8. Click OK.
  9. If asked, confirm the Login password.

Upsize an Access Database to SQL

Upsizing an emPOWER Access Database

When an existing emPOWER Access database is upsized to SQL Server the emPOWER upsize facility must be used.  The upsize should be done in emPOWER before Zinform is installed.

The Intersoft user must have been created for the upsize facilty, see under Installation & Configuration of SQL.

Database Naming

SQL Server Database names are case sensitive and may contain the following characters, A through Z, a through z and in addition may include, but not start with, 0 through 9, @, $, #,  and _.

An SQL emPOWER database is prefixed with emPOWER_, eg emPOWER_DEMO.  The upsize utility will automatically add this prefix, so no need to include it in the database name.

To Upsize an existing Access database

  1. The emPOWER database must be emPOWER version 4.1 or higher.
  2. Remove any extraneous tables created by the user in the duplicated database. Failure to remove any user defined tables may cause the SQL Server upsize to fail.
  3. Open the duplicated company in emPOWER.
  4. From the Tools menu point to Database, then click SQL Server Upsize. Server Name Enter the Server Name. Note, although this field has a dropdown list it may not be populated under Windows 95, 98 or Me. Database Name Enter the name of the database. See the notes on database naming earlier. Folder Location The folder location is the folder the SQL database and related transaction file is stored on the Server. It is the location on the Server, not the local PC. It is strongly recommended this field is left blank and therefore the database is stored in the default location. Log File The log file records a log of the process, including date/time started/completed and any errors encountered.
  5. Click Upsize.
  6. emPOWER will then proceed to update the database to SQL Server.
    SQL Server Upsize
  7. Once complete a “Success” message will appear.
  8. Zinform requires the database name to be prefixed with Accounts_ so rename the database post upsizing.

WARNINGS.

  1. The success message may also warn if any non-critical messages were generated – in this case the log file will contain more information.
  2. Non-critical error messages should NOT be ignored, instead they should be referred back to Exodesk before proceeding to use the SQL Server database.


Post SQL Server Upsize

Do not ignore any non-critical error messages.

Although SQL Server should use default values from the model database when creating a new database it may pay to check various database properties before using it in emPOWER.

To check database options

  1. Open SQL Server Management Studio(SSMS).
  2. Expand the Databases.
  3. Right click the emPOWER database created, then click Properties.
  4. Check the database properties. DO NOT change any permissions.
  5. The Data Files and Transaction Log tabs should automatically grow the files.
    Database Options Fig. 1
  6. The Options tab should be ideally setup as below.
    Database Options Fig. 2

Recovery

The Recovery Model should normally be set to Simple. For more information see the section on Backups.

Auto update/create statistics

Tells SQL Server to automatically build out-of-date or missing statistics required when running queries.

Auto shrink

Tells SQL Server to include the database for automatic shrinking.

Installation of Zinform 5

  1. Zinform requires MS Office 32-bit to be installed.  If Office 64-bit is already installed this needs to be uninstalled and replaced with 32-bit.
    1. Uninstall the following (note this may not be all that need to be uninstalled)
      MS Office 64-bit uninstall items
  2. If the emPOWER database is Access then it must be upsized to SQL.  This should be done via emPOWER before you start installing Zinform.  See instructions for upsizing in the section Upsizing an Access Database to SQL in this document.
  3. If emPOWER is installed, it must be uninstalled first as the two cannot co-exist.
  4. If there is an existing SQL emPOWER database this will need to be renamed.  emPOWER databases are prefixed with emPOWER_, e.g. emPOWER_Demo.  This needs the prefix to be renamed to have a prefix of Accounts_, e.g. Accounts_DEMO.
  5. To install, run the Zinform Accounts 5 installer, run it as Administrator if you can.
  6. Leave the Create a desktop ticked and Click Next, and then Install.
    Create Desktop Shortcut
  7. The first time you open the database you will have to select the licence, the software will present a screen like the following.  Browse to the folder where the licence is held, usually in the emData folder.  The licence file is normally called power.lic but may be a variation on that.
    Zinform 5 Licence
  8. To open the database the first time, use File/Open Company
    1. Select the database Type
      Open Database fig. 1
    2. Enter the Server Name and select the database from the Database Name dropdown and click Open
      Open Database fig. 2

Installation of Zinform 6

Once Zinform Accounts v5 has been installed go ahead and install v6.

  1. Open SQL Server Management Studio and add a new user ZinformDB, this requires a password Exodesk will provide.  Use the settings as follows.
    ZinformDB user fig. 1
    ZinformDB user fig. 2
    ZinformDB user fig. 3
  2. Install Zinform Accounts 6.

First time running

  • Upon first run it will ask you if you want to set up a new connection.
  • You will get the following:
    Setup Connection fig. 1
  • Click Yes and you will get the following.
    Setup Connection fig. 2
  • Enter the SQL Server Name and click refresh to see the database. Check “Trusted Windows Connection” and then click Open Organisation.
  • It should then present you with a licence screen.
  • Search for the licence in the emData folder
Retrieved from ‘https://wiki.zinform.co.nz/index.php?title=Installing_SQL_and_Zinform&oldid=103’