Support Portal ContactGet in touch

TDE Database Encryption with On Premise Dynamics

   Words by James Binnie

   on 04/09/2020 09:41:00

TDE Encryption

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…

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

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

    3. Disclaimer…

    It is worth stressing that you need to check your SQL Server version(s) you will be using – The SQL Server licensing model allows for a non-production ‘Developer’ License that shares the same functionality as an Enterprise License. I found this out the hard way as we had the following set up

    • SQL Server 2016 Developer Edition for UAT
    • SQL Server 2016 Standard Edition for Live

    TDE is not available for the SQL Server 2016 Standard Edition so bare this in mind – However it is available for the SQL Server 2019 Edition 

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

    5. Setting up TDE

    The diagram taken below from 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;
        Create Master Key encryption by password = '<Add a secure password here>';
        CREATE CERTIFICATE Give the DMK a certificate name WITH SUBJECT = 'Example Certificate Subject';
        USE The target Database you want to apply TDE to;
        WITH ALGORITHM = AES_128
        ALTER DATABASE USE The target Database you want to apply TDE to

          And here is what is looks like for me:


              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

                6. 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'
                      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:


                          TDE Encryption Scan in progress


                          TDE Encryption Scan complete

                          Next: Visit our Dynamics Day in the Life Articles and see how CRMCS have successfully implemented innovative and seamless solutions with our current clients

                          Contact us on 0161 348 7360 for a chat or Fill out our Contact Form for further information and how to get started.

                          Share this Article

                          Search Articles

                          Filter Articles

                          CRM Tech DocMan

                          Recent Articles

                          CRMCS Quick Start Guide: How To Produce a Microsoft Teams Live Event Dynamics 365 Marketing: Lead Scoring and Sales Acceptance Designing and Developing Microsoft Power Apps Portals Thank You for Attending CRMCS’ Webinar - Achieving B2B sales excellence with Dynamics 365 & Microsoft Teams Thank You for Attending Our Webinar - Achieving B2B sales excellence with Dynamics 365 & Microsoft Teams Webinar: Discover How CRMCS Have United Dynamics 365, SharePoint and Microsoft Teams To Create Sales Excellence Ignite your workflow by adding DocDrive365 to Office 365 The CRMCS guide to everything you need to know about integrating Teams with Dynamics 365 Saving Time By Keeping Documents In One Place TDE Database Encryption with On Premise Dynamics The Key to Successful Compliance in 2020 Part 2: Let’s get GDPR Compliant with Microsoft Power Automate Top 3 Essential Tips for Remote Working Dynamics 365 Marketing: Top 5 Best Features Dynamics Day in the Life - Puma Investments Can you use Teams to amplify collaboration in Dynamics? Part 1: Using a Scheduled Power Automate to Trigger Expiry Date Reminders The secrets of successful document collaboration in Dynamics CRMCS launches new AppSource approved DocDrive365 Dynamics Day in the Life - Moneypenny Release Management Add the App to Dynamics DocDrive365 Security: Day One - Getting Started with Dynamics to SharePoint Permissions Building a New Scheduled Process using Flow
                          • "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
                          M1 6EQ

                          London Office
                          CRM Consultancy London
                          Grosvenor Avenue

                          Content © CRM Consultancy.