...
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.
...
| Issue | Workaround | ||||||||
|---|---|---|---|---|---|---|---|---|---|
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+ server | Synergetic Management Systems | columns | key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution | serverId | 3587bf9f-563b-3952-afa4-4457c6487443 | key | DSY-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 |
...
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
| Programs | Tests | Comments |
|---|---|---|
| SynMain |
|  |
| SynWeb |
| |
| Community Portal |
| |
| Form Builder |
| |
| Online Event Booking |
| |
| SADT |
| |
| SSRS |
| |
| Service Suite |
| 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 API | Has been utilised through testing (e.g. Form Builder/Payments) | |
| Application Portal |
| |
| DB Patcher | Database has been patched without error | |
| Crystal Reports | SynMain
SynWeb
| Working in SynMain, no longer working in SynWeb Have located issue and working on finalising solution
|
| Study Period | Accessed and checked in Student | |
| SIF | Executed calls to retrieve data
| |
| Power BI | Finance & Non-Finance report produces correct data | |
Reference articles
https://blog.coeo.com/securing-connections-to-sql-server-with-tls
...