Support Portal ContactGet in touch

TDE Database Encryption with On Premise Dynamics

   Words by James Binnie

   on 03/06/2020 17:00:00

We were tasked by a client to look at what levels of database encryption (encryption at rest) were in place and also how we could fill in any gaps. Here in the UK we would be talking about the sensitive personal information covered by the GDPR legislation, and how we can make it as secure as possible.

The request is quite simple – ‘please encrypt our database’, however from a SQL novice point of view, this would require a lot of new learning for your typical Dynamics CRM consultant.

Sure I can deploy a multi–server deployment of Dynamics, but we only really touch base with SQL Server and database backups, so I already knew this would be an interesting challenge…

Why Should you Encrypt?..

So we have the correct password policy, latest CRM patches for 8.2 and the server array and network conforms to TLS 1.2. For industry standards data access and data transit is where it currently needs to be, however how secure is that data storage? Microsoft’s example runs along the line of ‘If someone was able to get access to your SQL Server and remove the hard drive, they could then restore the data in a secondary SQL Server’. This causes a major security flaw and that is where TDE (Transparent Database Encryption) comes in

TDE is SQL’s form of encryption at rest. The term “data at rest” refers to the data, log files, and backups stored in persistent storage and allows an organisation to protect its data when not in use or when stored for audit purposes for instance

Things to Consider…

If you ever find yourself in this domain then I thought I would stick this at the top of the blog rather than a wrap up at the end – These are things I either knew already of found out during the process;

    How and Where to Start

    I began my search with Microsoft via a good old google search. From what I could find there seemed to be 2 different options available here – Always Encrypted and TDE (Transparent Data Encryption). I did start with Always Encrypted as we only needed to encrypt 6 Fields on the Case (Incident) Entity and this seemed to be the least intrusive option. However as stated above, it was proving to be awkward and slow to test, and CRM was throwing generic SQL errors. With nothing in the SQL Event Log or the SQL Server Logs (Plus with me knowing that Dynamics 365 uses TDE my heart wasn’t really into wasting large amounts of time without exploring TDE myself first)

    What I would say is that the Microsoft documentation is very good and clear and provides your with all the information your require.

    Setting up TDE

    The diagram taken below from https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15 shows how the setup of TDE works. Using the familiar Parent/Child relationship concept we have in CRM, here we have a Database Master Key (Parent) and a Database Encryption Key (Child)

    The Transparent Database Encryption architecture

    I would recommend getting comfortable around the above diagram as this drives the next stage in the process

      So we now need to create a DMK (Database Master Key) and a DEK (Database Encryption Key) using the below SQL Query;

        USE master;
        Go
        Create Master Key encryption by password = '<Add a secure password here>';
        go
        CREATE CERTIFICATE Give the DMK a certificate name WITH SUBJECT = 'Example Certificate Subject';
        go
        USE The target Database you want to apply TDE to;
        GO
        CREATE DATABASE ENCRYPTION KEY
        WITH ALGORITHM = AES_128
        ENCRYPTION BY SERVER CERTIFICATE Give the DMK a certificate name WITH SUBJECT ;
        GO
        ALTER DATABASE USE The target Database you want to apply TDE to
        SET ENCRYPTION ON;
        GO

          And here is what is looks like for me

            image

              Once executed, you will get a message below to confirm and you can also check the current SQL Server Logs to show that the TDE Encryption Scan is in progress – Once the scan is completed, TDE is now set up and in place

                Progress and Evidencing

                On my google travels I came across the following query a few times that I would recommend;

                  Helps you to evidence TDE is in place for a Database

                    Shows you the progress of the TDE encryption scan

                      SELECT DB_NAME (database_id) AS DatabaseName, encryption_state,
                      encryption_state_desc =
                      CASE encryption_state
                               WHEN '0'  THEN  'No database encryption key present, no encryption'
                               WHEN '1'  THEN  'Unencrypted'
                               WHEN '2'  THEN  'Encryption in progress'
                               WHEN '3'  THEN  'Encrypted'
                               WHEN '4'  THEN  'Key change in progress'
                               WHEN '5'  THEN  'Decryption in progress'
                               WHEN '6'  THEN  'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
                               ELSE 'No Status'
                               END,
                      percent_complete,encryptor_thumbprint, encryptor_type  FROM sys.dm_database_encryption_keys

                        Copy the above query like for like, you don’t need to change anything

                          image

                          TDE Encryption Scan in progress

                          image

                          TDE Encryption Scan complete

                          • "Paul has made a real difference to how my team of 24 people record and store valuable customer data and sales opportunities. Highly recommended."

                            James, Operations Director

                          • "Understanding your business allows us to advise when to implement aspects of CRM and, likewise, when not to."

                            Paul McQuillan, Managing Director

                          • "Dynamics 365 and CRMCS have made a real lasting difference to our business, allowing us to replace older systems that were holding back our performance."

                            Grahame, Chief Operating Officer

                          • "James worked well with us to help connect CRM with Outlook and relate how this might benefit our team using CRM for Property Care."

                            Natalie, Property Care Supervisor

                          • "Matt was really good with helping us run User Testing on the new Compliance Module of our CRM System."

                            Tom, Compliance Administrator

                          Prefer to go old-school?

                          Write to us using the below addresses.

                          Head Office
                          CRM Consultancy
                          61 Oxford Street
                          Manchester
                          M1 6EQ

                          London Office
                          CRM Consultancy London
                          Grosvenor Avenue
                          London

                          Content © CRM Consultancy.