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

tde-azuresql

In fact, if your database is created after May 2017, chance is that encryption has it 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 Safewhere Identify database encryption.sql script that we prepared to encrypt all Identify's databases 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 works for normal web requests.

Enable encrypted connection for Azure SQL

Enable encrypted connection for Azure SQL is again easy: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-security-tutorial. In fact, you do not 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 trusts 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. You can generate this cerificate via PowerShell. For example, to generate a self-signed SSL server certificate for domain.win2016.com with a validity period of three years, you can use the following command:

New-SelfSignedCertificate -Type SSLServerAuthentication -Subject "CN=domain.win2016.com" -DnsName 'domain.win2016.com',"$env:COMPUTERNAME" -KeyAlgorithm "RSA" -KeyLength 2048 -Hash "SHA256" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1" -NotAfter (Get-Date).AddMonths(36) -KeySpec KeyExchange -Provider "Microsoft RSA SChannel Cryptographic Provider"

Certificates are stored locally on the computer for normal users. 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.

Configure certificate and encryption

Import the newly generated certificate to the Windows Server certificate store: LocalMachine\Personal.
Grant read permission for the user account used by the SQL Server service account to the private key of the certificate.
Configure the server to force encrypted connections by opening the properties for the SQL server under SQL Server Network configuration:
- Select Yes for the Force encryption option. Note that when this option is set to Yes, all communication between client and server is encrypted no matter whether the Encrypt connection option (such as from the SSMS) is checked or not. If you do not want to enable this setting, you can refer to https://blogs.msdn.microsoft.com/sql_protocols/2009/10/19/selectively-using-secure-connection-to-sql-server/ to see how to selectively use secure SQL connection

force-encryption-sql

Choose the certificate:

force-encryption-sql-certificate

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 Identify Admin site you need to add it as follow:

<connectionStrings> 
     <add name="IdentityProvider" connectionString="Server=DOMAIN\MSSQLSERVER01,49842; Database=Identify; uid=idp432778v1_********************; pwd=********; MultipleActiveResultSets=true;Encrypt=True;TrustServerCertificate=False;" providerName="System.Data.SqlClient" />
     <add name="IdentityProviderAudit" connectionString="Server=DOMAIN\MSSQLSERVER01,49842; initial catalog=IdentifyAudit; uid=idp432778v1_********************; pwd=********; MultipleActiveResultSets=true; Encrypt=True;TrustServerCertificate=False;" providerName="System.Data.SqlClient" />
     <add name="IdentityProviderCache" connectionString="Server=DOMAIN\MSSQLSERVER01,49842; Database=IdentifyCache; uid=idp432778v1_********************; pwd=********; MultipleActiveResultSets=true; Encrypt=True;TrustServerCertificate=False;" providerName="System.Data.SqlClient" /> 
     <add name="IdentifyUserNamePassword" connectionString="Server=DOMAIN\MSSQLSERVER01,49842; Database=Identify; uid=idp432778v1_********************; pwd=********; MultipleActiveResultSets=true; Encrypt=True;TrustServerCertificate=False;" providerName="System.Data.SqlClient" />
     <add name="IdentityProviderSession" connectionString="Server=DOMAIN\MSSQLSERVER01,49842; Database=IdentifySession; uid=idp432778v1_********************; pwd=********; MultipleActiveResultSets=true; Encrypt=True;TrustServerCertificate=False;" providerName="System.Data.SqlClient" />
</connectionStrings>

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

SQL Server Always Encrypted is currently not supported.