Safewhere Identify data encryption


Data encryption 

Safewhere Identify stores its main data in either Azure SQL or SQL Server databases and this document will show you how to do encryption for its data. 

There are usually two main layers of encryption: encryption at the database level (also called encryption at rest) and encryption at the application level. In addition, we can use encrypted connection to encrypt data while it is in-transit from database server to client. 

Encryption at database level 

Encryption at database level means that we will encrypt the whole database and is fairly easy to do. Microsoft calls this “Transparent Data Encryption (TDE) and explains its benefit as: 

However, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can just restore or attach the database and browse the data. One solution is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using the data, but this kind of protection must be planned in advance. 

Enable TDE for Azure SQL 

After you have created your first Identify instance, a few databases were created. You can log in to your Azure Subscription, open the databases -> Transparent data encryption -> Enable data encryption. 

 2019-06-10_21-33-03

In fact, if your database is created after May 2017, chance is that encryption has is enabled by default. You can find more information at https://docs.microsoft.com/en-us/azure/sql-database/transparent-data-encryption-azure-sql 

Enable TDE for SQL Server 

Enabling TDE for SQL Server needs a bit more work. The full detail is here: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-2017   

You can use the script we prepared to encrypt all Identify’s databases: “Safewhere Identify database encryption.sql” as well as do the certificate and key backup.

Encrypted connection 

The purpose of encrypted connection is to make sure data transferred from database to client (e.g. your application) is encrypted. You can liken this to how https work for normal web requests. 

Enable encrypted connection for Azure SQL 

Enable encrypted connection for Azure SQL is again easyhttps://docs.microsoft.com/en-us/azure/sql-database/sql-database-security-tutorial. In fact, you don’t have to do anything on the server side. 

Enable encrypted connection for SQL Server 

Enable encrypted connection for SQL Server requires you to generate a certificate and install it correctly on the server side. If it is a self-signed certificate, you will need to make sure your client machine trust it: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-2017 

Prepare 

  • The certificate must be issued for Server Authentication. The name of the certificate must be the fully qualified domain name (FQDN) of the computer. 
  • Certificates are stored locally for the users on the computer. To install a certificate for use by SQL Server, you must be running SQL Server Configuration Manager with an account that has local administrator privileges. 

How to do for SQL server 

  • Import the certificate to the window server store: LocalMachine\Personal 
  • Grant read permission for the user account used by the SQL Server service account.  
  • Configure the server to force encrypted connections by opening the properties for the SQL server under SQL Server Network configuration: 

 2019-06-10_21-38-49

  • Choose the certificate: 

 2019-06-10_21-39-37

Enable encrypted connection on client side 

After enabling encrypted connection on the server side, you need to add “Encrypt=True;TrustServerCertificate=False;” to all connection strings. For example, with Admin site you need to add it as follow: 

In which the TrustServerCertificate=False states that only server certificate that is trusted by the client machine is accepted. Setting it to true will means client code will accept all server certificates which is less secure. 

Encryption at application level 

We have tested the Always Encrypted feature of SQL Server a few times, but it still hasn’t worked perfectly for us. We expect to give the new Always Encrypted with security enclave (https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-enclaves?view=sqlallproducts-allversionsa try when SQL Server 2019 is officially released.