Revision History

Securing Connections to SQL Server

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 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.

Network traffic encryption adds some administrative (minor) and performance overheads to the environment so this should be weighed up against the existing security risk exposure of the environment, for example within existing network security and type of data accessed. This article provides top level details on SQL Server TLS encryption and demonstrates how to enable it for all connections to the server. The example also provides the additional steps required for environments using SQL Availability Groups.

Important note: there are some known issues with TLS enablement with Synergetic and additional testing is planned to determine if any further issues need to be resolved. Limited testing has been performed and some more extensive testing is currently planned before sign-off. Whilst there are workarounds available for some of the issues it is recommended to first enable TLS within a development or test environment to verify any other implications such as with third party integrations.

Network Encryption Options

Internet Protocol Security (IPSec)

SQL Server data can also be encrypted during transmission by using IPSec which is technology normally used by VPNs. IPSec is provided by the client and server operating systems and requires no SQL Server configuration. For information about IPSec, refer to Windows or networking documentation.

Key points:

• Often IPSec VPNs are used in conjunction with SSL to help secure remote site access to applications
• Layer 3 protocol Network
• Less granular than SSL/TLS
• For granular session and presentation layer (layer 5 & 6) it is recommended practice to use TLS/SSL
• Does not encrypt traffic after the endpoints by itself, therefor TLS is also recommended

This article does not cover IPSec configuration.

Transport Layer Security (TLS)

Key points:

• Encrypts network traffic only not file system or database (TDE) or backups etc

  • column level encrypted

  • always encrypted - client controlled

• PCI compliance requires encrption with TLS 1.2 which is supported in SQL 2008+

• Requires certificate installed on SQL Server

• Requires the trusted root CA certificate installed to the client

• Can be configured to work with SQL Availability Groups or stand-alone SQL servers

  • Permissions for the SQL service account to access cert

  • SQL Server set to force-encryption, unless endpoint can specify encrypt option = true (not currently supported by Synergetic)

Known issues

The following lists known issues with enabling TLS 1.2 and Synergetic.

List current as at 31 Aug 2020 

SQL Server TLS Configuration Steps

The process below is required on each node of the SQL Availability Group. Each server node requires its own unique cert, so the request should be reflective of the local node.

For single server environments then the SAN for the AG listener is not required.

1. Create the certificate request

Server may already have a certificate available

Certificate request requirements:

• CN=servername.domain.com
• SAN=sqlaglistener.domain.com (only for SQL AGs)
• Extended Key Usage = Server Authentication
• Key Size = 2048
• Key Type = Exchange

2. Submit the request to the CA

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

3. Install the certificate

4. Configure SQL Server Service Account Permissions (for SQL AGs only)

If using SQL AGs then the registry requires a manual update with the certificate thumbprint.

Open properties of the cert > Details and copy the Thumbprint to clipboard

Open key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer\SuperSocketNetLib > Certificate

Paste the thumbprint value in

Windows Explorer - add "Read" permission for the SQL Server Service account to the following folder path:

C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\yournewcertname

Eg. "C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\3ab225fb9552f228af62f765073e4c2d_d0561438-de72-4fe6-ad5e-781b7838de90"

If unsure of the certificate name, sort by date modified to see the new cert added today, this will be the required cert file.

If the SQL Server Service account is a local administrator then the permission set is not required however it is best practice for the service account to not have admin permissions to the server.

4. Configure SQL Server to use encryption

5. Disabling weaker encryption methods (TLS 1.0/1.1/SSL)

Windows registry settings need to be created to ensure that weaker encryption methods are disabled - TLS 1.0 should ideally be disabled as it is not secure and whilst TLS 1.1 does not have known vulnerabilities at the time of writing, this has been replaced by TLS 1.2 which addresses some flaws within it's implementation.

Use a tool such as IISCrypto or manually set registry settings as per Microsoft KB article or this blog post.

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

Connect via SQL Management Studio or Synmain app then run the following query:

SELECT 
  p.spid,
  p.program_name,
  u.name, 
  p.hostname, 
  c.encrypt_option, 
  c.auth_scheme, 
  c.last_read, 
  c.last_write,
  p.login_time,
  p.last_batch
FROM sys.dm_exec_connections c
INNER JOIN sys.sysprocesses p on (p.spid = c.session_id)
INNER JOIN sys.database_principals u on (u.sid = p.sid)

The encrypt_option column should return TRUE for all sessions.

Wireshark Tests

Sample Query : SELECT @@SERVERNAME

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

Sample unencrypted traffic (TLS disabled)

Sample encrypted traffic (TLS enabled)

Certificate Management (Important)

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

Executed against  Unable to locate Jira server for this macro. It may be due to Application Link configuration.

Programs	Tests	Comments
SynMain	Authentication: Windows & DB Accessed Maintenance Programs Executed basic functionality (incl. Finance) Created Student Completed Debtor Cash Receipt posting
SynWeb	Authentication: Windows & DB Accessed Maintenance Programs Executed basic functionality (incl. Finance) Created Student Completed Purchase Order Requisition (incl. email received)
Community Portal	Accessed all pages Executed some functionality
Form Builder	Copied form Published form Submitted form (Application received in SynMain)
Online Event Booking	Completed through to paid event
SADT	Signed In/Out (data correct in SynMain)
SSRS	Ran finance/non-finance report STUYR DEBFEE
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 API	Has been utilised through testing (e.g. Form Builder/Payments)
Application Portal	Authentication: DB Created and processed new form
DB Patcher	Database has been patched without error
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 Unable to locate Jira server for this macro. It may be due to Application Link configuration.  - SynMain fixed Unable to locate Jira server for this macro. It may be due to Application Link configuration.  - Tracking progress
Study Period	Accessed and checked in Student
SIF	Executed calls to retrieve data StudentPersonals
Power BI	Finance & Non-Finance report produces correct data

Reference articles

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

https://www.mssqltips.com/sqlservertip/3299/how-to-configure-ssl-encryption-in-sql-server/

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189067(v=sql.105)?redirectedfrom=MSDN

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-ver15

https://blogs.msdn.microsoft.com/sqlserverfaq/2010/05/28/inf-permissions-required-for-sql-server-service-account-to-use-ssl-certificate/

https://www.youtube.com/watch?v=UvoEYqB0tX

https://www.youtube.com/watch?v=KrPp-G_1aAk

https://support.microsoft.com/en-sg/help/3135244/tls-1-2-support-for-microsoft-sql-server