Versions Compared

Old Version 2

changes.mady.by.user Steve Lynch (Deactivated)

Saved on

compared with

New Version Current

changes.mady.by.user David Rogers

Saved on

Key

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

...

Out of the box SQL Server queries and results are passed to and from the client and server in an unencrypted format which can be viewed easily from network traffic capture tools. SQL Server network traffic may contain sensitive data such as personal or financial details that need to be protected from unauthorised parties. See Synergetic Data Privacy and Security Information Sheet for for more information on the types of data and data breach rules that may apply to your organisation. To protect network traffic transmissing SQL Server provides native facility to encrypt traffic between the client and server, rendering captured network packets as unreadable and keeping any data transferred confidential whilst it is in transit.

...

key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution


IssueWorkaround

Crystal Reports fail from Synmain when TLS 1.0 is disabled

Cause:

Synergetic 'ODBCAutoConfig' and File > Workstation Config defaults to using SQLSRV32.DLL

This is due to the default ODBC driver SQLSRV32.DLL which does not support TLS 1.1+

Jira Legacy
serverSynergetic Management Systems
columns
serverId3587bf9f-563b-3952-afa4-4457c6487443
keyDSY-18676

Disable ODBCAutoConfig (HKLM\Software\Wow6432Node\ComputingDirections\ODBCAutoConfig = 0) - note that this cannot be disabled in multi-tenant environments at this stage.

Change ODBC reg setting to use newer SQL driver

Key: HKCU\Software\ODBC\odbc.ini\Synergetic\Driver

Value: 'C:\Windows\system32\sqlncli11.dll'


The ODBC settings will require manual update via reg key import to switch between environments (prod/dev/test) or tenants in a  multi-tenant environment.

SEQTA Sync JDBC error

none available

...

1. Create the certificate request

Server may already have a certificate available

Certificate request requirements:

...

2. Submit the request to the CA

http://hyperion.main.cda.com.au/certsrv/


3. Install the certificate

...

Then reboot the SQL server(s) to apply the settings.

Important Note: third party dependencies will need to be tested to ensure that they support TLS 1.2. For example if the SQL Server also needs to send emails via TLS it is important to test that the mail server will support TLS 1.2. For example, one issue encountered whilst we were testing was sending email via smtp.office365.com, the following error was returned via the sample Powershell command 'Send-MailMessage : The client and server cannot communicate, because they do not possess a common algorithm'. This required the TLS 1.1 and 1.0 Client Protocols to be reactivated to be able to send emails successfully.

6. Test that encryption is working

...

Sample Query : SELECT @@SERVERNAME

Sample Filter: ip.src == 10.50.50.xxx and tcp.port == 1430

Sample unencrypted traffic (TLS disabled)

...

With encryption forced on the SQL Server it is critical that a trusted and current certificate is maintained on the server. After the certificate expiry the server will stop accepting connections until the certificate is replaced with a new one, so proactive certificate management should be scheduled to replace the certificate ahead of the expiry and avoid any outages.

Testing

Set up on HERA3\DevTest for v70


ProgramsTestsComments
SynMain
  1. Authentication: Windows & DB
  2. Accessed Maintenance Programs
  3. Executed basic functionality (incl. Finance)
    1. Created Student
    2. Completed Debtor Cash Receipt posting
(tick)
SynWeb
  1. Authentication: Windows & DB
  2. Accessed Maintenance Programs
  3. Executed basic functionality (incl. Finance)
    1. Created Student
    2. Completed Purchase Order Requisition (incl. email received)
(tick)
Community Portal
  1. Accessed all pages
  2. Executed some functionality
(tick)
Form Builder
  1. Copied form
  2. Published form
  3. Submitted form (Application received in SynMain)
(tick)
Online Event Booking
  1. Completed through to paid event
(tick)
SADT
  1. Signed In/Out (data correct in SynMain)
(tick)
SSRS
  1. Ran finance/non-finance report
    1. STUYR
    2. DEBFEE
(tick)
Service Suite
  • Service Running
  • Logs Produced
  • Scheduled Reports emailed

Working on issue where scheduled Crystal reports to be emailed are erroring due to TLS

Working on solution that does not conflict with SynWeb fix

Core APIHas been utilised through testing (e.g. Form Builder/Payments)(tick)
Application Portal
  1. Authentication: DB
  2. Created and processed new form
(tick)
DB PatcherDatabase has been patched without error(tick)
Crystal Reports

SynMain

  • Finance
  • Non-finance

SynWeb

  • Finance
  • Non-finance

Working in SynMain, no longer working in SynWeb

Have located issue and working on finalising solution

  •  SynMain fixed DSY-18676
  •  Tracking progress - DSY-20570
Study PeriodAccessed and checked in Student(tick)
SIF

Executed calls to retrieve data

  1. StudentPersonals
(tick)
Power BI

Finance & Non-Finance report produces correct data

(tick)

Reference articles

https://blog.coeo.com/securing-connections-to-sql-server-with-tls

...