Installing Microsoft SQL Server

This document will guide you through installing the required Microsoft SQL engine for Patriot 6 deployment. This document covers all supported versions of SQL with sections to help the non-automated full version installations. (when not using the provided SQL express engine). If at any time you run into any unexpected problems during any part of this installation guide, try the Patriot 6 installation trouble shooting guide, or contact your software supplier. Alternatively you can contact Patriot support directly at support@patriotsystems.com

As an alternative, Patriot is allowed to freely distribute SQL Express to anyone using Patriot. The express version is purely a SQL database engine; it has some advanced functionality removed, and has some size restrictions. But these should only affect users of Patriot with a large client base. If you are trialling Patriot, SQL express will be more than adequate. Patriot 6 requires that Microsoft SQL Server 2014 or newer is installed on the server machine. The full version of the Database Engine is recommended, but requires separate licensing through Microsoft. For ease of use, patriot supplies an installation package for express SQL for easy configuration.

Comparisons of the different versions of SQL with respect to Patriot are available in the Patriot Hardware Specifications. For new installations, Patriot recommends installing the newest supported version

Previous versions of SQL server such as SQL server 2012 or lower are no longer supported in Patriot 6

For larger monitoring stations, read the SQL Server System Setup document for more information on setting up an SQL server using recommended settings.

Installing Microsoft SQL 2014

Follow the steps below to install and configure SQL 2014

• Ensure you have administrative rights to the target computer
• Check your computer specification and make sure that it is compatible with SQL 2014.
• Insert the SQL Server DVD or start the downloaded SQL 2014 Setup Program
• Complete the planning phase of the SQL 2014 installation process and move on to Installation
• Select the Installation type required. Usually this will be the first option: New SQL Server stand alone installation Consult with your network administrator if unsure which installation procedure to follow.
• Ensure that all Operations of the Setup Support Rules completed correctly. Any failures should be analysed and remedied before continuing.
• Select the SQL Server Installation type or enter your product key
• Install the Setup Support Files when prompted. View the resultant Setup Support Rules report and remedy any errors reported.
• In the Feature Selection Window, the minimum, feature set is:
  • Database Engine Services
  • Client tools Connectivity
  • Management Tools - Basic
  • Management Tools - Complete
• In the Instance configuration select Default Instance
• In The Server Configuration window, select the Service Accounts tab. For the SQL Server Agent and SQL Server Database Engine, select the NT AUTHORITY\SYSTEM user from the drop down.
• In the collation tab, leave the values as default unless specialised requirements exist. Click Next to continue.
• In the Database Engine Configuration, select the Account Provisioning tab. Under Authentication Mode select:
• Mixed Mode (SQL Server Authentication and Windows Authentication) Enter and confirm the SQL Administrator password. This must be Patriot12## The Password is case sensitive.
• Add the Current User as an SQL Administrator Account.
• Follow the on screen prompts to complete the installation process.

After installation of SQL 2014 is finished follow steps open SQL Server Management Studio and run the following query to check if account NT AUTHORITY\SYSTEM has system admin permission (please contact patriot support if you are unsure how to do this).

USE master
GO

SELECT
  p.name AS [loginname],
  p.type,
  p.type_desc,
  p.is_disabled,
  CONVERT(VARCHAR(10),p.create_date ,101) AS [created],
  CONVERT(VARCHAR(10),p.modify_date , 101) AS [update],
  s.sysadmin
FROM sys.server_principals p
JOIN sys.syslogins s ON p.sid = s.sid
WHERE
  p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
  -- Logins that are not process logins
  AND p.name NOT LIKE '##%'
  -- Logins that are sysadmins
  -- AND s.sysadmin = 1
GO

• If value in column sysadmin for NT AUTHORITY\SYSTEM is set to 0, you need to enable sysadmin for NT AUTHORITY\SYSTEM
• To change sysadmin permission for account NT AUTHORITY\SYSTEM in SQL Management Studio go to Security > Logins. Right click on NT AUTHORITY\SYSTEM > Properties > Server Roles and tick checkbox for sysadmin. Click OK.
• Patriot 6 installation can proceed.