SQL Server Information for Simplify Suite

This article contains general information about database requirements for Simplify Suite and also describes the required permissions for Microsoft SQL Server for proper installation and operation of Simplify Suite.

Microsoft SQL Server Database Versions Supported

A Microsoft SQL Server 2008 (or later) database is required to install and operate Simplify Suite.

Tricerat has confirmed compatibility between Simplify Suite and:

  • Microsoft SQL Server 2008

  • Microsoft SQL Server 2012

  • Microsoft SQL Server 2014

  • Microsoft SQL Server 2016

  • Microsoft SQL Server 2017

  • Microsoft SQL Server 2019

Tricerat supports both full SQL Server and SQL Server Express as database platforms.

Some customers may want to install on SQL Server Express for evaluation and testing then migrate later to full SQL Server on an enterprise-class machine.

Microsoft SQL Server Management Studio (SSMS)

    TIP: Tricerat strongly recommends that customers install Microsoft SQL Server Management Studio (SSMS) in their Simplify Suite environment

SSMS can be a valuable tool to help configure SQL Server and can also help troubleshoot important issues.

SSMS should be installed on a different machine than the machine where SQL Server resides.  This is so SSMS can be used to help isolate firewall and connectivity issues.

Simplify Suite Database Authentication Types

For Simplify Suite, Tricerat supports both Windows authentication and SQL Server authentication.

     TIP: Tricerat strongly recommends that customers configure mixed mode authentication to allow both Windows authentication and SQL Server authentication for the widest flexibility.

Windows Authentication vs SQL Server Authentication

For both Windows Authentication mode and SQL Server authentication mode, a SQL Server Login ID is required as a prerequisite before installing Simplify Suite.

Creating a New Simplify Database

The Simplify Suite installer creates the required database during installation.

Customers must not create an empty database prior to the first installation of Simplify Suite.  That is not supported.

Customers must not attempt to re-use an existing database prior or re-house Simplify Suite in an existing database.  That is not supported.  Each instance of a Simplify Suite database must reside in its own database.

Special SQL Server permissions are needed during installation of Simplify Suite.

     TIP: Tricerat strongly recommends that customers install new versions of Simplify Suite in a test environment for evaluation, internal quality assurance, and change control management.  

SQL Server Permissions Required to Create a Simplify Database

    RULE: The SQL Server Login ID specified during the installation of Simplify Suite when the Simplify database does not yet exist must have at least SQL Server-level roles of 'db_creator' and 'securityadmin'.

A SQL Server Login ID that has a SQL Server-level role of 'sysadmin' can also be used since that role contains both 'db_creator' and 'securityadmin'.

 

Example

The following sample display from SSMS shows how to configure the required permissions:

 

Installing Simplify Suite When the Database Does Not Yet Exist

The following sample SQL credentials dialog from the Simplify Suite installer shows the relationship between the SQL Server information and the installer prompts:

 

During the creation of the database, permission to use the database and execute stored procedures is granted to all authenticated users via the GRANT EXECUTE TO [NT AUTHORITY\Authenticated Users] SQL command.

This is so SQL Server stored procedures installed in the database can execute without disruption or error.

If the SQL Server Login ID does not have sufficient privileges a message box is displayed and the installation will not proceed.  

Using an Existing Simplify Database

When Simplify Suite has been installed, the required SQL Server database is created.

Later, installations on satellite Simplify Suite machines can reference the existing database.

The SQL Server permissions needed to install Simplify Suite and refer to an existing database are lower than but different from the permissions needed to create a Simplify Suite database for the first time.

The Simplify Suite installer checks to see if the specified Simplify database already exists.

If not, the installer requires full database creation privileges (see above).

If the database exists, the installer may need to update the database schema version level to the version being installed. 

For this reason, the SQL Login ID used during installation of Simplify Suite requires that the SQL Login ID be mapped to the 'db_owner' permission for the destination database.

SQL Server Permissions Required to Install When the Simplify Database Already Exists

    RULE: The SQL Server Login ID specified during the installation of Simplify Suite when the Simplify database already exists must have have at least a mapping to 'db_owner' for the destination database.

A SQL Server Login ID that has SQL Server-level roles of either 'db_creator' and 'securityadmin' or 'sysadmin' can also be used since those SQL Server-level roles can manipulate and administer databases.

 

Example

The following sample display from SSMS shows how to configure the required permissions:

Installing Simplify Suite When the Database Already Exists

The following sample SQL credentials dialog from the Simplify Suite installer shows the relationship between the SQL Server information and the installer prompts:

 

During Simplify Suite installations on satellite machines when the destination database already exists, important checks are performed to make sure the database conforms to the minimum database version schema level for the Simplify Suite version being installed.

Least-Required Operating Privileges

Tricerat customers often require 'least privileges' information as part of best practices in their security environments.

After initial installation using suitable database SQL Login IDs, customers may want to tamp down their operating environment and use the lowest privileges required.

In the Simplify Console, customers can quickly configure their ODBC connection definitions to use the very lowest possible database permissions.

Running the Simplify Suite Console

Any authenticated user can run the SimplifyConsole.exe program.

During installation, Tricerat does not impose any UAC "As Administrator" requirements on the executable program or the Start menu shortcut.

After installation or upgrade of Simplify Suite, the least-required SQL permissions needed for most customers to launch Simplify Console are 'db_datareader' and 'db_datawriter'.

Users can be granted only 'db_datareader' access, in which case the Simplify Console becomes a read-only tool suitable for viewing only.

 

The SQL Login ID must also have permission to execute the stored procedures within the Simplify database.

To grant this permission do this, the following query can be used:

USE [<database name>]

GRANT EXECUTE TO [<SQL Login ID>]

 

Example:

USE [Simplify]

GRANT EXECUTE TO [TriceratDatabaseUser]

 

Related articles