You are concerned about securing your SQL Server database that is being used to store your Sage X3 data.   It is best practice to utilise defence-in-depth and use the relevant security features SQL Server provides in order to protect your business data.    

When it comes to SQL Server specifically, you may have been reading up on the Microsoft SQL Server documentation pages about some or all the following topics, and want to know which can be used with Sage X3:

• Transparent Data Encryption (TDE)
• Encrypted Connections to the Database Engine
• Always Encrypted
• Dynamic Data Masking (DDM)

The first thing I must say is that Sage do not explicitly test nor certify any of these features, so have no documentation or experience to share with you.    If you are considering using any of these features, you should therefore engage your own SQL Server experts to assist with the implementation and testing, to ensure it performs to your expectations and business requirements
 
Whilst Sage X3 Support team would always try to assist with any issues you may have with such an installation, if the reported issue seems to be related to these features, our input may be limited unless you are able to reproduce with an instance not using these features

So is that the end of the story… well yes and no.  The above situation is true, Sage do not explicitly test nor certify these features, but it does not necessarily mean Sage X3 will not function with them nor that you cannot implement them if they are needed to satisfy a particular business requirement.   Of course, you need to satisfy yourself any of these features will work in your specific circumstances, by performing your own testing of all business functions using a representative test system.   Don’t forget to test any Third Party or customised interfaces, as well as making sure things like patching your Sage X3 components still functions correctly.  You should also have a backout plan in place, in case you do start getting issues and need to re-test without these features enabled

It just happens I have taken a look at these features on my own internal Version 12 test system and would like to share my results with you:

So where do we start ?  The Microsoft documentation seems like a good place!

NOTE: My test system is using Windows 2016 server with X3 V12 Patch 24 instance with latest patches and SQL Server 2017 Developer Edition, although the principles will likely apply for some earlier versions

Summary

Transparent Data Encryption (TDE)

This provides encrypting of data at rest, i.e. the physical data/log files are encrypted on disk.  If an attacker can gain access to these files, then they will have trouble decrypting the data unless they also obtain the "key" (server certificate)
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-2017

Encryption of a database file is done at the page level. The pages in an encrypted database are encrypted before they're written to disk and are decrypted when read into memory.  Our estimation (using SQL Server 2017) is that heavy operations that use tempdb are consuming up to 30% to 40% more CPU and memory consumption is also increased by up to 15-20% when using TDE, compared to without using TDE

NOTE: This feature is only available for Developer or Enterprise editions of SQL server

Connect as "sa" user

Check you are using Developer or Enterprise edition

SELECT SERVERPROPERTY('productversion'), 
SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The steps in summary are to:

1. Create a master key.
2. Create or obtain a certificate protected by the master key.
3. Create a database encryption key and protect it by using the certificate.
4. Set the database to use encryption.

You can achieve these steps with the following SQL statements:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';
go
CREATE CERTIFICATE ServerCertificate WITH SUBJECT = 'LocalServer';
Go

The following SQL should now return a value:

select * from master.sys.symmetric_keys where name like '%DatabaseMasterKey%'

Continuing with the process, now select the X3 database and apply encryption to it.   Although this process is running in background, you ideally don’t want to have user activity on the database whilst this is happening, so you may want to shutdown all Sage X3 processes before running this command:

USE x3erpv12;   -- X3 database name
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 
ENCRYPTION BY SERVER CERTIFICATE ServerCertificate;
GO
ALTER DATABASE x3erpv12 SET ENCRYPTION ON;
GO

Run the following SQL to check the status of the encryption for this database:

SELECT DB_NAME(database_id)
  ,encryption_state = CASE 
    WHEN encryption_state = 1
      THEN 'Unencrypted'
    WHEN encryption_state = 2
      THEN 'Encryption in progress'
    WHEN encryption_state = 3
      THEN 'Encrypted'
    WHEN encryption_state = 4
      THEN 'Key change in progress'
    WHEN encryption_state = 5
      THEN 'Decryption in progress'
    WHEN encryption_state = 6
      THEN 'Protection change in progress'
    WHEN encryption_state = 0
      THEN 'No database encryption key present, no encryption'
    END
  ,create_date
  ,encryptor_type
FROM sys.dm_database_encryption_keys

Once completed, your database should now be ready to go with encrypted data stored on physical disk.     You may find slightly higher CPU usage as decryption is required when getting data from disk, but provides extra protection of your data stored on disk

Encrypting connections over the network

SQL Server can use Transport Layer Security (TLS) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application, such as Sage X3
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-2017

NOTE: You should use Sage X3 Print Server EDT 2.21 or above, and ensure you are using "ODBC Driver 17 for SQL Server" rather than the older "CR SQL Server Wire Protocol ODBC Driver 5.3" (Described further in KB Article 100320 "How can I update the ODBC connection being used by the Sage X3 Print Server"

After implementing this process, database connections require the use of a TLS communication channel between the client processes (Sage X3) and SQL Server

1. Obtain or create SQL Server certificate, taking note of the "Certificate Requirements" listed in the Microsoft documentation
2. Install the certificate into the "Local Computer" account using the Certificates snap-in in MMC.  This needs to be done on the SQL Server itself
3. Configure SQL Server to force the use of certificates for connection

1. Obtain SQL Server certificate
In my example, I am using a PFX certificate signed by my own CA, as mine is a TEST server, which I generated using OpenSSL

2. Install the certificate
As my certificate is PFX format, I can just double click it and install to the Local Machine


 

 

 

 

 
Check the result by using the Manage computer certificates, or launch MMC and select the "Certificates" add-in

 
Finally, I will import the CA certificate, as I am not using one of the recognised CAs


 

3. Configure SQL Server to force the use of TLS connection

Launch SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for <server instance>, and then select Properties

In the Protocols for <instance name> Properties dialog box, click on the Certificate tab, select the desired certificate from the drop-down for the Certificate box, and then click OK

 

On the Flags tab, in the ForceEncryption box, select Yes, and then click OK to close the dialog box

 

Restart the SQL Server service

If the service does not restart, and the ERRORLOG shows error message "The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid." This likely means there is a permissions issue for the certificate.  In this case you can take these steps to check/resolve:
-In Windows Services, check which account is configured to launch SQL Server

 
-Go back into Certificates
-In Personal> Certificates select the server certificate you loaded
-Right click then select All tasks> Manage Private Keys

 
-Add the SQL Server account

 
-The SQL Server service should now startup OK

We can now test connectivity from SSMS
-On the Object Explorer toolbar, click Connect, and then click Database Engine
-In the Connect to Server dialog box, complete the connection information, and then click Options
-On the Connection Properties tab, click Encrypt connection

In SSMS you can check the status of your connections using the SQL

SELECT  session_id,encrypt_option
FROM    sys.dm_exec_connections

NOTE: as part of your testing, ensure any third party software, such as EDM, etc. can still connect to the database  

Always Encrypted

Encrypting data at rest, for specific table columns.  This means that even System Administrators cannot view the actual column data (unless they also have access to the encryption certificate)
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-2017

Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine, i.e. the encrypted value is stored in a table column.  This requires an Always Encrypted enabled client driver

The basic steps are to :

1. Create encryption key certificates
2. Encrypt specific table columns
3. Setup your application to use Always Encrypted

With Sage X3, the big blocker is point 3 for now, as Sage X3 currently only implements SQL Server Native Client 11 ODBC driver, which does not support Always Encrypted.  In other words, Sage X3 does not have an Always Encrypted enabled client driver…

Even if this can be overcome, then point 2 should not be underestimated.  Having read the restrictions on what you can do with encrypted columns, you would need to be very careful about what columns you try to encrypt and the type of encryption used!

Dynamic Data Masking (DDM)

Hide sensitive data in result sets, for specific table columns
https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-2017

Data in the database is not changed by this process, instead masking rules are applied in the query results, so this should be transparent to applications.  A masking rule may be defined on a column in a table, to obfuscate the data in that column

Be aware though, that these restrictions are based on the database user name, not the X3 User name, so any user who can login to X3 will be able to see the unmasked data (as the same database user is used for all X3 logins to the same folder), however if you only have access to the database via SSMS, or some other third party tool or software, your user name may not have the correct rights to see the masked data.   

In my example I will add a default data mask to the PINVOICE  table NUM_0 column (the invoice number) using the following SQL:

ALTER TABLE [SEED].[PINVOICE] ALTER COLUMN NUM_0 ADD MASKED WITH (FUNCTION = 'default()')

If I restart Sage X3 services for this to take effect, then navigate to Purchasing> Invoices> Invoices,  I now see my document numbers are masked:

 
This isn’t what I was aiming for as I still want my X3 users to see this data, so in SSMS I need to add the UNMASK permission to the appropriate SQL accounts, for example:

GRANT UNMASK TO X3, SEED, HSEED

Restart Sage X3 services, so now when I login to view the Purchase Invoices via Sage X3, I can see the data values as expected:

 
However if I login to SSMS using a different user (without UNMASK permissions) I only see the masked values

 
It may be more useful to have these masking rules based on an X3 username rather than the database username, however that is not an option so this may limit the benefit provided by this feature

Conclusion

Whilst Sage do not test nor certify these specific options for Microsoft SQL Server, if you do need to use them to fulfil your businesses legal or corporate policies, then the above notes will hopefully give you some initial ideas as to how to approach these implementations