Admin

Database Setup

    Please note that the intended audience of this section of the Historion User Manual includes;

IT Department staff involved in networks and SQL database administration

Power Users with SQL Server and Database training and experience

Undertaking the steps described in this section will require

Correctly functioning Windows Server and SQL Server environments

Appropriate administrative permissions, group membership etc. of the above

Please further note that for the 5.x and 6.x genre of Historion versions Historion database scripts and backup files are only available on request from Historion support at: www.historion.com.au\contact-us 

Typical SQL Setup Process

The SQL Database Setup process often requires tailoring to a specific IT Department’s requirements.

The usual process used for database setup for Historion trials or production deployment is described in this section of the Historion User Manual and is based on the following steps further detailed below;

1.Decide whether Active Directory (integrated) or SQL (direct) Security will be used

2.Establish initial (founding) Historion Administrator(s)

3.Provide Historion Support with required SQL Server version details

4.Either a database creation script or SQL backup for restore will be provided

5.Implement the Historion SQL Database on the required SQL Server

6.Adjust security settings for SQL database

7.Implement Client Connections

1. Decide whether Active Directory (integrated) or SQL (direct) Security will be used

This is a decision that a local IT Department would make. The most common implementation mode used by Historion customers is Active Directory (integrated) security.

The Windows Active Directory is a security architecture option for Windows Server based networks. Active Directory security provides central authentication and authorization services for Windows based computers. It also enables Network Administrators to assign policies, deploy software, and apply critical updates within an organization. When a user attempts to log on to a Windows computer that is on a Windows Active Directory Domain.

With Active Directory security Windows automatically attempts to verify a user’s password with the Active Directory (which resides on a separate central authentication server). SQL Server databases can leverage an existing Active Directory security regime in a login mode known as integrated authentication. If Active Directory is used;

A single password to the network (the Windows logon) is sufficient to access both the workstation and Historion, no second password is needed to run the application.

Access to the Historion database is governed by membership of an Active Directory Group that is managed by the IT Department.

SQL Security or ‘direct” security involves logging into the database itself as a process independent of logging in to the Windows workstation. If SQL Security is used;

A separate logon, in addition to logging into Windows will be required, with a separate SQL database username and password.

Access to the Historion database will be manually maintained by an SQL server database administrator user in addition to normal Windows network access.

 Active Directory (integrated) security is more convenient for Help Desk workers to manage. When a new Historion User required access the Help Desk simply adds the new worker’s account to the Historion Group and the Historion Admin user (the main RSO) adds the new User details in Historion. With Active Directory a separate SQL account setup is not needed.

2. Establish initial (founding) Historion Administrator(s)

We recommend Historion customers have two Historion Administrator Users. One is for contingency.  An IT Department individual is a suggestion for a second or fall back Historion Administrator.

Founding Historion Administrators are added to Historion at the SQL Server Database Table level. This can be done by Historion Support and included in the Historion database create script or backup file for restoring on SQL Server, or the founding Administrators can be added to the required Historion SQL Server Database Tables after the database is setup by local IT Support.

If Historion Support is to include founding Administrators in the script or backup that creates the Historion Database then we must be sent the full name and Windows User ID of each Administrator.

After a founding Historion Administrator has been added, that User can log in with their Administrator permissions and add other users to Historion using purpose built screens and prompts in the software.

The process below is required to establish (or re-establish) a founding Historion Administrator if there are no Historion Users added to the database yet, or no user accounts with Administrator permissions.

After the required Historion SQL Database has been installed;

1.Setup security as described under Adjust security settings for Historion SQL database

2.Open the Historion Database table tblUser

3.Add a new entry for the required Historion Administrator Account with values as shown below;

4.Open the Historion Database table tblHxSetup

5.Add a new entry for the required Historion Administrator Account with values as shown below;

Once Historion Founding Administrators are added and Workstation Setup completed other users can be added using the normal Historion User Administration process.

3. Provide Historion Support with required SQL Server details

SQL Server Version

If Historion Support are providing either an SQL database creation script or an SQL database backup file for restoration, we will need to know the version of SQL Server that the new Historion Database will be hosted on.

The version 5.x and 6.x genres of Historion have been tested and are deployed by our customers on;

Current service packs and update releases (32-bit and 64-bit versions) as at time of publication of;

SQL Server 2008 R2

SQL Server 2012

SQL Server 2014

SQL Server 2016

Historion Support is constantly testing Historion on new and emerging SQL Server Versions, Service Packs, patch releases and updates. We recommend ensuring the latest service packs are applied to SQL Server. More information is available on the Microsoft® SQL Server web site at www.microsoft.com\sql

Cloud Hosting if Required

The Historion Database can be hosted in the cloud, provided port 1433 is opened on the local router for outgoing internet traffic to the cloud host and database. A future release will provide an additional web-based service so that the Internet port 80 can be used instead. Historion’s parent company Cybermynd Pty Ltd is a Microsoft® Azure Cloud Agent and can provide administrative services accordingly.

In Australia the Australian Signals Directorate (ASD) has recognised Microsoft Azure as a certified and Government approved Cloud Computing Provider. For further details refer to the ASD article at;

www.asd.gov.au/infosec/irap/certified_clouds

Microsoft® Azure is available on the Eastern side of Australia in secure data centres in Sydney and Melbourne. Alternative cloud arrangements are possible, please contact Historion Support for details;

www.historion.com.au\contact-us

4. Either a database creation script or SQL backup for restore will be provided

Starting Historion Database Name

Historion Support and Local IT Support will need to know the starting database name.

The name of the database is not limited to any specific name, other than SQL Server Database naming conventions or Local It Department standards. Further, Historion licencing does not restrict the number of SQL Databases the organisation can elect to use.

We recommend at least three SQL databases be considered for each Historion customer deployment;

Examples names:

Sizing and Settings Requirements

Depending on implementation size and complexity we may also seek to confirm database starting requirements and any specialized configuration requirements including but not limited to;

Starting database size

Starting log size

Any other preferred local settings if specified

  Please note that for the 5.x and 6.x genre of Historion versions Historion database creation scripts and backup files are only available on request from Historion support at: www.historion.com.au\contact-us

5. Implement the Historion SQL Database on the required SQL Server

This chapter of the Historion User Manual assumes that an SQL Server environment is already in place and that access to skills and support required to create or restore SQL databases are available.

Specific details relative to Active Directory Group SQL accounts and permissions are provide in the following section 6 of this chapter for assisting IT Departments with this aspect of deployment.

Express editions for Microsoft SQL Server can be downloaded from the Microsoft web site for free. Historion supports Express editions of SQL Server however there are limitations to SQL Server Express editions that implementers should be aware of.

Limitations differ according to the version of Microsoft SQL Express used. Examples (this information is subject to change) can include;

×Maximum memory utilized by SQL Server Database Engine is 1GB

×Maximum size of the each relational database is 10 GB.

×No SQL Profiler

×Dedicated admin connection under trace flag.

×Single CPU (prior to 2008 R2 Express)

×Depending on SQL Express versions, setup and operating system there can be limitations on numbers of user connections that can be active concurrently

Microsoft SQL Server Express downloads are available at:

www.microsoft.com/en-us/sql-server/sql-server-downloads

A good comparison of SQL Express Editions and the limitations is available at:

www.fmsinc.com/microsoftaccess/sqlserverupsizing/express/index.html

6. Adjust security settings for Historion SQL database

In order to operate Historion, users must have EXECUTE permissions on Historion’s SQL Stored Procedures (SPROCS) and SELECT permissions on its Tables and Views. Once this is provided there is an application-based layer of security that regulates access to various software features within the Historion application itself.

The require SQL permissions are met by either establishing the user as a database owner (dbo – in most settings this is too high a level of permission and not appropriate) or by assigning the user membership of the SQL database role Historion_Role. The user’s SQL login can be assigned directly to the Historion_Role or an Active Directory Group containing required users can be assigned instead.

The following example provides a basic overview of assigning access to the Historion_Role via an Active Directory Group, in a small Active Directory setting. This is a simple example only to highlight the concept of Group membership minimally achieving the security permissions assignment required to operate Historion in an Active Directory setting.

The example does not recommend a specific Active Directory setup or configuration type nor does it address the practice of creating domain local resource groups and catalogue global user groups and binding the two groups in Active Directory per Microsoft best practices and industry adopted methods.

Active Directory Group

If Active Directory (integrated) security is used a Group must be created for the Historion Users that will be using the software. The name of the Group and the name of the Historion database are not restricted however the following SQL security steps occur. In Windows Server Active Directory Management a Group must be created and Users assigned. A basic example is shown below;

Active Directory Group Login

If Active Directory (integrated) security will be used then an SQL Login for the Active Directory Group containing the intended Historion Users must be created. Create a new Login by selecting the Group;

  The Search Object Types to find must include Groups otherwise only Users will be selectable.

Select [OK] and confirm that the new Login for the required Active Directory Group was created;

The new Login should appear now appear in the Logins list preceded by the Active Directory Domain;

The new Login icon should appear as a Group icon. Open the new Login from the list again then;

Select User Mapping from the top left panel then;

Map the Historion database to the new Active Directory Group Login in the top right panel

Select the Historion_Role membership in the lower right panel then select [OK]

Confirmation of Active Directory Group Addition to Database Security

If the preceding steps have been done correctly the applicable security nodes in SQL Management Studio emphasized below should have values corresponding to the name you nominated for your Historion database and the name used for your Historion Active Directory users Group;

The next step is to proceed to and complete Historion_Role Permission Assignment.

SQL (Direct) Security Login

The following steps are required to add a new Historion User without Active Directory Group membership and only use SQL (direct) security. Add an SQL Login for the new user, complete required details and select the required Historion database as the default. The Login name does not need to match the Windows Username. Ensure SQL Server authentication (not Windows) is selected;

Select [OK] then confirm that the new Login has been added to the list of Logins;

The new Login should appear in the list with a User icon. Open the Login from the list again then;

Select User Mapping from the top left panel.

Map the Historion database to the new Login for the SQL (direct) user account top right panel.

Select the Historion_Role membership in the lower right panel then select OK.

Confirmation of SQL (direct) Security

If the preceding steps have been done correctly the applicable security nodes in SQL Management Studio emphasized below should have values corresponding to the name you nominated for your Historion database and the name used for the new SQL (direct) Security User Account;

The next step is to proceed to and complete Historion_Role Permission Assignment.

Historion_Role Permission Assignment

Historion requires that the SQL Database Role Historion_Role has a standard set of permissions assigned to it which accord members access, in a safe manner, to allow appropriate record actions; SELECT,DELETE and INSERT to be performed on applicable data.

The Historion_Role needs at least Stored Proc Execute and Table Select access. There are also some custom SQL Types used by Historion that require specific permissions to be set.

The Historion database provides procedures that assign the required permissions to the SQL Database Role: Historion_Role. The procedures can be executed as a new script in SQL Management Studio by executing the commands exactly as follows or by downloading and running the permissions assignment script from one of the links below.

These are time saving commands for assigning applicable permissions without having to manually select and set permissions against hundreds of individual SPROCS, tables and views. Running the permission assignment commands also negates risk of human error.

The above Historion Permissions script can be typed in or is available more conveniently in three different formats (.txt, .sql and .zip) from the following three different locations;

Historion          https://www.historion.com.au/uploads/HistorionPermissions.txt

Drop Box          https://www.dropbox.com/s/bo215u22q3wdyk4/HistorionPermissions.sql?dl=0

Cybermynd          http://www.cybermynd.com.au\HistorionPermissions.zip

7. Implement Client Connections

Implementing Client Connections is part of the Workstation Implementation process.

It is important for the Client Connection string to correctly specify whether a trusted connection is used, in the case of SQL Server setup with Windows Active Directory (integrated) security or if an untrusted connection is used in the case of SQL (direct) Login security.

The connection string must end in Trusted_Connection=True for Active Directory integrated security

The connection string must end in Trustet_Connection =False for SQL (direct) security logins;

Copyright© Cybermynd Information Systems Consulting Pty Ltd