Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Synergetic databases and applications require adequate security configuration through multiple layers of the environment. The objective of this document is to provide guidelines to hardening a Microsoft SQL server.

Whilst all care has been taken in preparing this guide, Education Horizons Group does not warrant that the contents of this guide (i.e. information, recommendations, opinions or conclusions contained in this guide (“Information”)) is accurate, reliable, complete or current. The Information does not purport to contain all matters relevant to the usage of Synergetic software. The Information has been prepared on the basis of circumstances and technology current as at the date of the report and care should be taken by the School to determine if circumstances have changed in a manner which would affect the Information. To the extent permissible by law, Education Horizons Group shall not be liable for any errors, omissions, defects or misrepresentations in the Information or for any loss or damage suffered by persons who use or rely on such Information (including by reasons of negligence, negligent misstatement or otherwise). If any law prohibits the exclusion of such liability, Synergetic limits its liability to the re-supply of the Information, provided that such limitation is permitted by law and is fair and reasonable.

The recommendations herein provided are based on the Center for Internet Security (CIS) hardening guides and benchmarks for SQL server 2016 or above running on Windows Server 2016 or above. Each recommendation should be considered with reference to the specific environment requirements. Changes may result in applications not functioning as expected, particularly where there configuration differs from the base products.

A server level backup as well as a backup of the individual configuration files should be taken prior to making any changes. 

The server will need to be rebooted after the changes have been made

All commands supplied are to be run in an elevated command shell or elevated PowerShell as required or SQL Server management studio, connected as a priviledged user.

Table of Contents

Installation, updates and patches

Ensure Latest SQL Server Service Packs and Hotfixes are Installed

...

Enable or disable the service as needed for your environment.

CIS Recommendation exceptions

There are a number of CIS recommendations that should not be implemented on Synergetic environments.  These are:

CISDescriptionReason
2.2Ensure 'CLR Enabled' Server Configuration Option is set to '0'Required for underlying logic
2.9Ensure 'Trustworthy' Database Property is set to 'Off' Required for CLR access
2.11Ensure SQL Server is configured to use non-standard ports

Not supported for default instances . May have issue with changing port on default instance as Synergetic config does not allow supplying of port number in the configuration file. However, this would works okay for named instances using the SQL Browser Service but then CIS 2.12 could not be performed to 'hide' the instance. 

2.14Ensure the 'sa' Login Account has been renamedSynergetic has dependencies on DB owner matching the user that created the CLRs, which is normally ‘sa’ and set the DB owner to dbo (which is linked to sa).
2.17Ensure no login exists with the name 'sa'As above, ‘sa’ user is required but can be disabled
3.1Ensure 'Server Authentication' Property is set to 'Windows 
Authentication Mode'
Synergetic requires mixed mode - normal staff and admin user accounts can all use Windows Auth but the application has internal SQL user accounts (zSynergetic_*) managed by the patch process and used for each application
3.4Ensure SQL Authentication is not used in contained databasesAs above, Synergetic uses contained users for the zSynergetic* application user accounts
6.2Ensure 'CLR Assembly Permission Set' is set to 'SAFE_ACCESS' 
for All CLR Assemblies

Current Synergetic CLR settings are defined as follows: 

System.Drawing UNSAFE_ACCESS 

SynStreamCrypt SAFE_ACCESS 

Synergetic.Database.CLR UNSAFE_ACCESS 

GroupConcat SAFE_ACCESS 

SqlRegEx SAFE_ACCESS 

Synergetic.Database.CLR.XmlSerializers EXTERNAL_ACCESS